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 BAU Tasks daily weekly and monthly
I just copied from oracle forum to keep it here as well for reference. Here is the original https://forums.oracle.com/ords/apexds/post/oracle-dba-daily-weekly-monthly-or-quarterly-checklist-task-9948 Daily Activity Oracle Database instance is running or not Database Listener is running or not. Check any session blocking the other session Check the alert log for an error Check is there any dbms jobs running & check the status of the same Check the Top session using more Physical I/O Check the number of log switch per hour How_much_redo_generated_per_hour.sql Run the statpack report Detect lock objects Check the SQL query consuming lot of resources. Check the usage of SGA Display database sessions using rollback segments State of all the…
-
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,…