List locked objects


SELECT VLO.OS_USER_NAME               “OS USERNAME”,
VLO.ORACLE_USERNAME            “DB USER”,
VP.SPID                        “SPID”,
AO.OWNER                       “OWNER”,
AO.OBJECT_NAME                 “OBJECT LOCKED”,
AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE, 1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL) “MODE OF LOCK”,
VS.STATUS                      “CURRENT STATUS”
FROM   V$LOCKED_OBJECT VLO,
ALL_OBJECTS AO,
V$SESSION VS,
V$PROCESS VP
WHERE  VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

Querying sequences


SELECT seq.CURRVAL 
FROM   dual;
SELECT seq.NEXTVAL 
FROM   dual;

if you execute the first script before executing second one, you will get the error

ORA-08002: 
"sequence %s.CURRVAL is not yet defined in this session"
*Cause: sequence CURRVAL has been selected before sequence NEXTVAL
*Action: select NEXTVAL from the sequence before selecting CURRVAL

The solution is simple. Execute the second script initially.

List indexes which you named intentionally


Here’s a nice Oracle SQL script that lists indexes which you named on purpose.

SELECT index_name 
FROM all_indexes 
WHERE table_owner = 'SCHEME' --schema name
AND table_name = 'TABLE' --table name
AND index_name NOT LIKE 'SYS%';

if you comment out this part, this script lists all indexes.

AND index_name NOT LIKE 'SYS%';

Oracle prefixes “SYS_” constraints and indexes when u dont give names to them. That’s why we add the condition.

Find the tables referencing a specific table by foreign key


SELECT a.owner || '.' || a.table_name "Referenced Table", 
 b.owner || '.' || b.table_name "Referenced by", 
 b.constraint_name "Foreign Key", 
 b.constraint_type 
FROM all_constraints a, 
 all_constraints b 
WHERE b.constraint_type = 'R' 
AND a.constraint_name = b.r_constraint_name 
AND a.table_name = 'TABLE1' -- Table name
AND a.owner = b.r_owner
ORDER BY 1;

Source: http://www.databasejournal.com/features/oracle/article.php/3665591/Finding-Foreign-Key-Constraints-in-Oracle.htm