Oracle DBA 必須掌握的 查詢指令碼：

標籤：evel   group   span   user   false   標準   value   rac   bsp   

Oracle  DBA 必須掌握的 查詢指令碼：

1：通過v$parameter資料欄位來查詢oracle標準資料區塊的大小

  1 ----通過 v$parameter資料字典來查詢oracle標準資料區塊的大小。  2 [email protected]> startup  3 ORACLE instance started.  4   5 Total System Global Area 1221992448 bytes  6 Fixed Size                  1344596 bytes  7 Variable Size             771754924 bytes  8 Database Buffers          436207616 bytes  9 Redo Buffers               12685312 bytes 10 Database mounted. 11 Database opened. 12 [email protected]> col name format a30; 13 [email protected]> col value format a20; 14 [email protected]> select name,value from v$parameter where name=‘db_block_size‘; 15  16 NAME                           VALUE 17 ------------------------------ -------------------- 18 db_block_size                  8192 19  20 [email protected]> show parameter db_block 21  22 NAME                                 TYPE        VALUE 23 ------------------------------------ ----------- ------------------------------ 24 db_block_buffers                     integer     0 25 db_block_checking                    string      FALSE 26 db_block_checksum                    string      TYPICAL 27 db_block_size                        integer     8192

2：通過　dict 查看資料庫中資料字典的資訊

  1 [email protected]> col table_name for a30;  2 [email protected]> col comments for a30;  3 [email protected]> select * from dict;  4   5 TABLE_NAME                     COMMENTS  6 ------------------------------ ------------------------------  7 DBA_CONS_COLUMNS               Information about accessible c  8                                olumns in constraint definitio  9                                ns 10  11 DBA_LOG_GROUP_COLUMNS          Information about columns in l 12                                og group definitions 13  14 DBA_LOBS                       Description of LOBs contained 15                                in all tables 16  17 DBA_CATALOG                    All database Tables, Views, Sy

3 : 通過 v$fixed_view_definition 查看資料庫中內部系統資料表的資訊

  1 [email protected]> col view_name format a15;  2 [email protected]> col view_definition format a30000;  3 [email protected]>  select * from v$fixed_view_definition where rownum<=10;  4   5 VIEW_NAME              VIEW_DEFINITION  6 ----------------------------------------------------------------------------------------------  7 GV$WAITSTAT             select inst_id,decode(indx,1,‘data block‘,2,‘sort block‘,3,‘save undo block‘, 4,  8 ‘segment header‘,5,‘save undo header‘,6,‘free list‘,7,‘extent map‘, 8,‘1st level  9  bmb‘,9,‘2nd level bmb‘,10,‘3rd level bmb‘, 11,‘bitmap block‘,12,‘bitmap index b 10 lock‘,13,‘file header block‘,14,‘unused‘, 15,‘system undo header‘,16,‘system und 11 o block‘, 17,‘undo header‘,18,‘undo block‘), count,time from x$kcbwait where ind 12 x!=0

4：通過查詢 dba_data_files  資料來瞭解Oracle系統的資料檔案資訊

  1 [[email protected] ~]$ sqlplus / as sysdba;  2   3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 8 23:27:12 2016  4   5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.  6   7   8 Connected to:  9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options 11  12 [email protected]> col file_name format a50; 13 [email protected]> set linesize3000; 14 [email protected]> select file_name,tablespace_name from dba_data_files where rownum<=10; 15  16 FILE_NAME                                          TABLESPACE_NAME 17 -------------------------------------------------- ------------------------------ 18 /u01/app/oracle/oradata/orcl/users01.dbf           USERS 19 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1 20 /u01/app/oracle/oradata/orcl/sysaux01.dbf          SYSAUX 21 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM 22 /u01/app/oracle/oradata/orcl/example01.dbf         EXAMPLE 23  24 [email protected]>

-----------------

Oracle DBA 必須掌握的 查詢指令碼：