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.
Cheat Sheet Contents
- SQL Login
- Drop all tables in database
- Show all tables in database
- Show errors in Create Stored Procedure
$ sqlplus <username>@<service-name>/<password>
e.g: $ sqlplus johnsmith@oracle/password
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 \
select ‘drop table ‘, table_name, ‘cascade constraints PURGE \;’ from user_tables;
select * from user_objects where object_type = ‘TABLE’;
select TABLE_NAME from tabs;
CREATE PROCEDURE HR.PROC1 AS
: P1 := 1;
Warning: Procedure created with compilation errors.
SHOW ERRORS PROCEDURE PROC1
SHOW ERRORS PROCEDURE HR.PROC1
Errors for PROCEDURE HR PROC1:
3/3 PLS-00049: bad bind variable ‘P1’