Being a database administrator you have asked to take support of other databases as well like Oracle, noSQL, Cloud PaaS and IaaS etc. In Indian service based company, we need attest more than one database knowledge and support experience to lead a database team as well. I have been working in IT more than 12 years and had supported Oracle not in top level and it was older days and I thought of writing lot once, I have strong working experience, but I did not get much after that. I just started again quarantine time, let us install 18c and do some test ourselves. Take a reference of old post:…
-
-
Oracle DBA day to day handy scripts MSSQL DBA usage
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>…