Oracle DBA Command

Give me list of Oracle DBA commands.

[Pathak]

Following is list of Oracle DBA commands:

To see your Oracle user id
show user


Version of Oracle is running
select * from v$version;


Check which Oracle components are enabled/installed
select * from v$option;


Check if instance is running
select * from v$instance;


Check if database is in READ-WRITE mode
select open_mode from v$database;


Check if any data files need recovery
select * from v$recover_file;

If some datafiles are listed here, they would need recovery. When a database is in backup mode, the tablespaces in backup mode will also be in recovery mode and will show up in this view.

select * from v$backup;


Preparing a sorted list of wait events for wait analysis
select EVENT, TIME_WAITED, AVERAGE_WAIT from V$SYSTEM_EVENT order by TIME_WAITED desc;


List all DB users
SELECT USERNAME FROM DBA_USERS;


Change a DB user's password
ALTER USER <username> IDENTIFIED BY <new_password>;


Logging information regarding time consumption for performance analysis
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;


Determining the current free space in the Shared Pool
SELECT BYTES FROM V$SGASTAT WHERE POOL = 'shared pool' AND NAME = 'free memory';


Determining if there are UNUSABLE indexes
SELECT OWNER, INDEX_NAME FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE';


Determining the memory requirement of the shared pool
SELECT SUM(BYTES) FROM V$SGASTAT WHERE POOL = 'shared pool';


Determining areas within the shared pool sorted by size
SELECT BYTES, NAME FROM V$SGASTAT WHERE POOL = 'shared pool' ORDER BY BYTES;


Finding the chronological sequence of the overall PGA consumption
SELECT SUBSTR(S.END_INTERVAL_TIME, 1, 40) TIME, P.VALUE PGA_ALLOCATION FROM BA_HIST_SNAPSHOT S, DBA_HIST_PGASTAT P WHERE P.NAME = 'total PGA allocated' AND S.SNAP_ID = P.SNAP_ID ORDER BY P.SNAP_ID;


Set Oracle debug trace on Oracle process with UNIX PID <upid>
oradebug setospid <upid>


Set Oracle debug trace on Oracle process with Oracle PID <opid>
oradebug setorapid <opid>


Set Oracle debug trace on SQL session
oradebug setmypid