Oracle
Local
External
Connecting as SYSDBA
See http://www.adp-gmbh.ch/ora/admin/connect_sysdba_sysoper.html
How to Drop All Tables
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET ESCAPE \
SPOOL DELETEME.SQL
select 'drop table ', table_name, 'cascade constraints \;' from user_tables;
spool off
@DELETEME.SQL
#then to removed the delete files
PURGE RECYCLEBIN;
Error ORA 12505 'TNS:listener does not currently know of SID given in connect descriptor'
Solutions
- Database is down, restart it (or restart the machine)
Limiting the Number of Rows Returned
SELECT * FROM TABLENAME WHERE ROWNUM <= 10;
Scripting
List Stuff
# Switch to another user
CONNECT USERNAME/PASSWORD;
# See what columns are in the TABLE of TABLES
DESCRIBE USER_TABLES;
DESC USER_TABLES;
# Show your tables
SELECT TABLE_NAME FROM USER_TABLES;
# drop a table
DROP TABLE {TABLENAME};
# drop a table that has constraints
DROP TABLE {TABLENAME} CASCADE CONSTRAINTS;
# print a list of the commands needed to drop all your tables to the console
SELECT 'DROP TABLE ', TABLE_NAME, 'CASCADE CONSTRAINTS;' FROM USER_TABLES;