Oracle Common Commands Summary

1. User Management

• Create a user:

  CREATE USER username IDENTIFIED BY password;

• Drop a user:

  DROP USER username CASCADE;

• Change user password:

  ALTER USER username IDENTIFIED BY new_password;

• Grant privileges:

  GRANT privilege_name TO username;

• Show the current user:

  SHOW USER;

---

2. Privilege Management

• Grant a role:

  GRANT role_name TO username;

• Revoke a privilege:

  REVOKE privilege_name FROM username;

• Check user privileges:

  SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'username';

• Check role privileges:

  SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'username';

---

3. Tablespace Management

• Create a tablespace:

  CREATE TABLESPACE tablespace_name DATAFILE 'path_to_file' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;

• Drop a tablespace:

  DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

• View tablespace information:

  SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB FROM DBA_DATA_FILES;

---

4. Table Management

• Create a table:

  CREATE TABLE table_name (
      column1 datatype PRIMARY KEY,
      column2 datatype,
      column3 datatype
  );

• Drop a table:

  DROP TABLE table_name;

• Insert data into a table:

  INSERT INTO table_name (column1, column2) VALUES (value1, value2);

• Query data from a table:

  SELECT * FROM table_name WHERE condition;

• Update data in a table:

  UPDATE table_name SET column1 = value1 WHERE condition;

• Delete data from a table:

  DELETE FROM table_name WHERE condition;

---

5. Index and View Management

• Create an index:

  CREATE INDEX index_name ON table_name (column1);

• Drop an index:

  DROP INDEX index_name;

• Create a view:

  CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

• Drop a view:

  DROP VIEW view_name;

---

6. Backup and Recovery

• Backup the database using RMAN:

  RMAN> BACKUP DATABASE;

• Backup a tablespace using RMAN:

  RMAN> BACKUP TABLESPACE tablespace_name;

• Restore the database using RMAN:

  RMAN> RESTORE DATABASE;

• Backup the control file:

  ALTER DATABASE BACKUP CONTROLFILE TO 'path_to_controlfile';

---

7. Query and Performance Management

• View active sessions:

  SELECT SID, SERIAL#, USERNAME, STATUS FROM V$SESSION;

• Terminate a session:

  ALTER SYSTEM KILL SESSION 'sid,serial#';

• View session wait events:

  SELECT EVENT, WAIT_TIME FROM V$SESSION_WAIT;

• Display the execution plan for a query:

  EXPLAIN PLAN FOR SELECT * FROM table_name;

• View memory usage (SGA):

  SELECT * FROM V$SGA;

---

8. Transaction Management

• Commit a transaction:

  COMMIT;

• Rollback a transaction:

  ROLLBACK;

• Create a savepoint:

  SAVEPOINT savepoint_name;

• Rollback to a savepoint:

  ROLLBACK TO savepoint_name;

---

9. Archive Log Management

• Enable archive log mode:

  ALTER DATABASE ARCHIVELOG;

• Disable archive log mode:

  ALTER DATABASE NOARCHIVELOG;

• View archive log status:

  SELECT DEST_NAME, STATUS FROM V$ARCHIVE_DEST;

---

10. System Management Commands

Startup and Shutdown Database

• Start the database instance:

  STARTUP;

• Options:
  • STARTUP NOMOUNT; Starts the instance without loading the control files, typically used for creating or restoring control files.
  • STARTUP MOUNT; Loads the control files but does not open the data files, used for database recovery and maintenance tasks.
  • STARTUP OPEN; Fully starts and opens the database, allowing users to access the database and perform normal operations.

    STARTUP MOUNT; 
    select instance_name,status from v$instance;
    alter database open;
    select instance_name,status from v$instance;

• Shutdown the database instance:

  SHUTDOWN;

• Options:
  • SHUTDOWN IMMEDIATE;
  • SHUTDOWN ABORT;
  • SHUTDOWN NORMAL;

Database Instance Information

• View instance name:

  SELECT INSTANCE_NAME FROM V$INSTANCE;

• View database version:

  SELECT * FROM V$VERSION;

• View database startup time:

  SELECT INSTANCE_NAME, STARTUP_TIME FROM V$INSTANCE;

Adjusting Database Parameters

• Modify a database parameter:

  ALTER SYSTEM SET parameter_name = value SCOPE = BOTH;

• Options:
  • SCOPE=MEMORY: Only affects the current session.
  • SCOPE=SPFILE: Makes it persistent but not immediate.

Memory Management

• View memory usage (SGA):

  SELECT * FROM V$SGA;

• Adjust memory allocation:

  ALTER SYSTEM SET SGA_TARGET = value SCOPE = BOTH;

View System Processes

• Query background processes:

  SELECT NAME, DESCRIPTION FROM V$BGPROCESS;

---

11. Network Management

• Check the listener status:

  lsnrctl status;

• Start the listener:

  lsnrctl start;

• Stop the listener:

  lsnrctl stop;

---

12. Database Configuration and Instance Management

• View instance name:

  SELECT INSTANCE_NAME FROM V$INSTANCE;

• View database parameters:

  SELECT NAME, VALUE FROM V$PARAMETER;

• Modify database parameters:

  ALTER SYSTEM SET parameter_name = value SCOPE = BOTH;

---