As a database admin who works on various different types of databases (Oracle, MySQL, PostgreSQL), I sometimes forget commands that are specific to a database product.
I work with Oracle database quite a fair bit and while there’s always the Oracle Database Documentation Library to refer to, it’s always the same few commands I find myself having to look up.

So I have decided to create an Oracle SQL cheat sheet to list all the Oracle specific SQL commands which I commonly “forget”.
This will be a growing list and I will add items in there as I come across them. If there’s anything you feel is worth adding to the list, please feel free to share in the comments.

Also, do you know about Oracle Database’s Recycle Bin?

Cheat Sheet Contents

SQL Login

$ sqlplus <username>@<service-name>/<password>

e.g: $ sqlplus johnsmith@oracle/password

Drop all tables in database

To drop all tables in a database, use the following script:

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 PURGE \;’ from user_tables;
SPOOL OFF
@DELETEME

Show all tables in database

select * from user_objects where object_type = ‘TABLE’;

or

select TABLE_NAME from tabs;

Show errors in Create Stored Procedures

CONNECT SYSTEM/MANAGER
CREATE PROCEDURE HR.PROC1 AS
BEGIN
: P1 := 1;
END;
/

Warning: Procedure created with compilation errors.

SHOW ERRORS PROCEDURE PROC1

NO ERRORS.

SHOW ERRORS PROCEDURE HR.PROC1

Errors for PROCEDURE HR PROC1:
LINE/COL ERROR
——————————————————–
3/3 PLS-00049: bad bind variable ‘P1’

Edwin Kwan is the founder and editor of Little Handy Tips and Wollongong Fitness. He is also the developer for the Google Custom Search WordPress plugin and Custom About Author WordPress plugin. He also has a keen interest in Photography . Find out more about him here.

Google+