Oracle tips

 1. Query how to get table name by constraint name.

SELECT owner,
       table_name
FROM   dba_constraints
WHERE  constraint_name = <<your CONSTRAINT name>>

 2. Query how to get primary by table name.

SELECT cols.table_name,
       cols.column_name,
       cols.position,
       cons.status,
       cons.owner
FROM   all_constraints cons,
       all_cons_columns cols
WHERE  cols.table_name = '<<your TABLE NAME name>>'
       AND cons.constraint_type = 'P'
       AND cons.constraint_name = cols.constraint_name
       AND cons.owner = cols.owner
ORDER  BY cols.table_name,
          cols.position; 

3. Query how to show running processes in Oracle DB

SELECT sess.process, 
       sess.status, 
       sess.username, 
       sess.schemaname, 
       SQL.sql_text 
FROM   v$session sess, 
       v$sql SQL 
WHERE  SQL.sql_id(+) = sess.sql_id 
       AND sess.username = 'YOUR_SCHEMA' 
       AND sess.status = 'ACTIVE';

4. Query check SID and SERIAL FOR KILL SESSION

SELECT sid, 
       serial#, 
       status, 
       server 
FROM   v$session 
WHERE  username = 'YOUR_SCHEMA'; 

5. Query kill process.

ALTER SYSTEM kill SESSION 'SID,SERIAL'; 

6. Query check all constraint in Oracle.

SELECT table_name,
       constraint_name,
       status,
       owner
FROM   all_constraints; 

7. Query constraint name or table name etc.

SELECT table_name,
       constraint_name,
       status,
       owner
FROM   all_constraints
WHERE  constraint_name = 'YOUR_CONSTRAINT_NAME';

8. Flashback Query

If you for error delete rows on oracle, you can do data recovery with that query and depending on the number of  transaction you have been on databases, if you made a lot of transaction the retention time is short. if you truncate the table you can't data recovery with that query.

SELECT * 
FROM   your_name_table AS OF timestamp(systimestamp - interval '60' minute); 

8. DB Link in Oracle 11g

Run next command from SQL*Plus or JDeveloper.

CREATE DATABASE LINK your_link_name 

CONNECT TO your_remote_db 

IDENTIFIED BY your_password_remote_db 

USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST =your_remote_ip)(PORT = 1521)) (CONNECT_DATA = (SID = your_schema)))'; 

Check remote table:

SELECT * 
FROM   your_table_remote@your_link_name; 

9. Database name

SELECT ora_database_name
FROM   dual; 

10. Database Schema

SELECT Sys_context('USERENV', 'current_schema')
FROM   dual; 

11. SID

SELECT Sys_context('USERENV', 'SID')
FROM   dual; 

12. Check if exist store procedure

SELECT *
FROM   all_objects
WHERE  object_type = 'PROCEDURE'
       AND object_name = 'YOUR_PROCEDURE_NAME'
       AND owner = 'YOUR_SCHEMA_NAME'; 

References:https://stackoverflow.com/questions/5247858/get-table-name-by-constraint-name/5247901