How to generate days, weeks, or months between two dates

select date'2021-06-08' + level - 1 dt
from   dual
connect by level <= (
  date'2023-12-14' - date'2021-06-08' + 1
);

 

Find table last modification date

select X.*  , Y.timestamp Last_updated_date from (
select     t.owner , table_name,     num_rows, TABLE_SIZE_IN_GB 
 from     dba_tables t
Inner join (select OWNER, segment_name,segment_type, sum(bytes/1024/1024/1024) TABLE_SIZE_IN_GB
from dba_segments
where OWNER in ('SCOTT', 'SCOTT_ADM')
group by OWNER,segment_name,segment_type ) s
  On table_name =segment_name and t.owner =s.owner ) X
  join dba_tab_modifications Y on X.owner = Y.TABLE_OWNER  and X.table_name = Y.table_name
  order by 4 desc , 3 desc ;

 

Generate - Rebuild Indexes

select 'alter index '||owner||'.'||index_name||' rebuild tablespace scott_index_tbs;'
from dba_indexes
where owner = 'SCOTT' and tablespace_name <> 'SCOTT_INDEX_TBS';

Rebuild LOB Index:

select 'alter table '||owner||'.'||table_name||' move tablespace SCOTT_DATA_TBS '||chr(10)||'LOB ('||'"'||column_name||'"'||') store as '||segment_name||chr(10)||'(tablespace SCOTT_INDEX_TBS);'
from dba_lobs where owner = 'SCOTT' and tablespace_name <> 'SCOTT_INDEX_TBS';

 

Generate "Drop all the objects in schema"

undefine owner
set pages 0
set lines 300
set heading off

spool /tmp/qwe123.sql

select 'drop table '||owner||'.'||table_name||' cascade constraints purge;'
from dba_tables
where owner = upper('&&owner')
union all
select 'drop '||object_type||' '||owner||'.'||object_name||';'
from dba_objects
where object_type not in ('TABLE','INDEX','PACKAGE BODY','TRIGGER','LOB')
and object_type not like '%LINK%'
and object_type not like '%PARTITION%'
and owner = upper('&&owner')
order by 1;

spool off

@/tmp/qwe123

PROMPT Count of Objects =

select object_type,count(*) from dba_objects where owner = upper('&&owner') group by object_type;

 

Generate Grant User privileges

select 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||grantee||';' 

from dba_tab_privs

where grantee = 'SCOTT';