To unplug a database, use the following commands. It is recomm ended that the path used match the datafile storage location. ALTER PLUGGABLE DATABASE CLOSE. SQL Facts SQL stands for Structured Query Language SQL is pronounced sequel SQL is declarative language SQL is used to access & manipulate data in databases Top SQL DBs are MS SQL Server, Oracle, DB2, and MySQL SQL Commands Categories Data Query Language (DQL) SELECT - Retrieve data from table(s). Liquibase (LB) – version control your Oracle Database Schema – either do an UPDATE, ROLLBACK, or we'll generate changesets for you using GENSCHEMA or GENOBJECT. LOAD – Take CSV and shove it into a table. Useful when you don't have SQL.Loader handy. OERR – Oracle Errror Message Lookups, for ORA and PLS errors.
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.
To list all users and profile, run the following command: SQL SELECT USERNAME, ACCOUNTSTATUS, PROFILE FROM DBAUSERS; To list all roles, run the following command: SQL SELECT. FROM DBAROLES; To create a user profile, run the following command: SQL CREATE PROFILE MYPROFILE LIMIT; To list all user profiles, run the following command.
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 @/
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
Oracle Sql Commands Cheat Sheet
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;
/
Oracle Pl Sql Cheat Sheet
Warning: Procedure created with compilation errors.
SHOW ERRORS PROCEDURE PROC1
NO ERRORS.
SHOW ERRORS PROCEDURE HR.PROC1
Sql Coding Cheat Sheet
Errors for PROCEDURE HR PROC1:
LINE/COL ERROR
——————————————————–
3/3 PLS-00049: bad bind variable ‘P1'
