Oracle

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired-in oracle 10g

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL# 

---------- ---------- ---------- 

         1        130        620

 alter system kill session '130,620';

For increasing table width in oracle

sql>column column_name format a30

sql>set linesize 300

For increasing table height in oracle

sql>set pagesize 50000

50,000 lines is the max. If you try to set PEGESIZE higher than that, you get the default, which is 14.

For clearing old values from a variable

sql>undefine starttime;

Remove Column Header into the Output Text file

sql>set heading off;

FINDING THE LAST AND FIRST ELEMENT AFTER SORTING

select * from(select sal from emp order  by sal asc) where rownum=1;

or

select * from(select sal from emp order  by sal desc) where rownum=1;

SQLPLUS query output to *.csv or *.txt format

SQL>spool out.csv SQL>select table_name from user_tables; CHRIS>spool off

Oracle - drop multiple table in a single query

You can generate a list of DROP TABLE commands with the query below:

SELECT 'DROP TABLE ' || table_name || ';' FROM user_tables;

After that you remove your six tables you want to keep and execute the other commands. Or you add a WHERE table_name NOT IN (...) clause to the query.

courtesy: https://stackoverflow.com/questions/26968999/oracle-drop-multiple-table-in-a-single-query

To find all "child" tables that point to your table, which is helpful before modifications like dropping a table, use this query:

sql

SELECT 
    table_name AS child_table, 
    constraint_name AS fk_name
FROM user_constraints
WHERE r_constraint_name IN (
    SELECT constraint_name 
    FROM user_constraints 
    WHERE table_name = 'YOUR_TABLE_NAME' 
      AND constraint_type IN ('P', 'U')
);