DBConsole Not starting: An instance of Oracle Enterprise Manager Database Control is already running Posted by MOHAMMED ABDUL HAMEED on 4:46 AM

Cause : 

Process did not shutdown cleanly form a previous run.

Workaround :

1) Ensure all dbconsole related processes are not running:

ps ef | grep dbconsole
ps ef| grep emagent
kill -9


2) Start the dbconsole

$ emctl start dbconsole

Keeping SSH Connections Alive Posted by MOHAMMED ABDUL HAMEED on 5:51 AM

We often experience problems when using the SSH connections from the PuTTY such as dropped , inactive or unresponsive connections.

Workaround :

1.Start PuTTY.

2.Load your connection session.

3.In the Category pane, click Connection.

4.Under Sending of null packets to keep session active, in the Seconds between keepalives, type 240.

5.With this configuration, PuTTY sends a packet to the server every 240 seconds (4 minutes) to keep    the connection alive.

6.Click Save.

Fix : Unable to spawn jobq slave process, slot 0, error 1089 Posted by MOHAMMED ABDUL HAMEED on 4:38 AM

While shutting down our test database we observe the below mention message in the alert log continuously : 

kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089

Probably its because of below mention reasons :

1) If we have fired "shut immediate" and presses "^C" to cancel the command.

OR

2) Some memory issue and you unable to down / start the database again.

Workaround :

Analysis the condition if its not due to memory then startup force.

$ sqlplus / as sysdba

SQL> startup force;

This will cause your database will be force fully down and start it again.

Note: Shut abort will not work in this situation.

Killing the Processes from Command Prompt Posted by MOHAMMED ABDUL HAMEED on 1:06 PM

Advanced Scripts for Indexes - Part 5 Posted by MOHAMMED ABDUL HAMEED on 3:37 PM

Rebuild Index Syntax

Syntax: alter index index_name rebuild;

SQL> alter index emp_ename rebuild;

Index altered.

Rebuild Index of multiple users :

Syntax : Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;' from all_indexes Where Owner = 'USER NAME'; 

SQL> Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;' from all_indexes Where Owner = 'SCOTT'; 

alter index SCOTT.EMP_ENAME rebuild;
alter index SCOTT.DEPT_UNIQUE_INDEX rebuild;

Collect Statistics on an Index :

Syntax : ALTER INDEX index_name REBUILD COMPUTE STATISTICS;

SQL> alter index emp_ename REBUILD COMPUTE STATISTICS; 

Index altered.

Move Table and Index to Other Table Space

Syntax: 
ALTER TABLE schema.table_name MOVE TABLESPACE TABLESPACE_NAME;

SQL Code:
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ’ts’ ||';'
from ALL_TABLES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

Syntax: 
ALTER INDEX schema.index_name REBUILD TABLESPACE TABLESPACE_NAME;

SQL Code
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || 'idx’;'
from ALL_INDEXES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

How to identify the index unusable

SQL Code:
select index_name, status, owner
from all_indexes
where STATUS = 'UNUSABLE';

How to rebuild index unusable

SQL Code:
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes

where STATUS = 'UNUSABLE';

Check Index Creation or Index Rebuild Date in Oracle

SQL> select table_name,index_name,last_analyzed from dba_indexes where table_name='xxxxxxx';

Note : Last_Analyzed column date changes when the index is rebuild.

Find index status of a Particular Table.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='xxxxx';

How to find the index status of Particular Schema.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE OWNER='xxxxxx';

Check particular index size

SQL> select segment_name,sum(bytes)/1024/1024 as "SIZE in MB" from user_segments where segment_name='XXXXX' group by segment_name;

Check all the indexes of a particular table.

SQL> SELECT DISTINCT TABLE_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='xxxxxxx';

Find Global & Local Indexes in Oracle Partitions


SQL> select table_name,index_name,locality from user_part_indexes where table_name='XXXXX';