Oracle Useful Queries

 -- Plugin Violations
set lin 180
column message format a50
column name  format a20
column status format a9
column type format a9
column con_id format 9

select p.con_id,c.name, p.type, p.message, p.status
  from PDB_PLUG_IN_VIOLATIONS p,v$containers c
 where status<>'RESOLVED' and p.con_id=c.con_id
 order by time;

-- running sqls in Oracle
SET LIN 20000
select x.sid
      ,x.serial#
      ,x.username
      ,x.sql_id
      ,x.sql_child_number
      ,optimizer_mode
      ,hash_value
      ,address
      ,sql_text
from   v$sqlarea sqlarea
      ,v$session x
where  x.sql_hash_value = sqlarea.hash_value
and    x.sql_address    = sqlarea.address
and    x.username       is not null;

-- fra space
set lin 180
set pagesize 50
col NAME for a20
col LIMITMB for 9999999
col USEDMB for 9999999
col freeMB for 9999999
SELECT  NAME,
        SPACE_LIMIT/(1024*1024) LIMITMB,
        SPACE_USED/(1024*1024)  USEDMB,
        (SPACE_LIMIT-SPACE_USED)/(1024*1024) freeMB 
FROM V$RECOVERY_FILE_DEST;

-- memory information
set lin 180
set pagesize 50
col name for a40
col value for 99999
col unit for a30
col con_id for 99999
select name, value/(1024*1024) value_MB , con_id 
from V$PGASTAT;

select * from v$memory_target_advice;


set lin 180
set pagesize 50
col MEMORY_SIZE for 9999999         
col MEMORY_SIZE_FACTOR for 9999999  
col ESTD_DB_TIME for 9999999     
col ESTD_DB_TIME_FACTOR for 9999999
col VERSION for 9999999       
col CON_ID for 9999999     

select MEMORY_SIZE,
MEMORY_SIZE_FACTOR,
ESTD_DB_TIME,
ESTD_DB_TIME_FACTOR,
VERSION,
CON_ID
from v$memory_target_advice;

set lin 180
set pagesize 50
col SGA_SIZE for 999999999          
col SGA_SIZE_FACTOR for 999999999      
col ESTD_DB_TIME for 999999999         
col ESTD_DB_TIME_FACTOR for 999999999  
col ESTD_PHYSICAL_READS for 999999999  
col ESTD_BUFFER_CACHE_SIZE for 999999999 
col ESTD_SHARED_POOL_SIZE for 999999999   
col CON_ID for 999999999            
select  SGA_SIZE,
      SGA_SIZE_FACTOR,
      ESTD_DB_TIME,
      ESTD_DB_TIME_FACTOR,
      ESTD_PHYSICAL_READS,
      ESTD_BUFFER_CACHE_SIZE,
      ESTD_SHARED_POOL_SIZE,
      CON_ID
from V$SGA_TARGET_ADVICE;


set lin 180
set pagesize 50
col PGA_TARGET_FOR_ESTIMATE for 9999999      
col PGA_TARGET_FACTOR for 9999999         
col ADVICE_STATUS for a5               
col BYTES_PROCESSED for 9999999           
col ESTD_TIME for 9999999              
col ESTD_EXTRA_BYTES_RW for 9999999       
col ESTD_PGA_CACHE_HIT_PERCENTAGE for 9999999
col ESTD_OVERALLOC_COUNT for 9999999      
col CON_ID for 9999999 
select 
PGA_TARGET_FOR_ESTIMATE/(1024*1024) PGA_TARGET_FOR_ESTIMATE,
PGA_TARGET_FACTOR/(1024*1024) PGA_TARGET_FACTOR,
ADVICE_STATUS,
BYTES_PROCESSED/(1024*1024) BYTES_PROCESSED,
ESTD_TIME/(1024*1024) ESTD_TIME,
ESTD_EXTRA_BYTES_RW/(1024*1024) ESTD_EXTRA_BYTES_RW,
ESTD_PGA_CACHE_HIT_PERCENTAGE/(1024*1024) ESTD_PGA_CACHE_HIT_PERCENTAGE
from v$pga_target_advice;

-- Tablespaces
select   b.tablespace_name,
         tbs_size SizeMb, 
         a.free_space FreeMb
from (select   tablespace_name, 
               round(sum(bytes)/1024/1024 ,2) as free_space 
      from dba_free_space 
      group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size 
from dba_data_files group by tablespace_name
UNION
select   tablespace_name, 
         sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

-- datafiles
set lin 180
set pagesize 50
select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE
from dba_data_files;