Oracle: Query column name with reserved keyword from a table

I happen to see a table(say X) created with column name using key words like NUMBER, GROUP . I could query the table X for all columns as

Select * from X;

To query one or two particular columns named as keywords, you need to use double quotes.

Select "NUMBER" from X;

Make note of the 'case'  as Select "number" from X; won't work.

Where table creation script is:

Create table X

( "NUMBER" varchar2(90),

   Creation_date date,

...

...
...);

Oracle: Memory Parameters Math

It is always challenging for Oracle Administrator to define Oracle Memory Parameters. Recently while creating new database with Oracle 11g (11.2.0.3) using DBCA, I noticed the values DBCA assigns to the following memory parameters.

Total Memory: 1024 M (or 1 GB)
Shared Pool - 160 M
Buffer Cache- 482 M
Java Pool - 48 M
Large Pool - 76M
PGA - 256 M

These value can help Oracle DBA to multiply or divide to get accurate numbers. I chose 'custom database' option with 8K block size in DBCA.

Oracle: Change the SQL Prompt with Glogin.sql

DBAs usually work with so many servers, databases at the same time. They toggle from one screen to another within few seconds. There are a lot hidden opportunities where you can make mistakes. Being a superuser  brings in more responsibilities and adds up cautions.

Following are some best practices to ensure you are on right database before you do any critical task like shutdown or drop is very relevant :

1. Login as SYSDBA only when required, otherwise create a database user and grant DBA role.
2. Use shell script to perform task like shutdown, startup of databases.
3. Change the color of putty sessions for example Red for Production, Blue for QA, Yellow for Test.
4. SQL Prompt: Change the SQL prompt on the database to show the Database Name/SID with user whom you have connected as. Oracle by default calls login.sql or glogin.sql whenever 'sqlplus' command is given on a UNIX machine. The script login.sql or glogin.sql is located in $ORACLE_HOME/sqlplus/admin. You can add a line to query from v$instance table something like below:

select host_name||':'||user||' @ '||UPPER(instance_name) X from v$instance;

The problem with the above SQL in login.sql is that SQLPLUS will hang if the database is down. When database is down, you cannot query any v$ views. The other way which does the same but safe way is by the command below:

The SQL prompt will be SYSTEM@TESTDB SQL> .