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;
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;
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';
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);
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;
FROM your_table_remote@your_link_name;
9. Database name
SELECT ora_database_name
FROM dual;
FROM dual;
10. Database Schema
SELECT Sys_context('USERENV', 'current_schema')
FROM dual;
FROM dual;
11. SID
SELECT Sys_context('USERENV', 'SID')
FROM dual;
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';
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