Pages

Oracle DBA commands

Sunday 25 August 2013
Some  Important Oracle DBA commands

Hello there all of you.
Today I am just going to jot down some essential DBA commands which are useful for every DBA in their day to day life. These commands can be as well handy for a Database developer
The commands are specific to Oracle Database.

Let start with knowing the version of your Oracle database

To see the version of the Oracle database
select * from v$version;
select * from v$version where banner like 'Oracle%';

To see the user privileges
select * from dba_role_privs;
select * form dba_role_privs where GRANTEE like 'NAVIN';
select * from dba_sys_privs;

To see which tables belon to which a specific user
select * from all_catalog where OWNER='NAVIN';


To see the current user
show user;

To see whether the oracle instance is running
select * from v$instance;


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


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

List all DB users
SELECT USERNAME FROM DBA_USERS;

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 current free space in the Shared Pool
SELECT BYTES FROM V$SGASTAT WHERE POOL = 'shared pool' AND NAME = 'free memory';

List all the users and their account status ( Locked or Open )
select username, account_status from dba_users;

To unlock a specific user account
alter user scott account unlock;

To check which tables does the current users owns
select table_name from user_tables;

To show the current database
select * from global_name;

To find all tables having a name beginning with EST and columns containing CALLREF anywhere in their names.
SELECT table_name, column_name
FROM cols
WHERE table_name LIKE 'EST%'
AND column_name LIKE '%CALLREF%';

To know your Oracle Home directory

SQL>var OHM varchar2(100);
SQL>EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL> PRINT OHM

To know the instance name

select sys_context('USERENV','INSTANCE_NAME') from dual;
select * from global_name;
select INSTANCE_NAME frim v$instace;

Hope these commands are useful for you.
Most of these commands need to be converted to use when you are dealing with different database like PostgreSQL
For list of such admin commands on PostgreSQL, You can visit my blogs dedicated to PostgreSQL database here www.postgresql-database.blogspot.in


Thanks



Read more ...