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

Spinning the BIG DATA boomerang

ITS ALL ABOUT BIG DATA

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

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

DBA_TABLES View 

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

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

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

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