show parameter cur%
显示参数
alter system set flush sharedpool ;
显示当前用户 oracle 当前回话下面
SQL> show user ;
USER 为"HR"
plsql 数字 输出 格式化 column format 输出 千
SQL> col pc format 999,999,999,999,999.9
SQL> select 8*333 pc from dual;
PC
----------------------
2,664.0
----------------------
2,664.0
摄入 数字oracle
SQL> select round( 8*333.99999 , 2 ) pc from dual;
PC
----------------------
2,672.0
plsql 命令
根据字段 不同 时候换行.
SQL> break on job_id skip 2 ;
SQL> select first_name , job_id from employees
2 where rownum < 10 order by job_id ;
FIRST_NAME JOB_ID
-------------------- ----------
Steven AD_PRES
Neena AD_VP
Lex
Nancy FI_MGR
SELECT DECODE(TOTALQ, 0, 'No Requests',
WAIT/TOTALQ || ' HUNDREDTHS OF SECONDS') "AVERAGE WAIT TIME PER REQUESTS"
FROM V$QUEUE
WHERE TYPE = 'COMMON';
摘要: where 1=1 and 'true' = ( case
when t.bssign_ = 0 and t.ordbs_ in ('B','S') then 'true'
else 'false'
end) ;
阅读全文
update temp_Test_pm_order t
set ( t.stkname_ , t.STKTP_ , t.HIGHPRICE_ , t.LOWPRICE_ , t.PRICEUNIT_ , t.CURRENCY_CODE_
, t.ISSTOP_ ,t.DEALUNITNAME_ ,t.MARTUNIT_ , t.UPDEALNUM_ , t.LOWDEALNUM_ , t.BUY_UNIT_ ,t.SALE_UNIT_
, t.IV_UNIT_ )
= (select stkname_ ,d.STKTP_ ,d.HIGHPRICE_ , d.LOWPRICE_ ,d.PRICEUNIT_ , d.CURRENCY_CODE_
, d.ISSTOP_, d.DEALUNITNAME_ , d.MARTUNIT_ , d.UPDEALNUM_ ,d.LOWDEALNUM_ , d.BUY_UNIT_ ,d.SALE_UNIT_
, d.IV_UNIT_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_ and d.stkcode_ = t.stkcode_)
UPDATE STATEMENT, GOAL = CHOOSE
UPDATE Object owner=IDMP Object name=TEMP_TEST_PM_ORDER
TABLE ACCESS FULL Object owner=IDMP Object name=TEMP_TEST_PM_ORDER
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
update temp_Test_pm_order t
set
t.STKTP_ = (select d.STKTP_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.HIGHPRICE_ = (select d.HIGHPRICE_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.LOWPRICE_ = (select d.LOWPRICE_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.PRICEUNIT_ = (select d.PRICEUNIT_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.CURRENCY_CODE_ = (select d.CURRENCY_CODE_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.ISSTOP_ = (select d.ISSTOP_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.DEALUNITNAME_ = (select d.DEALUNITNAME_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.MARTUNIT_ = (select d.MARTUNIT_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.UPDEALNUM_ = (select d.UPDEALNUM_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.LOWDEALNUM_ = (select d.LOWDEALNUM_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.BUY_UNIT_ = (select d.BUY_UNIT_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.SALE_UNIT_ = (select d.SALE_UNIT_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_),
t.IV_UNIT_ = (select d.IV_UNIT_
from dl_stkcode d
where d.exchgcode_ = t.exchgcode_
and d.stkcode_ = t.stkcode_)
UPDATE STATEMENT, GOAL = CHOOSE
UPDATE Object owner=IDMP Object name=TEMP_TEST_PM_ORDER
TABLE ACCESS FULL Object owner=IDMP Object name=TEMP_TEST_PM_ORDER
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
TABLE ACCESS BY INDEX ROWID Object owner=IDMP Object name=DL_STKCODE
INDEX UNIQUE SCAN Object owner=IDMP Object name=PK_DL_STKCODE
select *
from col =199012
select *
from col='199012'
查的字段 col 是数字的 还是用col =199012 快.
摘要: select lower( column_name) ||','||'--'||comments
from user_col_comments c
where c.table_name = upper('acc_fundcap')
阅读全文
摘要: 1. 一台机器上装了2个oracle, tns 文件2个都要更新一下 找到主要目录.
datalink 提示 ora-12154 tns 无法处理服务名
2 远程控制的机器 tns file添加了 , 然后连接到其他的机器上的 在其他机器上创建 datalink 和 synonym .不查询的时候不报错..一旦查询了 就开始说tns 没没找到.
阅读全文
摘要: oracle 访问 防火墙只要一个端口
阅读全文
摘要: oracle 9i 一个表空间 导入到另外一个表空间
阅读全文
set echo on
set echo off
表示打开或者关闭查询的显示
三个环境变量的设置
echo 是否现实执行的命令内容
feedback 是否现实 * rows selected
pagesize 页面大小设置
可分开写
set echo on/off
set feedback on/off
set pagesize 999
set echo on，则显示出文件中的每条命令和该命令执行的结果，如果设为set echo off，则只显示命令执行的结果，而不显示出命令本身。