Oracle DBA day to day handy scripts. find tempspace. ------------------- select tot.tablespace_name,tot.mb total_mb,tot.mb - nvl(used.blocks, 0) * blk.block_size / 1024 / 1024 free_mb, round (nvl (used.blocks, 0) * blk.block_size/1024/1024/tot.mb * 100) pct_used from (select tablespace_name,block_size from dba_tablespaces where contents = 'TEMPORARY') blk, (select tablespace_name, round (sum (bytes)/1024/1024) mb from dba_temp_files group by tablespace_name) tot, (select tablespace, nvl (sum (blocks), 0) blocks from v$tempseg_usage group by tablespace) used where blk.tablespace_name = tot.tablespace_name and tot.tablespace_name = used.tablespace (+); SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P. spid, S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT (*) statements FROM v$sort_usage T, v$session S, dba_tablespaces TBS,…
-
-
Learn oracle MS SQL server DBA in short notes
Oracle Short Notes Database – It’s a collection of files. Physical file locations: Listener.ora, TNSname.ora – $ORACLE_HOME/network/admin/ Password & Parameter file – $ORACLE_HOME/dbs/ Control file – flash_recovery_area & /opt/oracle/oradata/ Instance – It’s a collection of SGA+BG process SGA – Shared pool, Database buffer cache, redo log cache, Java pool, large pool & stream pool Shared Pool – Most recently executed statements and definitions. Data Dictionary – most recently used definitions, including table, index & privilege. Library cache – most recently used SQL & PL/SQL statements Shared SQL area – Shared PL/SQL area Database buffer pool – It stores a copy of data block. Default, keep & Recycle –…
-
Deep drive Indexes tuning query tuning-30
When do we rebuild an Index and Table https://mwidlake.wordpress.com/2009/11/18/depth-of-indexes-on-vldbs/ https://community.oracle.com/thread/589369?tstart=0 http://www.orafaq.com/node/2903 – SQL vs Oracle rebuild Depth/height of index – Blevel + 1. Blevel = branch+root, not leaf. Ex: Blevel 3 = Depth is 4, which has a rowid pointer to original table, So the IO taken to read a data is 5. For IOT only 4, since leaf and data is same. select max(blevel) from dba_indexes; select index_name,blevel,leaf_blocks from dba_indexes where owner=’PNNORPT’ and index_name like ‘SLM_STEP%’; select owner,index_name from dba_indexes where blevel=3; SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor,max(blevel) FROM DBA_tables t, DBA_indexes i WHERE t.table_name = i.table_name AND i.index_name like ‘SLM_STEP’ group by t.table_name, i.index_name, t.blocks, t.num_rows,…
-
Oracle DBA table needs to know and maintenance job-29
DBA Tables needs to know To know the database properties and parameters. Show parameter <Parameter name> Select * from v$parameters; – Parameres Select name from v$controlfile; – Control file Select * from v$logfile; – Members info Select * from v$log; – Log size Archive log list; – Archive details Sql> select * from v$sgastat – SGA size Sql> select * from v$pgastat; – PGA size sql> select * from v$tablespace; – Tablespace size sql> select * from dba_tablespaces; – Tablespace size sql> select * from v$datafile; – Datafile size sql> select * from dba_data_files; – Datafile size Rename data file Code Offline tablespace alter tablespace <ts name>…
-
Oracle Locking and isolation levels-28
Locking DML locking: It acquire locks at both the table level and the row level. Row_locking Always – Low level locking – Row lock – Keyword: TX Intent – High level locking – table lock – Keyword: TM Row level lock- DML operations Table level lock – when this a DDL & DML. Table locks do not affect concurrency of DML. Table lock modes Row share lock (RS) — ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. Row exclusive lock (RX) — ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode…