Query scripts that Oracle DBA must master:

Query scripts that Oracle DBA must master:

1: Querying the size of Oracle standard data blocks by V$parameter data fields

1 ----Query the size of the Oracle standard data block by v$parameter the data dictionary. 2[Email protected]> startup3ORACLE instance started.4 5Total System Global area 1221992448 bytes6Fixed Size 1344596 bytes7Variable Size 771754924 bytes8Database buffers 436207616 bytes9Redo buffers 12685312 bytesTenDatabase mounted. OneDatabase opened. A[Email protected]> col name format A30; -[Email protected]> col value format A20; -[Email protected]> Select Name,value from V$parameter where Name= 'db_block_size‘; the  -NAME VALUE - ------------------------------ -------------------- -Db_block_size 8192 +  -[Email protected]> show parameter Db_block +  ANAME TYPE VALUE at ------------------------------------ ----------- ------------------------------ -Db_block_buffers integer 0 -Db_block_checking string FALSE -Db_block_checksum string Typical -Db_block_size integer 8192

2: View information of data dictionary in database by dict

1[Email protected]> col table_name for A30;2[Email protected]> col comments for A30;3[Email protected]> select * from Dict;4 5TABLE_NAME COMMENTS6 ------------------------------ ------------------------------7Dba_cons_columns information about accessible C8Olumns in constraint Definitio9NsTen  OneDba_log_group_columns information about COLUMNS in L AOG Group definitions -  -Dba_lobs Description of LOBs contained theIn all Tables -  -Dba_catalog all database Tables, views, Sy

3: View information about internal system tables in the database by 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 5View_name view_definition6 ----------------------------------------------------------------------------------------------7Gv$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 Level9Bmb, 9,' 2nd level BMB ', ten,' 3rd level BMB ', one,' Bitmap block ', A,' Bitmap index BTenLock, and' File header block ', +,' Unused ', and,' System Undo Header ', +,' System und OneO Block ', +,' Undo Header ', ,' Undo Block '), count,time from x$kcbwait where IND AX!=0

4: Understand data file information of Oracle system by querying Dba_data_files data

1[Email protected] ~]$ Sqlplus/as sysdba;2 3Sql*plus:release 11.2.0.3.0 Production on Thu Dec 8 23:27:12 20164 5Copyright (c) 1982, Oracle. All rights reserved.6 7 8Connected to:9Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-productionTenWith the partitioning, OLAP, Data Mining and Real application testing options One  A[Email protected]> col file_name format A50; -[Email protected]> set linesize3000; -[Email protected]> Select file_name,tablespace_name from Dba_data_files where rownum<=10; the  -file_name Tablespace_name - -------------------------------------------------- ------------------------------ -/U01/APP/ORACLE/ORADATA/ORCL/USERS01.DBF USERS +/U01/APP/ORACLE/ORADATA/ORCL/UNDOTBS01.DBF UNDOTBS1 -/U01/APP/ORACLE/ORADATA/ORCL/SYSAUX01.DBF Sysaux +/U01/APP/ORACLE/ORADATA/ORCL/SYSTEM01.DBF SYSTEM A/U01/APP/ORACLE/ORADATA/ORCL/EXAMPLE01.DBF EXAMPLE at  -[Email protected]>

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

Query scripts that Oracle DBA must master: