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 ;

 

Find Resource Limit

select * from gv$resource_limit 
where RESOURCE_NAME in ( 'processes','sessions') ;

 

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'; 

Find Oracle Maintained (internal) users

 SYS @ GGATE1 >select username from dba_users where ORACLE_MAINTAINED ='Y' order by username;

USERNAME
--------------------------------------------------
ANONYMOUS
APPQOSSYS
AUDSYS
CTXSYS
DBSFWUSER
DBSNMP
DIP
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
MDSYS
OJVMSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
REMOTE_SCHEDULER_AGENT
SI_INFORMTN_SCHEMA
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
MSYS
XDB
XS$NULL

30 rows selected.

How to define Oracle Prompt in SQL*Plus

cd $ORACLE_HOME/sqlplus/admin

vi glogin.sql

set sqlprompt "_user'@'_connect_identifier> " 

sqlplus / as sysdba

SYS @ GGATE1>

Enable/Disable Restricted Session

 SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          ALLOWED
         2 GGATE12          ALLOWED

-- Run the following command on each RAC node if applicable

SYS @ GGATE1>Alter system enable restricted session;

System altered.

SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          RESTRICTED
         2 GGATE12          ALLOWED

SYS @ GGATE1>Alter system disable restricted session;

System altered.

SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          ALLOWED
         2 GGATE12          ALLOWED

Define Unified Audit in Oracle 19c

Step 1: Run the following query. Value for below query should be TRUE.

select value from v$option where parameter = 'Unified Auditing';

Step 2 :  As sysdba  run the below

CREATE AUDIT POLICY SCOTT_DEPT_POL
  ACTIONS DELETE on SCOTT.DEPT,
          INSERT on SCOTT.DEPT,
          UPDATE on SCOTT.DEPT;

AUDIT POLICY SCOTT_DEPT_POL;