• Oracle

    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

    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,…

  • Oracle

    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 –…

  • Oracle

    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

    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>…