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
- Drop all tables in database
- Show all tables in database
- Show errors in Create Stored Procedure
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’
