oracle security

http://blog.red-database-security.com/category/oracle-security/

Unix find command on shell script

cat mfind.sh


echo "enter the path:\c"
read path
echo "enter the file name:\c"
read filename
find $path -name $filename -print > x
nl=`wc -l < x`
if [ $nl -eq 0 ]
then
echo File not found
else
cat < x
fi

Unix tar command

tar :Description The "tar" command stands for tape archive.

This command is used to create new archives, list files in existing archives, and extract files from archives.

c - add to archive, using an absolute path
x - extract from archive
t - read the contents of an archive
f - you work with a file, otherwise it's a tape
p - preserve file's permissions and modes
v - verbose, display the result
-I - use an include list
X - use an exclude list





Examples tar cvf /dev/rct0 /home

This command writes a tar archive to the tape device /dev/rct0. It copies the files in the /home directory, and all subdirectories of /home to the tape device.




tar cvf /tmp/home.tar /home

This command creates a tar archive named /tmp/home.tar. The tar command copies the files in the /home directory, and all subdirectories of /home.


tar cvf /tmp/home.tar /home


tar -cvf /users/anuj/b.tar *.sql

tar xvf b.tar -----extract

tar tvf b.tar ----------list the file

tar rvf b.tar *.* appends files at end of archive


Using tar

Using tar to create a file archive on a tape device:

# tar -cvf /dev/rmt0 file.name

or

# tar -cvf /dev/rmt0 .

or for multiple directory hierarchies

# tar -crvf my.tar `find /tmp/junk -type f` `find /var/tmp -type f`

using tar to list the entries in a file archive on a tape device:
# tar -tvf /dev/rmt0

using tar to retrieve a file from a tape device:
# tar -xvf /dev/rmt0 file.name


To list the contents of your archive issue the following command:

tar tf my_arch.tar

To extract the contents of your archive:

tar xvfp my_arch1.tar

Note, that everything will be extracted exactly at the same location from where it was taken. If some directories do no exist then they will be created.


If you want to include into your archive several different directories you might use the following command:

tar cvf my_arch.tar -I include.lst


create an archive file called "sasdir.tar". In this case, the tar command when executed in your home directory will be:

tar -cvf sasdir.tar -C sasdir .
Suppose, you wish to archive the subdirectories "sasdir" and "spssdir" both of which are in your home directory. In this case the tar command executed in your home directory will be:

tar -cvf sasdir.tar -C sasdir . -C ../spssdir .

Oracle Event Numbers info ?

Oracle event info
Oracle event
Event Number
oracle event info



Event numbers can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

$cd $ORACLE_HOME/rdbms/mesg


oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/mesg> ls -ltr oraus.msg
-rw-r--r-- 1 oracle oinstall 4777005 2009-08-14 22:59 oraus.msg


oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/mesg> tail -10 oraus.msg
// *Document: NO
// *Cause: A LOB update using DBMS_LOB or a related interface was attempted on a HYBRID COLUMNAR compressed table with an unsupported index type. Virtual, functional, domain, and join indexes do not support these operations.
// *Action: Drop the index or do not perform LOB updates on this table.
//

64307, 00000, "hybrid columnar compression is only supported in tablespaces residing on Exadata storage"
// *Document: YES
// *Cause: An attempt was made to use hybrid columnar compression on unsupported storage.
// *Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.



=====================


to set event

sid Session identifier
serial# Session serial number
event no The event number 10000 to 10999
level The level to set. See file $ORACLE_HOME/rdbms/mesg/oraus.msg for details
of events and possible level settings
nm Name, usually just set it to a NULL string ''


sql> exec dbms_system.set_ev(9, 1188, 10046, 12, '');


===============================================


set events can be found through dbms_system.read_ev

set serveroutput on

declare
event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||to_char(event_level));
end if;
end loop;
end;
/

Event 10513 set at level 2

PL/SQL procedure successfully completed.




SQL> alter system set events '10513 trace name context off' ;

System altered.

SQL> declare
event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||to_char(event_level));
end if;
end loop;
end;
/

PL/SQL procedure successfully completed.




SQL> ALTER SYSTEM SET event='10235 trace name context forever,level 2','27072 trace name errorstack level 3' COMMENT='TEST' SCOPE=SPFILE;

System altered.



SQL> ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;

System altered.


ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

Oracle Shutdown Immediate Hang

 



oracle Shutdown hang 

or
before shutdown 


prompt  long time wait for shutdown to get completed if sum value is high

after kill session  long transaction , then run this query 

--- dictionary managed tablespaces, extents are maintained in uet$

prompt Is SMON clearing the temp space?  
prompt removed from the table for used extents UET$ ;
prompt placed on the table for free extents FET$ ;

select count(f.block#) "Should Increase >", count(u.block#) "Should Decrease <" from fet$ f ,uet$ u;



--  monitor clean up the session 

select sum(used_ublk) from v$transaction;  
                                                              
                                                              
 event="10513 trace name context forever, level 2" 
                                                              
          
  alter system set events '10513 trace name context forever, level 2' ;

System altered.


on spfile

SQL> alter system set event='10513 trace name context forever, level 2' scope=spfile;

System altered.


SQL> alter system reset event scope=spfile sid='*';

System altered.


alter system reset parameter  sid=’sid|*’



Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.

-Specify SID = 'sid_of_a_database' if you want Oracle Database to change the value of the parameter only for the instance sid.



SQL> alter system reset control_file_record_keep_time scope=spfile sid='*';

System altered.

****** Don't change the value of a parameter with '' or null to reset it to default ******




col NAME format a20
col DISPLAY_VALUE format a60
select NAME ,DISPLAY_VALUE from  V$SPPARAMETER where NAME like '%even%';


NAME                 DISPLAY_VALUE
-------------------- ------------------------------------------------------------
event                10513 trace name context forever, level 2
xml_db_events



col DISPLAY_VALUE format a60
select NAME ,DISPLAY_VALUE from  V$SYSTEM_PARAMETER where NAME like '%even%';


NAME                 DISPLAY_VALUE
-------------------- ------------------------------------------------------------
event
xml_db_events        enable




shutdown immediate to get complete without transaction recovery.SMON will not do a transaction recovery untill this event 

select SID, SERIAL#, EVENT#, EVENT from v$session  where  SERIAL# >1 ;


Recovery after killing a large transaction
The message 'Waiting for smon to disable tx recovery' will posted in the alert log as well.


alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", 
undoblockstotal-undoblocksdone "ToDo", 
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 
"Finish at" 
FROM v$fast_start_transactions; 

  
  

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",
 undoblockstotal-undoblocksdone "ToDo", DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))  "Finish at" 
FROM v$fast_start_transactions; 
  

SELECT state, undoblocksdone, undoblockstotal, cputime   FROM v$fast_start_transactions;

V$FAST_START_TRANSACTIONS contains information both for transactions that the Oracle server is recovering(the STATE is RECOVERING) and for transactions that the Oracle server has recovered(the STATE is RECOVERED). New columns are added to this view as;

• XID: Transaction ID of this transaction
• PXID: Transaction ID of the parent transaction
• RCVSERVERS: Number of servers working on this transaction including the coordinator server(It can be 1 if only SMON is doing the recovery).




- In some versions the cputime does not work (always 0), hence the estimated completion time will not be displayed
- In some cases the v$fast_start_transactions view will not work. If this is the case then you can query the internal data dictionary
view x$ktuxe, the 'ktuxesiz' column represents the remaining number of undo blocks required for rollback:

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'; 


The message 'Waiting for smon to disable tx recovery' will posted in the alert log as well.

First we need to determine the progress SMON is making. It is very possible the SMON process to work with one rollback segment. You can find it using the following query:

col LOGON format a10
col ACTION format a10
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10


SELECT r.NAME "RB Segment Name", dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, 
v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE, 
v$session.PROGRAM, v$session.module, action 
FROM v$lock l, v$process p, v$rollname r, v$session, 
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments 
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg 
WHERE l.SID = p.pid(+) AND 
v$session.SID = l.SID AND 
TRUNC (l.id1(+)/65536)=r.usn
-- AND l.TYPE(+) = 'TX' AND 
-- l.lmode(+) = 6 
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;

RB Segment Name                   SIZE_MB LOGON             SID    SERIAL# SPID                     PROCESS                  USERNAME   STATUS   OSUSER
------------------------------ ---------- ---------- ---------- ---------- ------------------------ ------------------------ ---------- -------- ------------------------------
MACHINE                                                          PROGRAM    MODULE     ACTION
---------------------------------------------------------------- ---------- ---------- ----------
_SYSSMU1_3780397527$                10.13 3 Days + 2         54      13021                          6325                     SYS        INACTIVE oracle
                                          0:10:54
apt-amd-02                                                       sqlplus@ap sqlplus@ap
                                                                 t-amd-02 ( t-amd-02 (
                                                                 TNS V1-V3) TNS V1-V3)

Oracle Create table with CONSTRAINT name

Always create CONSTRAINT with name

Example for create table


create table supplier
("s#" varchar2(4) constraint pk_supplier primary key,
sname varchar2(10) constraint nn_sname not null,
cname varchar2(10) constraint nn_cname not null,
salary number(6) default 500 constraint nn_salary not null,
worksfor varchar2(10),
theKey int constraint fk_thekey references parent(theKey),
constraint ck_salary check ((salary > 500) or (salary = 500)))



create table y(stno number constraint ref_y references x(stno) on delete cascade );



SQL> CREATE TABLE dept
(
deptno NUMBER(2)
CONSTRAINT pk_dept PRIMARY KEY
CONSTRAINT ck_deptno CHECK (deptno BETWEEN 10 and 99),
dname VARCHAR2(9) CONSTRAINT ck_dname CHECK (dname=UPPER(dname)),
loc VARCHAR2(10) CONSTRAINT ck_loc CHECK (loc IN ('DALLAS','BOSTON','CHICAGO'))
)


Table created.




create table taAccountTransaction(
AccountNumber char(8) not null
constraint coFKTransactiontaAccount references taAccount on delete cascade,
DateAndTime date not null,
Amount number(14,2) not null,
constraint coPKtaAccountTransaction primary key (AccountNumber, DateAndTime)
using index tablespace tsIndex) tablespace tsData;



CREATE TABLE order_detail
(order_id Number Constraint fk_oid REFERENCES scott.order(order_id),
(part_no Number Constraint fk_pno REFERENCES part(part_no),
(quantity Number Constraint nn_qty Not NULL Constraint check_qty_low CHECK (quantity > 0),
(cost Number Constraint check_cost CHECK (cost>0),
(Constraint pk_od PRIMARY KEY (order_id, part_no))

Oracle user Role

user Role
Oracle role info
Oracle create user

undefine user

SELECT GRANTEE, 'ROL' TYPE, GRANTED_ROLE PV
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = upper('&&user')
UNION
SELECT GRANTEE, 'PRV' TYPE, PRIVILEGE PV
FROM DBA_SYS_PRIVS
WHERE GRANTEE = upper('&&user')
UNION
SELECT GRANTEE,
'OBJ' TYPE,
MAX(DECODE(PRIVILEGE, 'WRITE', 'WRITE,')) ||
MAX(DECODE(PRIVILEGE, 'READ', 'READ')) ||
MAX(DECODE(PRIVILEGE, 'EXECUTE', 'EXECUTE')) ||
MAX(DECODE(PRIVILEGE, 'SELECT', 'SELECT')) ||
MAX(DECODE(PRIVILEGE, 'DELETE', ',DELETE')) ||
MAX(DECODE(PRIVILEGE, 'UPDATE', ',UPDATE')) ||
MAX(DECODE(PRIVILEGE, 'INSERT', ',INSERT')) || ' ON ' || OBJECT_TYPE || ' "' ||
A.OWNER || '.' || TABLE_NAME || '"' PV
FROM DBA_TAB_PRIVS A, DBA_OBJECTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.OBJECT_NAME
AND A.GRANTEE = upper('&&user')
GROUP BY A.OWNER, TABLE_NAME, OBJECT_TYPE, GRANTEE
UNION
SELECT USERNAME GRANTEE, '---' TYPE, 'empty user ---' PV
FROM DBA_USERS
WHERE NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_ROLE_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_TAB_PRIVS)
AND USERNAME LIKE upper('&&user')
GROUP BY USERNAME
ORDER BY GRANTEE, TYPE, PV;

undefine user
~
~

SQL> @role
Enter value for user: scott
old 3: WHERE GRANTEE = upper('&&user')
new 3: WHERE GRANTEE = upper('scott')
old 7: WHERE GRANTEE = upper('&&user')
new 7: WHERE GRANTEE = upper('scott')
old 22: AND A.GRANTEE = upper('&&user')
new 22: AND A.GRANTEE = upper('scott')
old 30: AND USERNAME LIKE upper('&&user')
new 30: AND USERNAME LIKE upper('scott')

GRANTEE TYPE PV
------------------------------ ------------ -----------------------------------------------------------------------------------------------------------------------------------
SCOTT PRV UNLIMITED TABLESPACE
SCOTT ROL CONNECT
SCOTT ROL RESOURCE


=================================================



---- **** role1.sql

set pagesize 58 linesize 131
rem
column username format a15 heading User
COLUMN account_status format a10 heading Status
column default_tablespace format a15 heading Default
column temporary_tablespace format a15 heading "Temporary"
column granted_role format a31 heading Roles
column default_role format a10 heading Default?
column admin_option format a7 heading Admin?
column profile format a15 heading 'Users Profile'
rem
start title132 'ORACLE USER REPORT'
-- define output = rep_out\db\usr_rep

break on username skip 1 on account_status on default_tablespace on temporary_tablespace on profile
spool role-info.txt
rem
select username, account_status, default_tablespace, temporary_tablespace, profile, granted_role, admin_option, default_role
from sys.dba_users a, sys.dba_role_privs b
where a.username = b.grantee
order by username,account_status, default_tablespace, temporary_tablespace,
profile, granted_role;
rem
spool off
set termout on flush on feedback on verify on
clear columns
clear breaks
pause Press enter to continue


undefine 1

SET VERIFY OFF FEEDBACK OFF PAGES 10000 LIN 80 RECSEP OFF ECHO OFF ARRAY 5
CL COL
SPOOL output.lst
COL username NOPRINT
COL external_name NOPRINT
COL default_tablespace FOR A16 HEADING "Default TS"
COL temporary_tablespace FOR A16 HEADING "Temporary TS"
COL account_status FOR A16
COL password FOR A16
COL profile FOR A16
COL grantee FOR A16 NOPRINT
COL owner FOR A16
COL table_name FOR A16
COL grantor FOR A16
PROMPT
PROMPT ============================= DBA_USERS ===============================
SELECT * FROM dba_users WHERE username = UPPER('&1')
/
PROMPT
PROMPT ============================= Granted roles ===========================
SELECT * FROM dba_role_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted system privileges ===============
SELECT * FROM dba_sys_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted object privileges ===============
COL privilege FOR A20 WORD_WRAPPED
SELECT * FROM dba_tab_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted column privileges ===============
SELECT * FROM dba_col_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Tablespace quotas =======================
SELECT tablespace_name, DECODE(max_bytes ,-1,'UNLIMITED',TO_CHAR(max_bytes/1048576,9999.9 )||' Mb') "Quota"
FROM dba_ts_quotas WHERE username = UPPER('&&1')
/
PROMPT
PROMPT ============================= Database objects ========================
SELECT object_type, COUNT(*) FROM dba_objects
WHERE owner = UPPER('&&1')
GROUP BY object_type
/
PROMPT
PROMPT ============================= End of report ===========================
SPOOL OFF
UNDEFINE USER
PROMPT Output is spooled in output.lst
SET FEEDBACK ON RECSEP WR
SET PAGES 24
CL COL

undefine 1


--- -**** role1.sql ---<<<<<<<<<<<<<<<<<<<<<<<



sample output -----------------


User Status Default Temporary Users Profile Roles Admin? Default?
--------------- ---------- --------------- --------------- --------------- --------------------- ------- ----------
WMSYS EXPIRED & SYSAUX TEMP1 DEFAULT RESOURCE NO YES
LOCKED

WM_ADMIN_ROLE YES YES

XDB EXPIRED & SYSAUX TEMP1 DEFAULT CTXAPP NO YES
LOCKED

JAVAUSERPRIV NO YES
RESOURCE NO YES


105 rows selected.

Press enter to continue


============================= DBA_USERS ===============================
Enter value for 1: BOEXI1

USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
---------- ---------------- ---------------- --------- ---------
Default TS Temporary TS CREATED PROFILE
---------------- ---------------- --------- ----------------
INITIAL_RSRC_CONSUMER_GROUP PASSWORD E AUTHENTI
------------------------------ -------- - --------
89 OPEN 27-FEB-12
APTUS_OBJ TEMP1 31-AUG-11 DEFAULT
DEFAULT_CONSUMER_GROUP 10G 11G N PASSWORD

============================= Granted roles ===========================
Enter value for 1: BOEXI1

GRANTED_ROLE ADM DEF
------------------------------ --- ---
CONNECT NO YES
RESOURCE NO YES

============================= Granted system privileges ===============

PRIVILEGE ADM
---------------------------------------- ---
UNLIMITED TABLESPACE NO

============================= Granted object privileges ===============

============================= Granted column privileges ===============

============================= Tablespace quotas =======================

============================= Database objects ========================

============================= End of report ===========================



==================



role2.sql ----------<<<<<<<<<<<<<<

set pages 1001
set lines 132
set verify off
set echo off
set head off

accept role_nm prompt 'Enter Role Name >>->'
prompt 'Role Has Following Object Privileges : '
select grantee || ' Have ' || lpad(substr(privilege, 1, 10), 10, ' ') ||
' On ' || substr(owner || '.' || table_name,1,50)
from dba_tab_privs
where owner = 'prod_schema_owner'
and grantee = upper('&role_nm')
order by grantee;

prompt 'Role Has Following System Privileges : '

select grantee || ' Have ' || lpad(substr(privilege, 1, 30), 30, ' ')
|| decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_sys_privs
where grantee = upper('&&role_nm')
order by grantee;

prompt 'Role Has Following Roles Granted : '

select grantee || ' Have Role' || lpad(substr(granted_role, 1, 30), 30, ' ')
|| decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where grantee = upper('&&role_nm')
order by grantee;

prompt 'Role is Granted to Following Roles : '
select 'Role ' || granted_role || ' Is Granted to Role ' || grantee ||
decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where granted_role = upper('&&role_nm')
and grantee in (select role from dba_roles)
order by grantee;

prompt 'Role is Granted to Following Users : '
select 'Role ' || granted_role || ' Is Granted to User ' || grantee ||
decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where granted_role = upper('&&role_nm')
and grantee in (select username from dba_users)
order by grantee;

set verify on
set echo on
set head on




SQL> @role2
Enter Role Name >>->connect
'Role Has Following Object Privileges : '

no rows selected

'Role Has Following System Privileges : '

CONNECT Have CREATE SESSION

1 row selected.

'Role Has Following Roles Granted : '

no rows selected

'Role is Granted to Following Roles : '

no rows selected

'Role is Granted to Following Users : '

Role CONNECT Is Granted to User ABC
Role CONNECT Is Granted to User ANUJ
Role CONNECT Is Granted to User ANUJREP
Role CONNECT Is Granted to User ANUJTEST
Role CONNECT Is Granted to User APEX_030200 With Admin Option
Role CONNECT Is Granted to User APEX_040000
Role CONNECT Is Granted to User GGATE
Role CONNECT Is Granted to User IX
Role CONNECT Is Granted to User MDDATA
Role CONNECT Is Granted to User MDSYS
Role CONNECT Is Granted to User OWBSYS With Admin Option
Role CONNECT Is Granted to User PM
Role CONNECT Is Granted to User SCOTT
Role CONNECT Is Granted to User SPATIAL_CSW_ADMIN_USR
Role CONNECT Is Granted to User SPATIAL_WFS_ADMIN_USR
Role CONNECT Is Granted to User SYS With Admin Option
Role CONNECT Is Granted to User TEST_USER With Admin Option
Role CONNECT Is Granted to User WMSYS
Role CONNECT Is Granted to User XYZ



======================





all_users
all_tables
user_sys_privs
sys.dba_users
sys.dba_tab_privs
sys.dba_sys_privs
sys.dba_profiles
sys.dba_roles
sys.dba_role_privs
sys.dba_audit_trail
sys.aud$




set linesize 121
col username format a25
col profile format a10
col "tmp tbs" format a10

SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role,r.admin_option, r.default_role
FROM sys.dba_users u, sys.dba_role_privs r
WHERE u.username = r.grantee (+)
GROUP BY u.username, u.default_tablespace,
u.temporary_tablespace, u.profile, r.granted_role,
r.admin_option, r.default_role
order by 1;



set pause off;
set linesize 78;
set pagesize 56;
set newpage 0;

column c1 heading "User" format a20;
column c2 heading "Privilege";
column c3 heading "Default TS" format a10;
column c4 heading "Temp TS" format a10;

select substr(dba_users.username,1,20) c1,
dba_role_privs.granted_role c2,
substr(dba_users.default_tablespace,1,15) c3,
substr(dba_users.temporary_tablespace,1,15) c4
from sys.dba_role_privs, sys.dba_users
where sys.dba_role_privs.grantee = sys.dba_users.username
order by 1,2;


Don't give connect,resource to any user while creating user

SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
FROM sys.dba_users a, sys.dba_role_privs b
wHERE a.username = b.grantee
and a.username = upper('&&user')
UNION
SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
WHERE a.username = b.grantee
and a.username = upper('&&user')
UNION
SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
FROM sys.dba_users a, sys.dba_tab_privs b
WHERE a.username = b.grantee
and a.username = upper('&&user')
ORDER BY 1;


USERNAME WHAT_GRANTED
------------------------------ ---------------------------------------------------------------------------------------------
SCOTT CONNECT
SCOTT RESOURCE
SCOTT UNLIMITED TABLESPACE -----not very good idea ....




SQL> revoke UNLIMITED TABLESPACE from scott;

Revoke succeeded.



SQL> SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
2 FROM sys.dba_users a, sys.dba_role_privs b
3 wHERE a.username = b.grantee
4 and a.username = upper('&&user')
5 UNION
6 SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
7 8 WHERE a.username = b.grantee
9 and a.username = upper('&&user')
10 UNION
11 SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
12 FROM sys.dba_users a, sys.dba_tab_privs b
13 WHERE a.username = b.grantee
14 and a.username = upper('&&user')
15 ORDER BY 1;
Enter value for user: scott
old 4: and a.username = upper('&&user')
new 4: and a.username = upper('scott')
old 9: and a.username = upper('&&user')
new 9: and a.username = upper('scott')
old 14: and a.username = upper('&&user')
new 14: and a.username = upper('scott')

USERNAME WHAT_GRANTED
------------------------------ ---------------------------------------------------------------------------------------------
SCOTT CONNECT
SCOTT RESOURCE



select * from sys.dba_sys_privs where GRANTEE=upper('&role') ;


select distinct grantee from dba_sys_privs ;

select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';


select grantee, granted_role from dba_role_privs where grantee not in ('SYS', 'SYSTEM','DBA') order by grantee;


create user rrr identified by rr
default tablespace users
temporary tablespace temp
quota unlimited on users
quota unlimited on temp ---- 10gR2 . you cannot grant quota on temporary tablespace
/

SQL> create user rrr identified by rrr
default tablespace users
temporary tablespace temp
quota unlimited on users
quota unlimited on temp
/

create user rrr identified by rr
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace



Note: 114673.1 RESOURCE Role in DBA_SYS_PRIVS does not Include UNLIMITED TABLESPACE Privilege
Note: 1005485.6 ORA-1950 When Creating an Object and Resource Role is Granted to the User
Note: 1084014.6 Revoking DBA or RESOURCE Privilege Revokes UNLIMITED TABLESPACE from the User


SELECT * FROM user_sys_privs;
select * from session_privs;


SQL> create user rrr identified by rrr
default tablespace users
temporary tablespace temp
quota unlimited on users
/

User created.





SQL> ALTER USER scott QUOTA UNLIMITED ON users;

User altered.



--- connect

grant CREATE SESSION to rrr ;

resoure ---
grant CREATE TRIGGER to rrr ;
grant CREATE SEQUENCE to rrr ;
grant CREATE TYPE to rrr ;
grant CREATE PROCEDURE to rrr ;
grant CREATE CLUSTER to rrr ;
grant CREATE OPERATOR to rrr ;
grant CREATE INDEXTYPE to rrr ;
grant CREATE TABLE to rrr ;




grant create view to rrr ;

grant CREATE ANY INDEX to rrr ; ?????

Grant succeeded.


revoke CREATE SESSION from rrr ;

Revoke succeeded.






SQL> REVOKE CREATE ANY INDEX FROM rrr ;

Revoke succeeded.



SQL> SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
2 FROM sys.dba_users a, sys.dba_role_privs b
3 wHERE a.username = b.grantee
4 and a.username = upper('&&user')
5 UNION
6 SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
7 FROM sys.dba_users a, sys.dba_sys_privs b
8 WHERE a.username = b.grantee
9 and a.username = upper('&&user')
10 UNION
11 SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
12 FROM sys.dba_users a, sys.dba_tab_privs b
13 WHERE a.username = b.grantee
14 and a.username = upper('&&user')
15 ORDER BY 1;
old 4: and a.username = upper('&&user')
new 4: and a.username = upper('RRR')
old 9: and a.username = upper('&&user')
new 9: and a.username = upper('RRR')
old 14: and a.username = upper('&&user')
new 14: and a.username = upper('RRR')

USERNAME WHAT_GRANTED
-------------------- ---------------------------------------------------------------------------------------------
RRR CREATE CLUSTER
RRR CREATE INDEXTYPE
RRR CREATE OPERATOR
RRR CREATE PROCEDURE
RRR CREATE SEQUENCE
RRR CREATE SESSION
RRR CREATE TABLE
RRR CREATE TRIGGER
RRR CREATE TYPE
RRR CREATE VIEW

10 rows selected.




select * from session_privs;


The UNLIMITED TABLESPACE system privilege does not let a user create their own datafiles.
The CREATE TABLESPACE system privilege lets a user create a tablespace. The ALTER TABLESPACE system
privilege lets a user add a datafile to an




SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO

SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='RESOURCE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO

8 rows selected.


SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='SCOTT';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT USERS TEMP


ALTER USER scott QUOTA unlimited ON users;

ALTER USER scott QUOTA 10M ON users;

SELECT * FROM DBA_ts_quotas;

A quota value of MAX_BYTES -1 indicated UNLIMITED




SQL> SELECT U.USERNAME,R.GRANTED_ROLE FROM SYS.DBA_USERS U,SYS.DBA_ROLE_PRIVS R
WHERE U.USERNAME=R.GRANTEE(+)
AND R.ADMIN_OPTION='NO'
AND U.USERNAME = UPPER('SCOTT')
GROUP BY U.USERNAME,R.GRANTED_ROLE
ORDER BY U.USERNAME ;



USERNAME GRANTED_ROLE
------------------------------ ------------------------------
SCOTT RESOURCE
SCOTT CONNECT

SQL> SELECT R.GRANTED_ROLE,U.USERNAME FROM SYS.DBA_USERS U,SYS.DBA_ROLE_PRIVS R WHERE U.USERNAME=R.GRANTEE(+)
AND R.ADMIN_OPTION='NO'
AND R.GRANTED_ROLE = 'CONNECT'
GROUP BY U.USERNAME,R.GRANTED_ROLE
ORDER BY U.USERNAME ;



GRANTED_ROLE USERNAME
------------------------------ ------------------------------
CONNECT ABC
CONNECT ANUJ
CONNECT ANUJREP
CONNECT ANUJTEST
CONNECT APEX_040000
CONNECT GGATE
CONNECT IX
CONNECT MDDATA
CONNECT MDSYS
CONNECT PM
CONNECT SCOTT
CONNECT SPATIAL_CSW_ADMIN_USR
CONNECT SPATIAL_WFS_ADMIN_USR
CONNECT WMSYS
CONNECT XYZ

15 rows selected.



SELECT table_name FROM dba_tab_privs p,dba_objects o
WHERE p.owner=o.owner
--- Managing Default User Accounts 333
AND p.table_name = o.object_name
-- AND p.owner = 'SCOTT'
AND p.privilege = 'EXECUTE'
AND p.grantee = 'PUBLIC'
AND o.object_type='PACKAGE';






col USERNAME format a25
col DEFAULT_TABLESPACE format a15
col PROFILE format a10
col GRANTED_ROLE format a25
SELECT usr.username, usr.default_tablespace, usr.temporary_tablespace "TMP TBS", usr.profile,rl.granted_role, rl.admin_option, rl.default_role
FROM sys.dba_users usr, sys.dba_role_privs rl
WHERE usr.username = rl.grantee (+)
and usr.username not in ('SYS','SYSTEM')
and usr.username=upper('&&user')
GROUP BY usr.username, usr.default_tablespace,usr.temporary_tablespace, usr.profile, rl.granted_role, rl.admin_option, rl.default_role;


USERNAME DEFAULT_TABLESP TMP TBS PROFILE GRANTED_ROLE ADM DEF
------------------------- --------------- ------------------------------ ---------- ------------------------- --- ---
WMSYS SYSAUX TEMP1 DEFAULT RESOURCE NO YES
OWBSYS SYSAUX TEMP1 DEFAULT JAVAUSERPRIV NO YES
MGMT_VIEW SYSTEM TEMP1 DEFAULT MGMT_USER NO YES
BOEXI APTUS_OBJ TEMP1 DEFAULT RESOURCE NO YES




select * from role_sys_privs where role in (select role from dba_role_privs where role=upper('&role') )

ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO


select * from role_role_privs where role in (select role from user_role_privs where username = 'SCOTT')


CREATE ROLE test_role IDENTIFIED BY test123;

grant select, insert, update, delete on suppliers to test_role;





SQL> SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS;

GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_1 UPDATE
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_2 UPDATE
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_3 UPDATE
FLOWS_FILES WWV_FLOW_COMPANIES PROVISIONING_COMPANY_ID REFERENCES
FLOWS_FILES WWV_FLOW_COMPANIES PROVISIONING_COMPANY_ID REFERENCES

=====


===============
SQL> show user
USER is "SYS"


SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
SYSDBA
SYSOPER
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
ALTER ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
EXEMPT ACCESS POLICY
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
ADMINISTER SQL TUNING SET
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
EXEMPT IDENTITY POLICY
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB
CREATE ANY EDITION
DROP ANY EDITION
ALTER ANY EDITION
CREATE ASSEMBLY
CREATE ANY ASSEMBLY
ALTER ANY ASSEMBLY
DROP ANY ASSEMBLY
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY
CREATE MINING MODEL
CREATE ANY MINING MODEL
DROP ANY MINING MODEL
SELECT ANY MINING MODEL
ALTER ANY MINING MODEL
COMMENT ANY MINING MODEL
CREATE CUBE DIMENSION
ALTER ANY CUBE DIMENSION
CREATE ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
DROP ANY CUBE DIMENSION
INSERT ANY CUBE DIMENSION
SELECT ANY CUBE DIMENSION
CREATE CUBE
ALTER ANY CUBE
CREATE ANY CUBE
DROP ANY CUBE
SELECT ANY CUBE
UPDATE ANY CUBE
CREATE MEASURE FOLDER
CREATE ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
DROP ANY MEASURE FOLDER
INSERT ANY MEASURE FOLDER
CREATE CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DROP ANY CUBE BUILD PROCESS
UPDATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
ADMINISTER SQL MANAGEMENT OBJECT
ALTER PUBLIC DATABASE LINK
ALTER DATABASE LINK
FLASHBACK ARCHIVE ADMINISTER

208 rows selected.


===========


COL roles FOR a60
COL table_name FOR a30
col privilege for a9
set lin 200 trims on pages 0 emb on hea on newp none

SELECT *
FROM ( SELECT CONNECT_BY_ROOT grantee grantee,
privilege,
REPLACE (
REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'),
'^/[^/]*'),
'/',
' --> ')
ROLES,
owner,
table_name,
column_name
FROM (SELECT PRIVILEGE,
GRANTEE,
OWNER,
TABLE_NAME,
NULL column_name
FROM DBA_TAB_PRIVS
WHERE owner NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')
UNION
SELECT PRIVILEGE,
GRANTEE,
OWNER,
TABLE_NAME,
column_name
FROM DBA_COL_PRIVS
WHERE owner NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')
UNION
SELECT GRANTED_ROLE,
GRANTEE,
NULL,
NULL,
NULL
FROM DBA_ROLE_PRIVS
WHERE GRANTEE NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')) T
START WITH grantee IN (SELECT username FROM dba_users)
CONNECT BY PRIOR PRIVILEGE = GRANTEE)
WHERE table_name IS NOT NULL AND grantee != OWNER
ORDER BY grantee,
owner,
table_name,
column_name,
privilege;

=================



VARIABLE user_name VARCHAR2(255);
exec :user_name :='&user_name' ;


WITH user_roles AS
(SELECT granted_role FROM dba_role_privs START WITH grantee =:user_name CONNECT BY grantee = prior granted_role
UNION ALL
SELECT username FROM dba_users WHERE username =:user_name
)
SELECT lpad(' ', 2 *(level - 1)) || privilege "Privilege",
type AS "Privilege_Type",
object_type AS "Object_Type",
owner AS "Owner",
table_name AS "Name",
grantable AS "Grantable",
grantor AS "Grantor",
hierarchy AS "Hierarchy"
FROM
(SELECT grantee,
privilege,
'Object' AS type,
do.object_type,
dtp.owner,
dtp.table_name,
dtp.grantable,
dtp.grantor,
dtp.hierarchy
FROM dba_tab_privs dtp
JOIN dba_objects DO
ON dtp.owner = do.owner
AND dtp.table_name = do.object_name
JOIN user_roles
ON dtp.grantee = user_roles.granted_role
WHERE NOT object_type LIKE '%_BODY'
UNION ALL
SELECT grantee,
privilege,
'Column' AS type,
'TABLE COLUMN' AS object_type,
owner,
table_name || ' (' || column_name || ')' AS table_name,
grantable,
grantor,
'NO' AS hierarchy
FROM dba_col_privs
JOIN user_roles
ON dba_col_privs.grantee = user_roles.granted_role
UNION ALL
SELECT grantee,
granted_role AS privilege,
'Role' AS type,
NULL AS object_type,
NULL AS owner,
NULL AS table_name,
NULL AS grantable,
NULL AS grantor,
NULL AS hierarchy
FROM dba_role_privs
)
START WITH grantee =:user_name
CONNECT BY grantee = prior privilege

Oracle Foreign Key info on table / user

primary key Foreign Key on a table



SQL> set serveroutput on
declare
cons varchar2(60);
r_cons varchar2(60);
type vc230 is table of varchar2(30) index by binary_integer;
type num is table of number index by binary_integer;
fk_coln vc230;
pk_coln vc230;
fk_pos num;
pk_pos num;
cnt number;
tab_name_with_fk varchar2(30);
tab_name_with_pk varchar2(30);
cursor main is
select constraint_name cons, r_constraint_name r_cons, table_name tab_name_with_fk
from user_constraints
where table_name = upper('&table_name_with_foreign_key')
and constraint_type = 'R';
begin
for cur in main loop
dbms_output.put_line(lpad('*', 80, '*'));
dbms_output.put_line('Reference Constraint_Name : ' || cur.r_cons);
dbms_output.put_line('Constraint_Name : ' || cur.cons);
select table_name into tab_name_with_pk
from user_constraints
where constraint_name = cur.r_cons;
declare
cursor c1 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.cons
order by position;
cursor c2 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.r_cons
order by position;
begin
cnt := 1;
for cur1 in c1 loop
fk_coln(cnt) := cur1.columnname;
fk_pos(cnt) := cur1.position;
cnt := cnt + 1;
end loop;
cnt := 1;
for cur2 in c2 loop
pk_coln(cnt) := cur2.columnname;
pk_pos(cnt) := cur2.position;
cnt := cnt + 1;
end loop;
dbms_output.put_line('Table Contains FK Is ' || rpad(cur.tab_name_with_fk, 15, ' ') ||
' Table Contains PK Is ' || rpad(tab_name_with_pk, 15, ' '));
for i in 1..cnt-1 loop
if (i != cnt-1) then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') ||
' Pos ' || rpad(to_char(fk_pos(i)), 2, ' ') || ' ' || ' Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
elsif i = cnt-1 then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') || ' Pos ' ||
rpad(to_char(fk_pos(i)), 2, ' ') || ' References ' || 'Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
end if;
end loop;
dbms_output.put_line(lpad('*', 80, '*'));
end;
end loop;
end;
/


SQL> /
Enter value for table_name_with_foreign_key: EMP
old 16: where table_name = upper('&table_name_with_foreign_key')
new 16: where table_name = upper('EMP')
********************************************************************************
Reference Constraint_Name : PK_DEPT
Constraint_Name : FK_DEPTNO
Table Contains FK Is EMP Table Contains PK Is DEPT
Foreign Key DEPTNO Pos 1 References Primary Key DEPTNO Pos 1
********************************************************************************

PL/SQL procedure successfully completed.


====



SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT b.table_name, b.owner
FROM dba_constraints a,
dba_constraints b
WHERE b.r_constraint_name = a.constraint_name
AND b.r_owner = a.owner
AND a.constraint_type in ('P','U')
AND b.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')
union
SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT a.table_name, a.owner
FROM dba_constraints a,
dba_constraints b
WHERE a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner
AND b.constraint_type in ('P','U')
AND a.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')

SQL> /

DDL
--------------------------------------------------------------------------------

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") DISABLE

Oracle MTTR advisory or FAST_START_MTTR_TARGET

It reduces the time required for cache recovery, after shutdown abort or Oracle crashed


Disable MTTR advisory

FAST_START_MTTR_TARGET = 0
LOG_CHECKPOINT_INTERVAL = 200000



Enabling MTTR Advisory

FAST_START_MTTR_TARGET to a value greater than zero enables the fast-start checkpointing feature.


STATISTICS_LEVEL = TYPICAL
FAST_START_MTTR_TARGET > 0 should be more then zero ....


FAST_START_MTTR_TARGET initialization parameter to 300. To modify FAST_START_MTTR_TARGET use: "

The default is 300 seconds (5 Minutes). and maximum we can give 3600 ( 1 Hour).



apt-rdbms-01.aptus.co.uk:APTDB\sys> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE
--------------------
<<<----- MTTR advisory is missing






apt-rdbms-01\sys> !cat mttr.sql

set linesize 145
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- MTTR target ---

prompt -- ----------------------------------------------------------------------- ---


set heading off
select ' fast_start_mttr_target '||value ||' (seconds)' from V$PARAMETER where name='fast_start_mttr_target'
union
select ' statistics_level '||value from V$PARAMETER where name='statistics_level' ;

select ' '||Name||Lpad(To_char(value),48-length(Name))|| ' (Default : '||Isdefault||')' from V$PARAMETER where name like 'log_checkpoint_%'




rdbms-01:DB\sys> @mttr
fast_start_mttr_target 0 (seconds)
statistics_level TYPICAL
log_checkpoint_interval 0 (Default : TRUE)
log_checkpoint_timeout 1800 (Default : TRUE)
log_checkpoints_to_alert FALSE (Default : TRUE)



rdbms-01:DB\sys>!cat mttr1.sql

column RECOVERY_ESTIMATED_IOS format 999999999 heading "Recovery Estim. IOS"
column ACTUAL_REDO_BLKS format 999999999 heading "Actual|Redo Blk."
column TARGET_REDO_BLKS format 999999999 heading "Target|Redo Blk."
column LOG_FILE_SIZE_REDO_BLKS format 999999999 heading "Log file size|Redo Blk."
column LOG_CHKPT_TIMEOUT_REDO_BLKS format 999999999 heading "Log Chk. Timeout|Redo Blk."
column LOG_CHKPT_INTERVAL_REDO_BLKS format 999999999 heading "Log Chk. Interval|Redo Blk."
column FAST_START_IO_TARGET_REDO_BLKS format 999999999 heading "Fast Start IO Target|Redo Blk."
column TARGET_MTTR format 999999999 heading "Target|MTTR"
column ESTIMATED_MTTR format 999999999 heading "Estimated|MTTR"
column CKPT_BLOCK_WRITES format 999999999 heading "Chk. Block Writes"
column OPTIMAL_LOGFILE_SIZE format 999999999 heading "Optimal Logfile Size"
column ESTD_CLUSTER_AVAILABLE_TIME format 999999999 heading "Estim. Cluster|Available Time"
column WRITES_MTTR format 999999999 heading "Writes MTTR"
column WRITES_LOGFILE_SIZE format 999999999 heading "Writes Logfile Size"
column WRITES_LOG_CHECKPOINT_SETTINGS format 999999999 heading "Writes Log Chk Set."
column WRITES_OTHER_SETTINGS format 999999999 heading "Writes Other Set."
column WRITES_AUTOTUNE format 999999999 heading "Writes Autotune"
column WRITES_FULL_THREAD_CKPT format 999999999 heading "Writes Full Thread Ckpt"


Select
RECOVERY_ESTIMATED_IOS
, ACTUAL_REDO_BLKS
, TARGET_REDO_BLKS
, LOG_FILE_SIZE_REDO_BLKS
, LOG_CHKPT_TIMEOUT_REDO_BLKS
, LOG_CHKPT_INTERVAL_REDO_BLKS
From
V$INSTANCE_RECOVERY
;

Select
FAST_START_IO_TARGET_REDO_BLKS
, TARGET_MTTR
, ESTIMATED_MTTR
, CKPT_BLOCK_WRITES
, OPTIMAL_LOGFILE_SIZE
, ESTD_CLUSTER_AVAILABLE_TIME
From
V$INSTANCE_RECOVERY ;

Select
WRITES_MTTR
, WRITES_LOGFILE_SIZE
, WRITES_LOG_CHECKPOINT_SETTINGS
, WRITES_OTHER_SETTINGS
, WRITES_AUTOTUNE
, WRITES_FULL_THREAD_CKPT
From
V$INSTANCE_RECOVERY ;


Actual Target Log file size Log Chk. Timeout Log Chk. Interval
Recovery Estim. IOS Redo Blk. Redo Blk. Redo Blk. Redo Blk. Redo Blk.
------------------- ---------- ---------- ------------- ---------------- -----------------
1121 5278 8005 165888 8005


Fast Start IO Target Target Estimated Estim. Cluster
Redo Blk. MTTR MTTR Chk. Block Writes Optimal Logfile Size Available Time
-------------------- ---------- ---------- ----------------- -------------------- --------------
0 16 2100071


Writes MTTR Writes Logfile Size Writes Log Chk Set. Writes Other Set. Writes Autotune Writes Full Thread Ckpt
----------- ------------------- ------------------- ----------------- --------------- -----------------------
0 5174 0 0 2485565 933




rdbms-01:DB\sys> alter system set FAST_START_MTTR_TARGET = 300 scope=both ;

System altered.


apt-rdbms-01.aptus.co.uk:APTDB\sys> SELECT OPTIMAL_LOGFILE_SIZE OPTIMAL_LOGFILE_SIZE_MB FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE_MB
-----------------------
321






apt-rdbms-01.aptus.co.uk:APTDB\sys> SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE/1024/1024,OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

Target Estimated
MTTR MTTR Writes MTTR WRITES_LOGFILE_SIZE/1024/1024 Optimal Logfile Size
---------- ---------- ----------- ----------------------------- --------------------
36 15 0 .004934311 321



checkpoint is not driven by WRITES_MTTR ie the FAST_START_MTTR parameter.because size is not optimal




apt-rdbms-01.aptus.co.uk:APTDB\sys> select BYTES/1024/1024 from v$log ;

BYTES/1024/1024
---------------
50 <<<<<<<<< --size should be 321 mb
50
50




Recommendation

All redo files should be same size .
small redo file can increase checkpoint activity and reduce performance
thumb rule is to switch logs every twenty minutes


ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 1200 SCOPE=BOTH; ---- for this set 20 min

or

through

cron

alter system archive log current;



Which means that Redo Logfile Size Advisor is enabled only if FAST_START_MTTR_TARGET is set.




some metalinke note

Note:265831.1 – Automatic Checkpoint Tuning in 10g
Note:274264.1 – REDO LOGS SIZING ADVISORY
Note 180894.1 – V$INSTANCE_RECOVERY
Note 151062.1 – Init.ora Parameter “FAST_START_MTTR_TARGET” Reference Note
Note 30754.1 – Init.ora Parameter “LOG_CHECKPOINT_INTERVAL” Reference Note
Note 30755.1 – Init.ora Parameter “LOG_CHECKPOINT_TIMEOUT” Reference Note
Note 68931.1 – Init.ora Parameter “FAST_START_IO_TARGET” Reference Note

Oracle Performance and Availability System-Level Response-Time Analysis

Oracle Performance and Availability System-Level Response-Time Analysis

Oracle Database Performance
Oracle SYSMETRIC
general database health


select METRIC_NAME, VALUE from SYS.V_$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio')
AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);


METRIC_NAME VALUE
------------------------------ ----------
Database Wait Time Ratio 6
Database CPU Time Ratio 94



prompt experienced any dips in overall performance by using this query

select end_time, value from sys.v_$sysmetric_history
where metric_name = 'Database CPU Time Ratio'
order by 1;


prompt overall database efficiency minimum, maximum, and average values by querying the V$SYSMETRIC_SUMMARY

select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1





prompt what types of user activities are responsible

select case db_stat_name
when 'parse time elapsed' then
'soft parse time'
else db_stat_name
end db_stat_name,
case db_stat_name
when 'sql execute elapsed time' then
time_secs - plsql_time
when 'parse time elapsed' then
time_secs - hard_parse_time
else time_secs
end time_secs,
case db_stat_name
when 'sql execute elapsed time' then
round(100 * (time_secs - plsql_time) / db_time,2)
when 'parse time elapsed' then
round(100 * (time_secs - hard_parse_time) / db_time,2)
else round(100 * time_secs / db_time,2)
end pct_time
from
(select stat_name db_stat_name,
round((value / 1000000),3) time_secs
from sys.v_$sys_time_model
where stat_name not in('DB time','background elapsed time','background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time
from sys.v_$sys_time_model
where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time
from sys.v_$sys_time_model
where stat_name = 'PL/SQL execution elapsed time'),
(select round((value / 1000000),3) hard_parse_time
from sys.v_$sys_time_model
where stat_name = 'hard parse elapsed time')
order by 2 desc;



prompt global wait times


select WAIT_CLASS,
TOTAL_WAITS,
round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS,
ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS,
round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
from
(select WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle'),
(select sum(TOTAL_WAITS) SUM_WAITS,
sum(TIME_WAITED) SUM_TIME
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle')
order by 5 desc;



prompt wait class


select to_char(a.end_time,'DD-MON-YYYY HH:MI:SS') end_time,
b.wait_class,
round((a.time_waited / 100),2) time_waited
from sys.v_$waitclassmetric_history a,
sys.v_$system_wait_class b
where a.wait_class# = b.wait_class# and
b.wait_class != 'Idle'
order by 1,2;


select a.sid,
b.username,
a.wait_class,
a.total_waits,
round((a.time_waited / 100),2) time_waited_secs
from sys.v_$session_wait_class a,
sys.v_$session b
where b.sid = a.sid and
b.username is not null and
a.wait_class != 'Idle'
order by 5 desc;






prompt at given time

set linesize 200
col WAIT_EVENT format a20
col WAIT_EVENT format a30
col USERNAME format a15

select sess_id,
username,
program,
wait_event,
sess_time,
round(100 * (sess_time / total_time),2) pct_time_waited
from
(select a.session_id sess_id,
decode(session_type,'background',session_type,c.username) username,
a.program program,
b.name wait_event,
sum(a.time_waited) sess_time
from sys.v_$active_session_history a,
sys.v_$event_name b,
sys.dba_users c
where a.event# = b.event# and
a.user_id = c.user_id and
sample_time > to_date('16-AUG-11 12:00:00','dd-mon-yy hh:mi:ss') and
sample_time < to_date('17-AUG-11 07:00:00','dd-mon-yy hh:mi:ss') and
b.wait_class = 'User I/O'
group by a.session_id, decode(session_type,'background',session_type,c.username),a.program, b.name),
(select sum(a.time_waited) total_time
from sys.v_$active_session_history a,
sys.v_$event_name b
where a.event# = b.event#
and sample_time > to_date('16-AUG-11 12:00:00','dd-mon-yy hh:mi:ss')
and sample_time < to_date('17-AUG-11 07:00:00','dd-mon-yy hh:mi:ss')
and b.wait_class = 'User I/O')
order by 6 desc;





select *
from
(select sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time
from sys.v_$sqlarea
order by 5 desc)
where rownum < 6;





select event,
time_waited,
owner,
object_name,
current_file#,
current_block#
from sys.v_$active_session_history a,
sys.dba_objects b
where sql_id = '&sql_id'
and a.current_obj# = b.object_id
and time_waited <> 0;

Oracle add datafile on oracle standby

Oracle add datafile
standby add datafile



col FILE_NAME format a55
select FILE_NAME,TABLESPACE_NAME from dba_data_files
where TABLESPACE_NAME='DM_xxxPROD1_DOCBASE';



FILE_NAME TABLESPACE_NAME
------------------------------------------------------- ------------------------------
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase01.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase02.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase03.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase04.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase05.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase06.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase07.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase08.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase09.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase10.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase11.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase12.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase13.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase14.dbf DM_xxxPROD1_DOCBASE



alter tablespace add datafile '/data/oracle/xxx/oradata/dev9r2/questaims2.dbf' size 100M maxsize 2000M autoextend on;

SQL> ALTER TABLESPACE DM_xxxPROD1_DOCBASE ADD DATAFILE '/opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf' size 2G ;

Tablespace altered.




SQL> !ls -ltr /opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf
-rw-r----- 1 oracle dba 2147491840 Aug 17 13:10 /opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf



SQL> alter system switch logfile; ---- this statement will create datafile on standby

System altered.


binary format:
SQL> alter database backup controlfile to '/xxxxx/xxxxx/file_name';



then dump the controlfile on human readable format


SQL> alter database backup controlfile to trace as '/opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt' ;

Database altered.

SQL> !ls -ltr /opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt
-rw-r--r-- 1 oracle dba 9990 Aug 17 13:27 /opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt




Now time to check on standby .


ssh on that box


xxx-ora-01dr# ls -ltr /opt/oracle/oradata1/xxxdb/
total 8294416
-rw-r----- 1 oracle dba 2147491840 Aug 17 13:34 dm_xxxprod1_docbase15.dbf <<<<<<<<<<<<<<<<---
-rw-r----- 1 oracle dba 2097160192 Aug 17 13:34 dm_xxxprod1_ind13.dbf




then dump the controlfile on human readable format on standby also


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace as '/opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt' ;

Database altered.

Oracle Archive Gaps in Standby Database

Oracle Standby Gap info

Oracle Standby log gap sql

Standby Gap

On the Both 

set pagesize 200
set linesize 200
select
  substr(local.name,1,70)  "Archive Name",
  case when remote.sequence# is null then 'NOT TRANSMITTED' 
                                     else 'transmitted'
  end,
  local.sequence#,
  local.thread#
from
  (select * from v$archived_log where dest_id = 1) local 
                                    left join 
  (select * from v$archived_log where dest_id = 2) remote
  on local.sequence# = remote.sequence# and
     local.thread#   = remote.thread#
  order by local.sequence#;



Archive Name                                                           CASEWHENREMOTE.  SEQUENCE#    THREAD#
---------------------------------------------------------------------- --------------- ---------- ----------
/u01/oracle/Archive/arch_1_0000000025_780268156.arc                    transmitted             25          1
/u01/oracle/Archive/arch_1_0000000026_780268156.arc                    transmitted             26          1
/u01/oracle/Archive/arch_1_0000000027_780268156.arc                    transmitted             27          1
/u01/oracle/Archive/arch_1_0000000028_780268156.arc                    transmitted             28          1
/u01/oracle/Archive/arch_1_0000000029_780268156.arc                    transmitted             29          1
/u01/oracle/Archive/arch_1_0000000030_780268156.arc                    transmitted             30          1
/u01/oracle/Archive/arch_1_0000000031_780268156.arc                    transmitted             31          1
/u01/oracle/Archive/arch_1_0000000032_780268156.arc                    transmitted             32          1





On Standby

SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);   

   THREAD#  SEQUENCE#
---------- ----------
         1         49
         1         50
         1         51
         1         52
         1         53
         1         54
         1         59
         1         60

8 rows selected.


On Standby

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     60                    60          0





SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM  V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1         59          1       1381
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      APPLYING_LOG          1         60        791     102400
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         60        791          1

9 rows selected.




on Primary

column DEST_NAME format A19
column DB_UNIQUE_NAME format A15
column VALID_NOW format A9
column VALID_TYPE format A15
column VALID_ROLE format A15
select DEST_NAME,DB_UNIQUE_NAME,VALID_NOW ,VALID_TYPE,VALID_ROLE from V$ARCHIVE_DEST WHERE DB_UNIQUE_NAME <> 'NONE';

DEST_NAME           DB_UNIQUE_NAME  VALID_NOW VALID_TYPE      VALID_ROLE
------------------- --------------- --------- --------------- ---------------
LOG_ARCHIVE_DEST_2  STANDBY         YES       ONLINE_LOGFILE  PRIMARY_ROLE





col archive_log_seq format 9999999
col dest format a10
select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#),'2' dest from v$archived_log where DEST_ID=2


or

select * from (select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#) archive_log_seq ,'2' dest from v$archived_log where DEST_ID=2 )

ARCHIVE_LOG_SEQ DEST
--------------- ----------
             60 1
             60 2


 select pri , stand , pri - stand "Diff" from ( select max(SEQUENCE#) pri from v$archived_log where DEST_ID=1) , (select max(b.SEQUENCE#) stand from v$archived_log b where b.DEST_ID=2 );

       PRI      STAND       Diff
---------- ---------- ----------
        60         60          0





On standby

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS ;

or

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS where ARCHIVED_THREAD# !=0 ;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
               1            60               0            0
               1            60               1           60




on Stnadby

alter session set nls_date_format='dd-mon-yy hh24:mi:ss'
 
Session altered.


select app_thread, seq_app, tm_applied,
nvl(seq_rcvd,seq_app) seq_rcvd, nvl(tm_rcvd,tm_applied) tm_rcvd
from (select sequence# seq_app, FIRST_TIME tm_applied, thread# app_thread
from v$archived_log where applied = 'YES'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'YES'
group by thread# ) ),(select sequence# seq_rcvd, FIRST_TIME tm_rcvd, thread# rcvd_thread
from v$archived_log where applied = 'NO'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'NO'
group by thread# )
)
where rcvd_thread(+)= app_thread
/



APP_THREAD    SEQ_APP TM_APPLIED           SEQ_RCVD TM_RCVD
---------- ---------- ------------------ ---------- ------------------
         1         60 19-jan-13 07:04:29         60 19-jan-13 07:04:29





On primary

select (select name from V$DATABASE) database ,(select max(sequence#) from v$archived_log where dest_id=1) Current_primary_seq,
( select max(sequence#) from v$archived_log where next_time > sysdate - 1
and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) from v$archived_log
where next_time > sysdate - 1 and dest_id=2 and applied='NO'),0) from dual) "To be applied",
((select max(sequence#) from v$archived_log
where dest_id=1) - (select max(sequence#) from v$archived_log where dest_id=2)) "To be Shipped" from dual 
/

DATABASE  CURRENT_PRIMARY_SEQ   MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
VIHAAN                     60         60             0             0




On both The site Primary and Standby 

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,(select thread# thrd, max(sequence#) lhmax from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd 

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                60               60


 
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

    Thread Last Sequence Generated
---------- -----------------------
         1                      60
         1                      60



SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
     (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#))   APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;


    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     60                    60          0




On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
 
   Thread Last Sequence Generated
---------- -----------------------
         1                      60
         1                      60



On Physical Standby

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;


    Thread Last Sequence Generated
---------- -----------------------
         1                      60

set pagesize 0
select 
'Thread                     : ' || THREAD   		||chr(10)||
'Prod - Archived            : ' ||PR_Archived 		||chr(10)||
'STBY - Archived            : ' ||STBY_ARCHIVED 	||chr(10)||
'STBY - Applied             : ' ||STBY_APPLIED 		||chr(10)||
'Shipping GAP (PR -> STBY)  : '	||SHIPPING_GAP_STBY ||chr(10)||
'Applied GAP (STBY -> STBY) : '	||APPLIED_GAP_STBY_STBY
from (
select
t1 THREAD,
pricre "PR_ARCHIVED",
stdcre "STBY_ARCHIVED",
stdnapp "STBY_APPLIED",
pricre-stdcre "SHIPPING_GAP_STBY",
stdcre-stdnapp "APPLIED_GAP_STBY_STBY"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
;
set pagesize 80



Thread                     : 1
Prod - Archived            : 7105
STBY - Archived            : 7105
STBY - Applied             : 7104
Shipping GAP (PR -> STBY)  : 0
Applied GAP (STBY -> STBY) : 1





set linesize 200 pagesize 200
alter session set nls_date_format='dd-mm-YYYY hh24:mi'; 
col destination for a15
select /*+ parallel(8) */ sysdate, ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq   from v$archived_log val, v$database vdb
           where val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)   from v$archived_log val, v$database vdb
              where  val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),  0)   "applied_gap",
         (SELECT MAX (sequence#) highiest_seq    from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                     AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
              where val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'), 0)     "received_gap",
       NVL (
          (SELECT MAX (sequence#)   from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'), 0) "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),  0)   "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar;



SYSDATE             inst_id    dest_id dest_stat destination     applied_gap received_gap last_received_seq last_applied_seq
---------------- ---------- ---------- --------- --------------- ----------- ------------ ----------------- ----------------
17-08-2023 14:58          1          2 VALID     *********_****5              1          0       7105             7104






SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;




SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log
WHERE sequence# =(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES')
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log
WHERE sequence# =(SELECT MAX(sequence#) FROM v$archived_log );



LOGS             TIME                           THREAD#  SEQUENCE#
---------------- --------------------------- ---------- ----------
Last Applied :   11-JUN-23:08:35:19                   1      35567
Last Received :  11-JUN-23:08:44:26                   1      35568



-- Check that Archive Logs are being Shipped
-- This query needs to be run on the Primary database

SET PAGESIZE 300
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A20
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
);

=====================================================================

Oracle  DataGaurd on Standard Edition !!!!!!!!!!!!!!!!!!!
on standby !!!! SE !!!!!! dbvisit

--Prod !!

select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2,almax Max_seq_arch1,almax2 Max_seq_arch2
 from (select  max(sequence#) almax    from gv$archived_log where  THREAD#=1) al1,
      (select  max(sequence#) almax2   from gv$archived_log  where THREAD#=2) al2,
      (select max(sequence#) maxSCN1   from gv$log_history   where THREAD#=1) lh,
	  (select max(sequence#) maxSCN2   from gv$log_history   where THREAD#=2) ;

MAX_SEQ_HIST1 MAX_SEQ_HIST2 MAX_SEQ_ARCH1 MAX_SEQ_ARCH2
------------- ------------- ------------- -------------
       524064        470606        524064        470606



---on standby !!!! SE !!!!!! dbvisit
select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2
--,almax Max_seq_arch1,almax2 Max_seq_arch2
 from (select  max(sequence#) almax    from  gv$archived_log where THREAD#=1) al1,
      (select  max(sequence#) almax2   from gv$archived_log  where THREAD#=2) al2,
      (select max(sequence#) maxSCN1   from gv$log_history   where THREAD#=1) lh,
	  (select max(sequence#) maxSCN2   from gv$log_history   where THREAD#=2) ;

MAX_SEQ_HIST1 MAX_SEQ_HIST2
------------- -------------
       524064        470605





Physical Standby
set linesize 300
alter session set nls_date_format = 'dd-Mon-yyyy hh24:mi:ss';
select * from ( select sysdate,count(*) To_Be_Restored from x$kcvfh where fhrba_seq=0),
              ( select count(*) restored,max(fhrba_seq) max_sequence,min(fhrba_seq) min_sequence,max(fhscn) max_scn  from x$kcvfh where fhrba_seq!=0),
			  ( select count(*) total,min(fhscn) min_scn,min(fhrba_seq) min_seq from x$kcvfh)

On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
 

On Physical Standby

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

-- Standard edition 

select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2

--,almax,almax2

 from (select  max(sequence#) almax   from v$archived_log   where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=1)) al1,

      (select  max(sequence#) almax2   from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=2)) al2,

      (select max(sequence#) maxSCN1   from v$log_history  where first_time=(select max(first_time) from v$log_history where THREAD#=1)) lh,

  (select max(sequence#) maxSCN2   from v$log_history  where first_time=(select max(first_time) from v$log_history where THREAD#=2)) ;