已登入: Logged in as: 登入
搜尋:
Page last modified 09:58, 19 Jul 2018 by alang
User:alang > IT 專案工作 > Oracle DBA 筆記 > SQL/Plus
Page Notifications Off

SQL/Plus

    SHELL應用

    只顯示輸出的結果

    SQL> set heading off
SQL> set feedback off
SQL> set pagesize 0

# sqlplus -s user/pass @my.sql

    常用 SQL

    SQL> show user
SQL> conn system/pass 
SQL> r                  執行前一個SQL
SQL> /                  執行前一個SQL
SQL> @?/rdbms/xxx.sql   @ 執行外部SQL檔, ? Oracle_HOME 
SQL> !ls                ! 執行Linux 系統指令
SQL> set timing on 顯示SQL執行耗費時間 
SQL> Rem This is a comment  不要加分號
SQL> l                  顯示前一個 SQL 內容, 直接輸入分號也可以
SQL> 3                 移動至 SQL 內容裡的第3行
SQL> 3 where status='INVALID'      將第3行改成 where status='INVALID'  
SQL> c/INVALID/VALID/                    將目前行內容的 INVALID 取代成 VALID, c = change

    SQL/Plus 輸出設定

    set pagesize 60  -- 輸出每頁行數，預設為24,為了避免分頁，可設定為0, 若設0, 不會顯示欄位名稱。
set linesize 350  -- 每行長度(太小會斷行，太大效能差)
set wrap off   -- 不斷行
set space 1  -- 欄位間空幾個space
set echo on  -- Show SQL語法在畫面上
col tablespace_mb heading 'TABLESPACE|TOTAL MB' -- 設定長標題以斷行顯示

    更多參數

    set termout off      --顯示腳本中的命令的執行結果
set trimout on       --刪除標準輸出每行的行尾空格
set trimspool on     --刪除行尾空白

Select 欄位A||'┴'|| replace(欄位B ,CHR(10),CHR(13))||'┴' from 資料庫.資料表 where 欄位B is not null order by 欄位A;

    TIPs:

    因為欄位B 裡有斷行符號^p，要將他轉換成分行符號^l

    ^p, ^l為word 的代碼，ASCII碼分別為CHR(10),CHR(13)

    欄位A與欄位B間，用┴作區隔

    去除 TAB 與格式化輸出

    set colsep "|"
set tab off

    SQL Language

    定義變數

    var tbs_source varchar2(50);  
var tbs_dest varchar2(50);  
var schema_user varchar2(50); 

exec :tbs_source  := 'SOURCE_TBS';  
    exec :schema_user     := 'SOURCE_USER';   
    exec :tbs_dest := 'DEST_TBS';  

    select 'Transporting tablespace ' || :tbs_source || ' or user ' || :schema_user || ' to tablespace ' || :tbs_dest from dual;

    特殊字元

    -- 換行
chr(10)

    註解文字

    -- 單行說明

/* 這是多行
   說明 */

    顯示系統時間

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)

    取得 Index 的名稱

    用法：sqlplus -s system/manager @get_idx_name.sql | tee idx_names.lst

    • 將結果導出成一個文字檔
    • 修改 where 條件，可以列出 Owner 的所有 Indexes。

    get_idx_names.sql：

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)

    Tablespace

    新增一： 基本單一檔案

    CREATE TABLESPACE "STDB"
    DATAFILE '/opt/oracle/oradata/win/STDB01.dbf' SIZE 1000M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT  AUTO
    ;

    新增多個 datafile *建議*

    create tablespace CWTEST
  datafile '/opt/oracle/oradata/topprod/cwtest_01.dbf' size 1024M
  extent management local
  segment space management auto;

alter tablespace CWTEST
  add datafile '/opt/oracle/oradata/topprod/cwtest_02.dbf' size 1024M;

    新增二：對於大的或數量很多時的 datafile , 使用 autoextend

    CREATE TABLESPACE "STDB"
    DATAFILE '/opt/oracle/oradata/win/STDB01.dbf' SIZE 1000M
    AUTOEXTEND ON NEXT 10M MAXSIZE 30000M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT  AUTO
    ;

    新增 datafile：

    ALTER TABLESPACE STDB
    ADD DATAFILE
    '/opt/oracle/oradata/win/STDB02.dbf' SIZE 1000M
    AUTOEXTEND ON NEXT 10M MAXSIZE 30000M,
    '/opt/oracle/oradata/win/STDB03.dbf' SIZE 1000M
    AUTOEXTEND ON NEXT 10M MAXSIZE 30000M,
    '/opt/oracle/oradata/win/STDB04.dbf' SIZE 1000M
    AUTOEXTEND ON NEXT 10M MAXSIZE 30000M
    ;

    移除 Tablespace：

    -- 確認 Tablespace 沒有任何物件
 select owner,segment_name,segment_type
 from dba_segments
where tablespace_name='<name of tablespace>';

-- 列出 datafile 
select file_name, tablespace_name 
from dba_data_files 
where tablespace_name ='<name of tablespace>';

-- 完整移除 Tablespace
drop tablespace <tablespace-name> including contents;
drop tablespace <tablespace-name> including contents and datafiles;

    Temporary Tablespace

    新增 Temporary Tablespace

    create temporary tablespace WIN_TMP
   tempfile '/u2/oradb/oradata/topdev/WIN_TMP.dbf' size 20000M
   autoextend on
   ;

    增加 datafile：

    alter tablespace TEMP add tempfile '/opt/oracle/oradata/oddfdc/temp01.dbf';

    移除 Temporary 的 datafile：

    alter tablespace TEMP drop tempfile '/opt/oracle/oradata/oddfdc/temp01.dbf';

    設定 Tablespace 成唯讀
    NOTE: 唯讀與線上模式隨時可以切換

    alter tablespace <tablespace-name> read only;

-- 回復成線上狀態
alter tablespace <tablespace-name> read write;

    DB Parameters

    Check DB parameter

    Method 1:

    show parameters
show parameters sga
show parameters sga_target

    Method 2:

    col name for A20
col value for A15
col type for A15
col issys_modifiable for A15
SELECT name, value, type, issys_modifiable FROM v$parameter WHERE name like '%sga%';

    TIPs:

    ISSYS_MODIFIABLE
    - FALSE, need to restart DB
    - IMMEDIATE, no need to restart DB

    Change SGA size to 4G

    --Backup old spfile first
show parameters spfile
CREATE pfile='/path/to/pfile.tmp' FROM spfile='/path/to/spfile.ora'
CREATE spfile='/path/to/spfile.ora.20140313' FROM pfile='/path/to/pfile.tmp'

alter system set sga_max_size=4096M scope=spfile;
alter system set sga_target=4096M scope=spfile;

shutdown immediate;
startup;

    TIPs:

    you must first increase the SGA_MAX_SIZE otherwise will get the following error

    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-00823: Specified value of sga_target greater than sga_max_size

    Check active sessions

    -- Top 10 CPU consumers in last 5 minutes
SELECT * FROM
(
SELECT session_id, session_serial#, count(*)
FROM v$active_session_history
WHERE session_state= 'ON CPU' AND
      sample_time > sysdate - interval '5' minute
GROUP BY session_id, session_serial#
ORDER BY count(*) desc
)
WHERE rownum <= 10;


-- Top 10 waiting sessions in last 5 minutes
SELECT * FROM
(
SELECT session_id, session_serial#, count(*)
FROM v$active_session_history
WHERE session_state='WAITING' AND
      sample_time >  sysdate - interval '5' minute
GROUP BY session_id, session_serial#
ORDER BY count(*) DESC
)
WHERE rownum <= 10;

    檢查所有物件(VIEW,PACKAGE,PROCEDURE....)的 status

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)
    OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
WINSTDB                        PROC_FDS_700982                VIEW
WINSTDB                        V_PROCDEF_FDS_700982           VIEW
WINSTDB                        PROCAVG_FDS_700982             VIEW
WINSTDB                        V_PROCDEFAVG_FDS_700982        VIEW
WINSTDB                        PROC_FDS_700984                VIEW
WINSTDB                        V_PROCDEF_FDS_700984           VIEW
WINSTDB                        PROCAVG_FDS_700984             VIEW
WINSTDB                        V_PROCDEFAVG_FDS_700984        VIEW
WINSTDB                        PROC_FDS_701631                VIEW
WINSTDB                        V_PROCDEF_FDS_701631           VIEW
WINSTDB                        PROCAVG_FDS_701631             VIEW

    Check the usage of the temporary tablespaces

    By tablespace:

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)
    TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TMP_CDB                                500          3        497
TMP_STDB                              1000          0       1000
TEMP                                    75          0         75

    By session:

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)

    As you can see, there are different segment types. Most of time, SORT is the one we need to check. If you are lucky enough, or the client only have 1 SQL cursor, you will get the SQL hash value in the above result.

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)
    • sid =
    • PARSING_SCHEMA_NAME =
       HASH_VALUE      SORTS ROWS_PROCESSED/EXECUTIONS
------------- ---------- -------------------------
    887856235      30506                .000196676
   2631006892      30227                .001323276
   3490377209        632                46993.6709 <==
    標籤 (Edit tags)
    • No tags
    什麼連接到這裡

    文件 0

    附加檔案或者圖像
     
    您必須 登入 才能發佈評論。
    Powered by MindTouch Core