Oracle Cheatsheet

Start SQL*Plus without login:

sqlplus /nolog

To start a session as sysdba:

sqlplus sys@tnsname AS sysdba

Show connected user:

SHOW USER;

Set display rows:

SET pagesize 1000;

Set line-size:

SET linesize 150;

Describe table:

DESC table_name;

List all tables in current schema:

SELECT table_name FROM user_tables;

Or, all tables current user has access to:

SELECT table_name FROM all_tables;

List views:

SELECT view_name FROM user_views;

List constraints:

SELECT constraint_name FROM user_constraints;

To list all schemas:

SELECT username FROM all_users ORDER BY username;

To list top n rows of a table

SELECT * FROM tablename WHERE ROWNUM &lt;= n;

Show current database:

SELECT name FROM v$database;
SELECT * FROM global_name;

Change a user’s password:

ALTER USER USER IDENTIFIED BY password;

List parameters:

SHOW PARAMETERS parameter_name;
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%xyz%'

List database files, redo-log files and control-files:

SELECT name FROM v$datafile;
SELECT member FROM v$logfile;
SELECT name FROM v$controlfile;

List roles for user:

SELECT * FROM sys.dba_role_privs WHERE grantee LIKE '%user%';

Oracle RDBMS version:

SELECT * FROM v$version;

Share and make me happy:

No comments yet.

No trackbacks yet.

Ramin Hossaini (blog)