Oracle DBA commands

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