#1
9th February 2013, 12:16 PM
| |||
| |||
Oracle DBA Command
Give me list of Oracle DBA commands.
|
#2
11th February 2013, 09:35 AM
| |||
| |||
Re: Oracle DBA Command
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 |
|