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，則只顯示命令執行的結果，而不顯示出命令本身。