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

Determining if there are UNUSABLE indexes

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

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


Read more ...

Spinning the BIG DATA boomerang

Thursday 13 June 2013

So, After a long time back to my Blogs.
This time with the latest trend in the IT industry. BIG DATA

Read more ...

A new breed of Database

Friday 15 February 2013

Paradigm shift in Database Technology

Gone are the days when organizations heavily used to rely on the their traditional RDBMS for keeping a track of all their transactions and operations. Today is the age of more smarter RDBMS which are made from ground zero for a specific purpose.We will be talking about the OLAP databases in particular, which unconditionally are necessary for every organization playing with large data.
OLAP databases help to derive the meaning of this large data for the organizations. OLAP databases play a major role when it comes to decision making times for organization. The OLAP databases are also called as the lifeline of Decision support systems.

Read more ...

Know your tables in Oracle

Thursday 14 February 2013


Have you ever thought of knowing statistics about your tables in oracle.
Statistics in terms of Owner,Tablespace, Number of Rows, Number of Blocks used, Number of Blocks empty,etc.
I was just trying to see what system tables does oracle has with it.
I tried
select * from tab;

BOOM BOOM what a surprise More then 2000 objects listed ...................
some of these objects are tables and some of these objects are views.

Read more ...

Installing JDK on Linux

Tuesday 5 February 2013

Installing SUN-JDK on Linux

While many of our applications are based on java framework. We definitely need the
Java development kit for running the java based applications.

So Lets start with What actually is a JDK

A Java Development Kit (JDK) is a program development environment for writing Java applets and
applications. It consists of a runtime environment that "sits on top" of the operating system
layer as well as the tools and programming that developers need to compile, debug, and run
applets and applications written in the Java language.

Read more ...

Passwordless SSH

Wednesday 30 January 2013

Setting passwordless ssh for  users on Linux

In this tutorial, I will be explaining how to setup passwordless ssh on multiple nodes
In Data warehousing world when you come across distributed Massively parallel processing
database products which are deployed in cluster environment, you will definitely need
passwordless ssh access to the nodes through your admin user of the Database.
Most of the time passwordless access is only needed from Master to Slave nodes

Read more ...

PostgreSQL Connection

Monday 21 January 2013

"PostgreSQL ODBC Connection from Windows to Linux" is my very first blog. Hope you all will find it useful. 

This blog will help you to create remote connections to PostgreSQL, as you all might know the PostgreSQL does not allow remote connections by default so it has to be configured accordingly. Please follow the steps mentioned below.

Note: The below scenario we are trying to make a remote connection from Windows to the PostgreSQL server which is Linux based.

Read more ...