溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

Oracle DBA常用sql有哪些

發(fā)布時間：2021-11-11 14:13:49 來源：億速云 閱讀：169 作者：iii 欄目：關(guān)系型數(shù)據(jù)庫

這篇文章主要介紹“Oracle DBA常用sql有哪些”，在日常操作中，相信很多人在Oracle DBA常用sql有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”O(jiān)racle DBA常用sql有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

1、Oracle 查詢每天執(zhí)行慢的SQL

2、Oracle 查詢鎖之間的依賴關(guān)系

3、Oracle 查找鎖之間依賴關(guān)系的最源頭SID 

4、Oracle 查詢各表空間使用情況--完善篇

5、Oracle 定期檢查意義不大的索引 

6、Oracle 以月為單位檢查索引的使用情況(郵件反饋)

7、Oracle 是分區(qū)表，但條件不帶分區(qū)條件的SQL  

8、Oracle 表結(jié)構(gòu)順序不一致 隱藏的2個問題...

9、Oracle 查看 表屬性 ：“表名(注釋)/列名(注釋)/字段是否NULL”

10、Oracle 查找某一個包體’PACKAGE BODY‘中包含PROCEDURE/FUNCTION的名稱有哪些 

小SQL 

連接~
/*查看Oracle錯誤號信息*/ [oracle@lottery ~]$oerr ora 600
/*清屏~*/ SQL>clear screen    
/*注冊oracle監(jiān)聽*/ SQL>alter system register;
/*查看OS連DB數(shù)*/ [oracle@lottery ~]$ ps -ef | grep oracle$ORACLE_SID| grep LOCAL=NO| wc -l
/*查詢數(shù)據(jù)庫當前進程的連接數(shù)*/ select count(*) from v$process;
/*查看數(shù)據(jù)庫當前會話的連接數(shù)*/ select count(*) from v$session;
/*查看數(shù)據(jù)庫用戶連接會話的總數(shù)*/ select username,count (username) from gv$session where username is not null group by username;
/*查詢數(shù)據(jù)庫最大連接/進程數(shù)*/ select name,value from v$parameter where name in ('processes','sessions');==>show parameter processes/sessions
優(yōu)化~
/*通過SQL_ID查找執(zhí)行計劃*/ select * from table(dbms_xplan.display_cursor('br8d2xs44sga8')); 
/*通過SQL_ID查找SQL文本*/ select * from gv$sqlarea s where s.sql_id= 'br8d2xs44sga8';
/*查看數(shù)據(jù)庫的等待事件*/ SELECT * FROM gv$session_wait where sid in (SELECT sid FROM gV$SESSION WHERE STATUS='ACTIVE'  and username is not null and sid!=userenv('sid'));
/*查看表的統(tǒng)計信息是否正確*/ SELECT TABLE_NAME,NUM_ROWS,LAST_ANALYZED FROM USER_TABLES T WHERE TABLE_NAME='表'; --#用于查看表最后一次統(tǒng)計和真實行數(shù)差距； 
/*查看表所有字段信息*/ select * from user_tab_columns where table_name= '表' ;  
/*統(tǒng)計整個用戶*/ begin DBMS_STATS.gather_schema_stats('用戶',cascade=>TRUE,no_invalidate=>false); end; 
/*統(tǒng)計表*/ begin DBMS_STATS.GATHER_TABLE_STATS('用戶','表', cascade=>TRUE); end ;
/*查看表最后一次DML時間*/ select max(ora_rowscn),scn_to_timestamp(max(ora_rowscn)) from 表;
基本信息~
/*查看表空間剩余情況*/          select TABLESPACE_NAME,sum(round(bytes/1024/1024/1024,2)) from dba_free_space a group by tablespace_name;
/*查詢內(nèi)存分配情況*/ select component,current_size/1024/1024 MB, user_specified_size/1024 MB from v$memory_dynamic_components where current_size!=0;
/*查看用戶大小*/               SELECT OWNER,SUM(BYTES/1024/1024/1024) FROM DBA_SEGMENTS GROUP BY OWNER;
/*查看數(shù)據(jù)庫默認表空間*/ select * from database_properties s where s.description like '%default%tablespace'
/*查看庫中的臨時表*/ select * from user_tables u where u.temporary='Y';
/*查看11g alert文件位置*/ select value from v$diag_info;  --> show parameter diagnostic_dest
/*當前回話的進程號*/ select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
權(quán)限~
/*查看resource角色的權(quán)限*/ select * from role_sys_privs where role='RESOURCE' ;
/*查看數(shù)據(jù)庫中授dba權(quán)限的用戶*/ SELECT * FROM DBA_ROLE_PRIVS S WHERE S.GRANTED_ROLE= 'DBA';
dblink~
/*創(chuàng)建DBLINK語句*/ create public database link dblink名 connect to 用戶 identified by 密碼
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = IP地址 )(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = 實例名)))';  
其他~
/*查找快照SNAP_ID對應(yīng)的時間*/ select * from sys.wrh$_active_session_history;
/*查看索引擁有者!=表的擁有者*/ SELECT owner,index_name,index_type,table_owner,table_name,table_type FROM dba_indexes where owner!=table_owner;
/*查看庫中(只讀)屬性的表*/ select table_name,status,read_only from dba_tables where read_only='YES';
#更改表屬性  alter table 表 read only(read write);(11g新特性)
#注意：索引創(chuàng)建/修改對只讀表【表空間】沒有影響！因為索引修改的是數(shù)據(jù)字典，和表不相關(guān)
/*查看分區(qū)表基本信息查詢*/ SELECT TABLE_NAME,column_name,PARTITION_NAME,HIGH_VALUE LESS_THAN值,TABLESPACE_NAME FROM USER_TAB_PARTITIONS tp join USER_PART_KEY_COLUMNS tpc on tp.table_name=tpc.name;
/*查看某用戶登錄的所有會話*/          SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';', S.* FROM V$SESSION S WHERE USERNAME= '用戶' AND STATUS!='KILLED';--用于解決ORA-01940無法刪除當前連接的用戶   
/*查看command_type值對應(yīng)類型*/ SELECT * FROM v$sqlcommand;  --【v$sqlarea.command_type、v$session.command 】
/*查看某sql_id綁定變量部分傳的值*/ SELECT * FROM V$SQL_BIND_CAPTURE s where s.sql_id in ('fdc8mt5xnjx2a') and CHILD_ADDRESS=2;
/*查找序列l(wèi)ast_number*/ SELECT * FROM USER_SEQUENCES S WHERE S.SEQUENCE_NAME='SEQ_CS_ONCE_CHAR_DET';
/*oracle查看鏈接的hostname和IP分別是什么*/ select utl_inaddr.get_host_address(host_name), host_name from v$instance;---用于當有2個服務(wù)器的數(shù)據(jù)庫是同版本、同監(jiān)聽、同實例
/*oracle 查看standby庫延遲時間*/         SELECT ((substr(value,2,2)*24 +substr(value,5,2))*60+substr(value,8,2 ))* 60+ substr(value,-2) 
TIME FROM gv$dataguard_stats where name = 'apply lag';

/*查看會話狀態(tài)被置為"killed" */   select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status= 'KILLED';
                         -->系統(tǒng)層釋放DB kill狀態(tài)的會話；     （linux：kill -9 spid；  Windows ：orakill orcl pid ）
                        SELECT distinct 'ALTER SYSTEM KILL SESSION '''||SID ||','|| s.SERIAL#||''';',
/*ORA-00054: 資源正忙,但指定以    O.OWNER, O.OBJECT_NAME , S.STATUS, LOCKWAIT  FROM V$SESSION S JOIN V$LOCKED_OBJECT LO
NOWAIT方式獲取資源,或者超時失效*/  ON LO.SESSION_ID = S.SID JOIN DBA_OBJECTS O ON O.OBJECT_ID = LO.OBJECT_ID and S.STATUS='ACTIVE'  AND OBJECT_NAME  in ( '表名字') ;


大SQL


一、查數(shù)據(jù)庫中正在執(zhí)行的SQL：


     SELECT SE.INST_ID, --實例

           SQ.SQL_TEXT, /*SQL文本*/

           SQ.SQL_FULLTEXT, /*SQL全部文本*/

           SE.SID, /*會話的唯一標識，通常要對某個會話進行分析前，首先就需要獲得該會話的SID。*/

           --SE.SERIAL#, /*會話的序號*/

           SQ.OPTIMIZER_COST AS COST_, /* COST 值*/

           SE.LAST_CALL_ET   CONTINUE_TIME, /*執(zhí)行時間 單位是秒 （時間可能是單個sql，也可能是整個功能）*/

           CEIL((SYSDATE-SE.PREV_EXEC_START)*24*60*60) 相差秒數(shù), /*執(zhí)行時間是整個功能時會用到這部分來判斷單個sql執(zhí)行時間*/

           SE.PREV_EXEC_START, /*SQL execution start of the last executed SQL statement*/

           SE.EVENT, /*等待事件*/ 

           SE.LOCKWAIT, /*是否等待LOCK(SE，P)*/

           SE.MACHINE, /*客戶端的機器名。(WORKGROUP\PC-201211082055)*/

           SQ.SQL_ID, /*SQL_ID*/

           SE.USERNAME, /*創(chuàng)建該會話的用戶名*/

           SE.LOGON_TIME /*登陸時間*/

           --SE.TERMINAL, /*客戶端運行的終端名。(PC-201211082055)*/

           --,SQ.HASH_VALUE, /*一個SQL 產(chǎn)生的HASH 值*/

           --SQ.PLAN_HASH_VALUE /*執(zhí)行SQL的HASH值(解析后HASH值)，與SQL_ADDRESS關(guān)聯(lián)查詢其他SQL相關(guān)視圖后即可查詢會話當前正在執(zhí)行的SQL語句*/

      FROM GV$SESSION SE, /*會話信息。每一個連接到ORACLE數(shù)據(jù)庫的會話都能在該視圖中對應(yīng)一條記錄，根據(jù)該視圖中的信息可以查詢該會話使用的用戶，正在執(zhí)行或者剛剛執(zhí)行的SQL語句*/

           /*[GV$SQLAREA 多節(jié)點 ]*/

           GV$SQLAREA SQ /*跟蹤所有SHARED POOL中的共享CURSOR信息，包括 執(zhí)行次數(shù)，邏輯讀，物理讀等*/

     WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE

       AND SE.STATUS = 'ACTIVE'

       AND SE.SQL_ID = SQ.SQL_ID

       AND SQ.INST_ID = SE.INST_ID

       AND SE.USERNAME is not null;

      --過濾條件 

   --AND SE.USERNAME = 'FWSB' --用戶名

   --AND SQ.COMMAND_TYPE IN (2, 3, 5, 6, 189)

   --AND SE.SID != USERENV ('SID')/*rac集群環(huán)境誤用*/

   --AND MACHINE != 'WORKGROUP\MHQ-PC' ;

二、 每天執(zhí)行慢的SQL：


         SELECT S.SQL_TEXT,

           S.SQL_FULLTEXT,

           S.SQL_ID,

           ROUND(ELAPSED_TIME / 1000000 / (CASE

                   WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN

                    1

                   ELSE

                    EXECUTIONS

                 END),

                 2) "執(zhí)行時間'S'",

           S.EXECUTIONS "執(zhí)行次數(shù)",

           S.OPTIMIZER_COST "COST",

           S.SORTS,

           S.MODULE, --連接模式（JDBC THIN CLIENT：程序）

           -- S.LOCKED_TOTAL,

           S.PHYSICAL_READ_BYTES "物理讀",

           -- S.PHYSICAL_READ_REQUESTS "物理讀請求",

           S.PHYSICAL_WRITE_REQUESTS "物理寫",

           -- S.PHYSICAL_WRITE_BYTES "物理寫請求",

           S.ROWS_PROCESSED      "返回行數(shù)",

           S.DISK_READS          "磁盤讀",

           S.DIRECT_WRITES       "直接路徑寫",

           S.PARSING_SCHEMA_NAME,

           S.LAST_ACTIVE_TIME

      FROM GV$SQLAREA S

     WHERE ROUND (ELAPSED_TIME / 1000000 / ( CASE

                   WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN

                    1

                   ELSE

                    EXECUTIONS

                 END),

                 2) > 5 --100 0000微秒=1S

     --  AND S.PARSING_SCHEMA_NAME = USER

       AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD' ) =

           TO_CHAR( SYSDATE, 'YYYY-MM-DD' )

       AND S.COMMAND_TYPE IN (2, 3, 5 , 6, 189) /*值對應(yīng)類型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查詢V$SQLCOMMAND*/

       AND MODULE = 'JDBC Thin Client'

     ORDER BY "執(zhí)行時間'S'" DESC;

三、查看非綁定變量的SQL：


       SELECT V.SQL_ID,

             V.SQL_FULLTEXT,

             V.PARSING_SCHEMA_NAME,

             FM.EXECUTIONS_COUNT,

             FM.ELAPSED_TIME

        FROM (SELECT L.FORCE_MATCHING_SIGNATURE MATHCES,

                     MAX(L.SQL_ID || L.CHILD_NUMBER) MAX_SQL_CHILD,

                       DENSE_RANK() OVER(ORDER BY COUNT(*) DESC ) RANKING,

                     ROUND(SUM (ROUND(ELAPSED_TIME / 1000000 / (CASE

                                       WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN

                                        1

                                       ELSE

                                        EXECUTIONS

                                     END),

                                     5))) ELAPSED_TIME,

                     SUM(L.EXECUTIONS) EXECUTIONS_COUNT 　

                FROM V$SQL L

               WHERE TO_CHAR(TO_DATE(LAST_LOAD_TIME, 'YYYY-MM-DD HH24:MI:SS'),

                             'YYYY-MM-DD') = TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') -- 當天 LAST_LOAD_TIME(VARCHAR類型,LOADED INTO THE LIBRARY CACHE TIME)

                 AND L.MODULE LIKE '%JDBC%' --程序連接

                 AND L.FORCE_MATCHING_SIGNATURE <> 0

                 AND L.PARSING_SCHEMA_NAME = UPPER ('&USERNAME') --用戶

                 AND L.COMMAND_TYPE IN (2, 3, 5 , 6, 189) 　 --命令類型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE  查詢V$SQLCOMMAND

               GROUP BY L.FORCE_MATCHING_SIGNATURE

              HAVING COUNT (*) > 5) FM,

             V$SQL V

       WHERE FM.MAX_SQL_CHILD = (V.SQL_ID || V.CHILD_NUMBER) 　

         AND EXECUTIONS_COUNT >= 50 --執(zhí)行次數(shù)超過50次先篩選改寫，后續(xù)慢慢在范圍小

          ORDER BY FM.RANKING;

         --V$SQL_BIND_CAPTURE  --記錄包含變量得表..包括 ROWNUM<:1 變量

四、查看LOG切換頻率：

       select b.SEQUENCE#,

             b.FIRST_TIME,

             a.SEQUENCE#,

             a.FIRST_TIME,

             round(((a.FIRST_TIME - b.FIRST_TIME) * 24 ) * 60, 2)  時間min

        from v$log_history a, v$log_history b

       where a.SEQUENCE# = b.SEQUENCE# + 1

         and b.THREAD# = 1

       order by a.SEQUENCE# desc;

      

      查看每小時log切換的次數(shù)


      SELECT  trunc(first_time) "Date",

        to_char(first_time, 'Dy') "Day",

        count(1) "Total",

        SUM(decode(to_char(first_time, 'hh34'),'00',1,0)) "h0",

        SUM(decode(to_char(first_time, 'hh34'),'01',1,0)) "h2",

        SUM(decode(to_char(first_time, 'hh34'),'02',1,0)) "h3",

        SUM(decode(to_char(first_time, 'hh34'),'03',1,0)) "h4",

        SUM(decode(to_char(first_time, 'hh34'),'04',1,0)) "h5",

        SUM(decode(to_char(first_time, 'hh34'),'05',1,0)) "h6",

        SUM(decode(to_char(first_time, 'hh34'),'06',1,0)) "h7",

        SUM(decode(to_char(first_time, 'hh34'),'07',1,0)) "h7",

        SUM(decode(to_char(first_time, 'hh34'),'08',1,0)) "h8",

        SUM(decode(to_char(first_time, 'hh34'),'09',1,0)) "h9",

        SUM(decode(to_char(first_time, 'hh34'),'10',1,0)) "h20",

        SUM(decode(to_char(first_time, 'hh34'),'11',1,0)) "h21",

        SUM(decode(to_char(first_time, 'hh34'),'12',1,0)) "h22",

        SUM(decode(to_char(first_time, 'hh34'),'13',1,0)) "h23",

        SUM(decode(to_char(first_time, 'hh34'),'14',1,0)) "h24",

        SUM(decode(to_char(first_time, 'hh34'),'15',1,0)) "h25",

        SUM(decode(to_char(first_time, 'hh34'),'16',1,0)) "h26",

        SUM(decode(to_char(first_time, 'hh34'),'17',1,0)) "h27",

        SUM(decode(to_char(first_time, 'hh34'),'18',1,0)) "h28",

        SUM(decode(to_char(first_time, 'hh34'),'19',1,0)) "h29",

        SUM(decode(to_char(first_time, 'hh34'),'20',1,0)) "h30",

        SUM(decode(to_char(first_time, 'hh34'),'21',1,0)) "h31",

        SUM(decode(to_char(first_time, 'hh34'),'22',1,0)) "h32",

        SUM(decode(to_char(first_time, 'hh34'),'23',1,0)) "h33"

     FROM    V$log_history

     where   trunc(first_time)>sysdate-8

     group by trunc(first_time), to_char(first_time, 'Dy')

     Order by 1;

五、查看SQL執(zhí)行進度：  --顯示運行時間超過6秒的數(shù)據(jù)庫操作的狀態(tài)

       SELECT A.SID,

             A.SERIAL#,

             OPNAME,

             TARGET, --對象

             TO_CHAR(START_TIME, 'YYYY-MM-DD HH24:MI:SS' ) START_TIME, --開始時間

             (SOFAR / TOTALWORK) * 100 PROGRESS, --進度比

             TIME_REMAINING, --估算剩余時間

             ELAPSED_SECONDS, --運行時間‘S’

             A.SQL_ID

        FROM V$SESSION_LONGOPS A

        WHERE SID = ;

        *** 其中SID和SERIAL#是與V$SESSION中的匹配的，

        *** OPNAME：指長時間執(zhí)行的操作名.如： TABLE SCAN

        *** TARGET：被操作的OBJECT_NAME. 如：TABLEA

        *** TARGET_DESC：描述TARGET的內(nèi)容

        *** SOFAR：這個是需要著重去關(guān)注的，表示已要完成的工作數(shù)，如掃描了多少個塊。

        *** TOTALWORK：指目標對象一共有多少數(shù)量（預(yù)計）。如塊的數(shù)量。

        *** START_TIME：進程的開始時間

        *** LAST_UPDATE_TIM：最后一次調(diào)用SET_SESSION_LONGOPS的時間

        *** TIME_REMAINING： 估計還需要多少時間完成，單位為秒

        *** ELAPSED_SECONDS：指從開始操作時間到最后更新時間

        *** MESSAGE：對于操作的完整描述，包括進度和操作內(nèi)容。

        *** USERNAME：與V$SESSION中的一樣。

        *** SQL_ADDRESS：關(guān)聯(lián)V$SQL

        *** SQL_HASH_VALUE：關(guān)聯(lián)V$SQL

        *** QCSID：主要是并行查詢一起使用。  


六、查詢外鍵字段在主鍵表中沒有索引的


      SELECT C.*,

       C1.r_constraint_name,

       c2.table_name,

       T.NUM_ROWS,

       'create index idx_' || c.table_name || '_' || column_name || ' on ' ||

       c.table_name || '(' || column_name || ');'

  FROM USER_CONS_COLUMNS C

  JOIN USER_CONSTRAINTS C1

    ON C1.CONSTRAINT_NAME = C.CONSTRAINT_NAME

   AND C1.CONSTRAINT_TYPE = 'R'

   AND (C.TABLE_NAME, C.COLUMN_NAME) NOT IN

       ( SELECT TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS I)

  JOIN USER_TABLES T

    ON T.TABLE_NAME = C.TABLE_NAME

  join USER_CONSTRAINTS c2

    on c1.r_constraint_name = c2.constraint_name;

       博客：為什么子表外鍵列需要建立索引?  http://blog.itpub.net/17203031/viewspace-701832/ 

        ** 自己測試【外鍵字段不加索引時】

        ** update外鍵表，主鍵表delete任何數(shù)據(jù)都不允許；但update session1的范圍 且set字段不是where字段就可以執(zhí)行，加索引后，更改where字段的數(shù)據(jù)會報錯

七、 查看軟硬解析，游標數(shù)

      SELECT /*A.SID,*/ /* A.STATISTIC#,*/

       SUM (A.VALUE),

       B.NAME,

       ( CASE

         WHEN NAME = 'PARSE COUNT (TOTAL)' THEN

          '表示總的解析次數(shù)'

         WHEN NAME = 'PARSE COUNT (HARD)' THEN

          '表示硬解析的次數(shù)'

         WHEN NAME = 'SESSION CURSOR CACHE COUNT' THEN

          '表示緩存的游標個數(shù)'

         WHEN NAME = 'SESSION CURSOR CACHE HITS' THEN

          '表示從緩存中找到游標的次數(shù)'

         WHEN NAME = 'OPENED CURSORS CURRENT' THEN

          '表示SESSION中打開的游標數(shù)'

       END )

        FROM V$SESSTAT A, V$STATNAME B

       WHERE A.STATISTIC# = B.STATISTIC#

         AND B.NAME IN ( 'PARSE COUNT (HARD)',

                        'PARSE COUNT (TOTAL)' ,

                        'SESSION CURSOR CACHE COUNT' ,

                        'SESSION CURSOR CACHE HITS' ,

                        'OPENED CURSORS CURRENT' )

        -- AND SID=11

       GROUP BY B.NAME

       ORDER BY NAME;

      --#用于衡量 軟硬解析/游標共享比.

八、查看未提交的事物的會話和鎖的對象

       SELECT DISTINCT S.SID,

                      S.SERIAL#,

                      S.MACHINE,

                      L.SQL_TEXT,

                      S.LAST_CALL_ET,

                      'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# ||

                      ''';' ,

                      LO.ORACLE_USERNAME,

                      LO.OS_USER_NAME,

                      AO.OBJECT_NAME,

                      LO.LOCKED_MODE

        FROM V$SESSION       S,

             V$TRANSACTION   T,

             V$SQL           L,

             V$LOCKED_OBJECT LO,

             DBA_OBJECTS     AO

       WHERE S.TADDR = T.ADDR

         AND S.PREV_SQL_ADDR = L.ADDRESS

         AND AO.OBJECT_ID = LO.OBJECT_ID

         AND LO.SESSION_ID = S.SID;

九、通過系統(tǒng)中PID去數(shù)據(jù)庫中找執(zhí)行的SQL:

     SELECT A.USERNAME, A.PROGRAM, B.SPID, C.SQL_TEXT, C.SQL_FULLTEXT

        FROM V$SESSION A, V$PROCESS B, V$SQLAREA C

       WHERE A.PADDR = B.ADDR

         AND A.SQL_HASH_VALUE = C.HASH_VALUE

         AND A.STATUS = 'ACTIVE'

         AND A.USERNAME NOT IN ( 'SYS', 'SYSTEM' , 'SYSMAN')

         AND A.SID != USERENV ('SID')

         AND B.SPID = 填寫PID;

十、序列/索引差異 比對結(jié)果后的創(chuàng)建語句 （例如：將A用戶index和B用戶對比，將A用戶多B用戶的在B用戶創(chuàng)建)

      【如下2個SQL都需要在 缺少sequence/index A用戶執(zhí)行】

      --#SEQUENCE的創(chuàng)建語句：

      SELECT 'CREATE SEQUENCE ' || SEQUENCE_NAME || ' MINVALUE ' || MIN_VALUE ||

             ' MAXVALUE ' || MAX_VALUE || ' START WITH ' || LAST_NUMBER ||

             ' INCREMENT BY ' || INCREMENT_BY || (CASE

               WHEN CACHE_SIZE = 0 THEN

                ' NOCACHE'

               ELSE

                ' CACHE ' || CACHE_SIZE

             END ) || ';'

        FROM USER_SEQUENCES W

       WHERE --過濾掉登錄用戶存在的SEQUENCE

       NOT EXISTS ( SELECT 1

          FROM USER_SEQUENCES@DB_SINOSOFT W1

         WHERE W.SEQUENCE_NAME = W1.SEQUENCE_NAME);

      --#索引差異 結(jié)果的創(chuàng)建語句

      SELECT 'CREATE ' || INDEX_TYPE || ' INDEX ' || INDEX_NAME || ' ON ' ||

             TABLE_NAME || ' (' || LISTAGG(CNAME, ',' ) WITHIN GROUP (ORDER BY COLUMN_POSITION) || ');'

        FROM (SELECT IC.INDEX_NAME,

                      IC.TABLE_NAME,

                      IC.COLUMN_NAME CNAME,

                      IC.COLUMN_POSITION,

                      COUNT(IC.INDEX_NAME) OVER ( PARTITION BY IC.INDEX_NAME, IC.TABLE_NAME) CON,

                      I.INDEX_TYPE

                 FROM USER_IND_COLUMNS@DB_SINOSOFT IC

                 JOIN USER_INDEXES@DB_SINOSOFT I

                   ON I.INDEX_NAME = IC.INDEX_NAME

                WHERE

               --過濾掉登錄用戶存在的INDEX

                NOT EXISTS

                ( SELECT 1

                   FROM USER_IND_COLUMNS IC1

                  WHERE IC1.INDEX_OWNER = UPPER ( '&TO_USERNAME')

                    AND IC.INDEX_NAME = IC1.INDEX_NAME)

               --過濾掉主鍵，避免索引創(chuàng)建，在創(chuàng)建主鍵報錯 對象已存在

             AND IC.INDEX_NAME NOT IN

                ( SELECT C.CONSTRAINT_NAME FROM USER_CONSTRAINTS@DB_SINOSOFT C)

                ORDER BY IC.INDEX_NAME, IC.COLUMN_POSITION)

       GROUP BY INDEX_TYPE, CON, INDEX_NAME, TABLE_NAME;

十一、查看熱點塊的對象


      SELECT A.HLADDR, A.FILE#, A.DBABLK, A.TCH, A.OBJ, B.OBJECT_NAME

        FROM X$BH A, DBA_OBJECTS B

       WHERE (A.OBJ = B.OBJECT_ID OR A.OBJ = B.DATA_OBJECT_ID)

         AND A.HLADDR = '0000000054435000' --V$SESSION_WAIT.P1RAW

      UNION

      SELECT HLADDR, FILE#, DBABLK, TCH, OBJ, NULL

        FROM X$BH

       WHERE OBJ IN ( SELECT OBJ

                       FROM X$BH

                      WHERE HLADDR = '0000000054435000'

                     MINUS

                     SELECT OBJECT_ID

                       FROM DBA_OBJECTS

                     MINUS

                     SELECT DATA_OBJECT_ID FROM DBA_OBJECTS)

         AND HLADDR = '0000000054435000'

       ORDER BY 4;

十一、查看某用戶表大小/總數(shù)情況


      SELECT T.TABLE_NAME,

             TC.COMMENTS,

             T.NUM_ROWS,

             ROUND (SUM (S.BYTES / 1024 / 1024 / 1024 )) GB

        FROM USER_TABLES T

        JOIN USER_SEGMENTS S

          ON S.SEGMENT_NAME = T.TABLE_NAME

        JOIN USER_TAB_COMMENTS TC

          ON TC.TABLE_NAME = T.TABLE_NAME

       GROUP BY T.TABLE_NAME, TC.COMMENTS, T.NUM_ROWS

       ORDER BY NUM_ROWS DESC  NULLS LAST ;

十二、 重新編譯失效存儲/包語句：

          SELECT 'ALTER ' || (CASE

                WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

                 'PACKAGE' ELSE OBJECT_TYPE

                END) || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE ' || (CASE

                  WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

                   'BODY;'  ELSE ';' END), --除類型是PACKAGE BODY返回是PACKAGE，其他正常顯示類型，是PACKAGE BODY顯示COMPILE BODY 否則顯示COMPILE

                OWNER,

                OBJECT_NAME,

                OBJECT_TYPE,

                STATUS,

                O.CREATED,

                LAST_DDL_TIME

           FROM DBA_OBJECTS O

          WHERE STATUS = 'INVALID' -->存儲狀態(tài)'無效';

十三、 Oracle 查看各表空間使用情況和最大最小塊：

        SELECT UPPER (F.TABLESPACE_NAME) "表空間名",

             D.TOT_GROOTTE_MB "表空間大小(M)",

             D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",

             TO_CHAR( ROUND ((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 ,

                           2 ),

                     '990.99' ) "使用比",

             F.TOTAL_BYTES "空閑空間(G)",

             F.MAX_BYTES "最大塊(G)"

        FROM (SELECT TABLESPACE_NAME,

                     ROUND (SUM (BYTES) / 1024 / 1024 / 1024 , 2) TOTAL_BYTES,

                     ROUND (MAX (BYTES) / 1024 / 1024 / 1024 , 2) MAX_BYTES

                FROM SYS.DBA_FREE_SPACE

               GROUP BY TABLESPACE_NAME) F,

             ( SELECT DD.TABLESPACE_NAME,

                     ROUND (SUM (DD.BYTES) / 1024 / 1024 / 1024 , 2) TOT_GROOTTE_MB

                FROM SYS.DBA_DATA_FILES DD

               GROUP BY DD.TABLESPACE_NAME) D

       WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

十四、 Oracle 查看TEMP表空間使用情況 ：

       SELECT F.BYTES_FREE + F.BYTES_USED TOTAL_BYTES,

             F.BYTES_FREE + F.BYTES_USED - NVL (P.BYTES_USED, 0 ) FREE_BYTES,

             D.FILE_NAME,

             NVL (P.BYTES_USED, 0 ) USED_BYTES

        FROM SYS.V_$TEMP_SPACE_HEADER F,

             DBA_TEMP_FILES           D,

             SYS.V_$TEMP_EXTENT_POOL  P

       WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME

         AND F.FILE_ID(+) = D.FILE_ID

         AND P.FILE_ID(+) = D.FILE_ID;

      --> 等同于

      SELECT TABLESPACE_NAME,

             TF.TABLESPACE_SIZE,

             TF.FREE_SPACE,

             TF.TABLESPACE_SIZE - TF.FREE_SPACE

        FROM DBA_TEMP_FREE_SPACE TF;

十五、 Oracle 查看回滾進度情況用的幾個SQL：

      SELECT DISTINCT KTUXESIZ FROM X$KTUXE WHERE KTUXESTA = 'ACTIVE' ;

      SELECT USED_UBLK FROM V$TRANSACTION;

      SELECT KTUXEUSN, KTUXESLT

        FROM X$KTUXE

       WHERE /*KTUXECFL = 'DEAD' AND*/

       KTUXESTA = 'ACTIVE' ;

      SELECT * FROM V_$FAST_START_TRANSACTIONS;

      SELECT USED_UBLK, T.USED_UREC FROM V$TRANSACTION T;

      --查詢視圖V$FAST_START_TRANSACTIONS中字段UNDOBLOCKSDONE，UNDOBLOCKSTOTAL估算SMON恢復(fù)進度

到此，關(guān)于“Oracle DBA常用sql有哪些”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>

向AI問一下細節(jié)

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手