skip to main content

SAP NetWeaver Newbie

Useful Oracle DBA Commands

This post will be updated with a list of useful commands for database administration.


1 To see your Oracle user id
show user


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


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


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


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


6. 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;



7. 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;


8. List all DB users
SELECT USERNAME FROM DBA_USERS;


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


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


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


12. 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';


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


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


15. 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;


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


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


18. Set Oracle debug trace on SQL session
oradebug setmypid

19. Find out free blocks in data buffer
select count(*) from v$bh where status='free'

20. Find out dirty buffers in v$bh
select status, count(*) from v$bh where dirty-'Y' group by status;

2 comments:

Email Subscription

Get every new post into your inbox by subscribing us.

Want a reason to subscribe?
1. This sitemap might convince you to subscribe.
2. We do not misuse email IDs. We respect privacy.

© 2008 - 2017 sapnwnewbie. All rights reserved.