SQL Oracle 1 e SAP - Dicas

--Long RAW TABLES--------------------------------------------------------------------

select
   owner c1,
   table_name c2,
   pct_free c3,
   pct_used c4,
   avg_row_len c5,
   num_rows c6,
   chain_cnt c7,
   chain_cnt/num_rows c8,
   tablespace_name c0
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name in
 (select table_name from dba_tab_columns
where
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;

--LONG RAW TABLES--------------------------------------------

select table_name,data_type from dba_tab_columns
where
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB');



 --LONG Raw tables and tablespaces status--------------------------------------

 select table_name, tablespace_name, num_rows, cluster_owner, PCT_USED, LAST_ANALYZED, DEPENDENCIES, (BLOCKS * 8192)     from dba_tables where tablespace_name = 'TABLESPACE' order by num_rows desc;




--ORACLE JOBS PROGRESS----------------------------------------------------------

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;

--ORACLE LONGOPS----------------------------------------------------------------

select * from v$session_longops

--Searching for a SID session---------------------------------------------------

select
*
from
   v$session
where
   sid = SID;

--Searching for a background job exhibiting some details------------------------ 

   SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
ORDER BY SID;

--Killing a session-------------------------------------------------------------

alter system kill session 'SID,SERIAL';



--DATAPUMP JOBS-----------------------------------------------------------------

select * from dba_datapump_jobs;

--Showing tables from a tablespace----------------------------------------------

select * from dba_tables where TABLESPACE_NAME ;

desc sys.dba_free_space

--Free space of a tablespace----------------------------------------------------

SELECT TABLESPACE_NAME,SUM(FREEMB),SUM(CONTIGUOMB)
FROM
(select tablespace_name,FILE_ID
                      , round(sum(bytes)/1024/1024,2) FreeMb
                      , round(max(bytes)/1024/1024,2) ContiguoMb
from sys.dba_free_space where tablespace_name='TABLESPACE'
group by tablespace_name,FILE_ID
having round(max(bytes)/1024/1024,2) > 70)
GROUP BY tablespace_name
ORDER BY tablespace_name;


--Free space of a tablespace----------------------------------------------------

select
   a.tablespace_name,
   a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
   a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
   nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
   (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
   (select
      tablespace_name,
      sum(bytes) physical_bytes,
      sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
    from
      dba_data_files
    group by
      tablespace_name ) a,
   (select
      tablespace_name,
      sum(bytes) tot_used
    from
      dba_segments
    group by
      tablespace_name ) b
where
   a.tablespace_name = b.tablespace_name (+)
and
   a.tablespace_name not in
   (select distinct
       tablespace_name
    from
       dba_temp_files)
and
   a.tablespace_name not like 'UNDO%'
   AND a.tablespace_name like '%TABLESPACE%'
order by 1;

--Space of a table--------------------------------------------------------------

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='USER'.'TABLE';

--Size of a table---------------------------------------------------------------

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
  FROM DBA_SEGMENTS DS
  WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES) and SEGMENT_NAME like 'TABLE'
 GROUP BY DS.TABLESPACE_NAME,
       SEGMENT_NAME;
     
--Abort a Redef table job-------------------------------------------------------   

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(
   uname       => 'USER',
   orig_table  => 'Table Origem',
   int_table   => 'Tabela destino#$');
END;


--Verifica paralelismo de um indice

select degree from dba_indexes where index_name LIKE 'indice';