Useful Oracle Commands

1. Some useful oracle commands To start a session as sqlplus sys@tnsname as sysdba; sysdba: To start a sysdba session sqlplus "/as sysdba" under Windows (9iAS): To list all tables in SELECT table_name FROM user_tables; current schema: or, all tables current user SELECT table_name FROM all_tables; has access to: SELECT username FROM all_users ORDER BY username; To list all schemas: SET PAUSE ON; To turn pause on: To list top n rows of a SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n; table in order: Show current database: SELECT * FROM global_name; CONNECT schema/password@tnsname; Use database: SHOW USER; Show who I am: DESC tablename; Describe table: SET PAGESIZE 66; Set display rows: Read field constraints:

SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';

Copy table from foreign host to here: Start SQLPLUS without login: Change a user's password: Unlock an account

COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename; SQLPLUS /NOLOG ALTER USER user IDENTIFIED BY password; ALTER USER user ACCOUNT UNLOCK;