bhagyaraj

How to check users, roles and privileges in Oracle How to check high resource intensive SQL in Oracle How to check execution plan of a query How to backup archivelog for specific sequence RMAN How to check last CPU applied in Oracle How to check biggest table in Oracle How to check database backups via sqlplus How to display date and time in query output How to check scheduler jobs in Oracle How to check datapump export progress How to drop all schema objects in Oracle How to find memory used by Oracle How to check last user login Oracle How to check CPU cores in Linux How to delete files older than X days in Linux How to analyze wait events in Oracle How to set DISPLAY variable in Linux Crontab error - Permission Denied How to check FRA location utilization in Oracle How to check last modified table in Oracle How to check single table size in oracle How to check database PITR after refresh How to check archive generation in Oracle How to disable firewall in Li...

======================================= For single Tablespace ---> change TB name in the script ======================================= col "Tablespace" for a22 col "Used MB" for 99,999,999 col "Free MB" for 99,999,999 col "Total MB" for 99,999,999 select df.tablespace_name "Tablespace", df.totalspace "Total MB", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name  and df.tablespace_name='PCMS_DATA' order by "Pct. Free"; col FILE_NAME for a50 set lines 200 pages 200 select F...

Database Health checks in Oracle ################################ Performing Database health checks, when there is an issue reported by Application users. 1. Check the Database details 2. Monitor the consumption of resources 3. Check the Alert Log 4. Check Listener log 5. Check Filesystem space Usage 6. Generate AWR Report 7. Generate ADDM Report 8. Finding Locks,Blocker Session and Waiting sessions in a oracle database 9. Check for alerts in OEM 1. Check the Database details :- ============================= set pages 9999 lines 300 col OPEN_MODE for a10 col HOST_NAME for a30 select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance; For RAC: ------- set pages 9999 lines 300 col OPEN_MODE for a10 col HOST_NAME for a30 select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(START...