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

Scripts for Indexes - Part 4 Posted by MOHAMMED ABDUL HAMEED on 7:21 AM

CREATE INDEX : Creates an index on a table where Duplicate values are allowed.

CREATE INDEX Syntax : CREATE INDEX index_name ON table_name (column1, column2, ...);

SQL> CREATE INDEX emp_ename ON emp(ename);
 
         Index created.

CREATE UNIQUE INDEX : Creates a unique index on a table where Duplicate values are not allowed:

CREATE UNIQUE INDEX Syntax :CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

SQL> CREATE UNIQUE INDEX dept_unique_index ON dept                  (dname);

                Index created.

DROP INDEX : To Drop the index.

DROP INDEX Syntax : DROP INDEX index_name;

SQL> DROP INDEX emp_ename; 

        Index dropped.

Indexes Data Dictionary Views - Part 3 Posted by MOHAMMED ABDUL HAMEED on 4:54 AM

DBA_INDEXES : DBA view describes indexes on all tables in the database.

ALL_INDEXES : ALL view describes indexes on all tables accessible to the user.

USER_INDEXES : USER view is restricted to indexes owned by the user. 

INDEX_STATS : Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.

INDEX_HISTOGRAM : Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.

Type of Indexes in Oracle - Part 2 Posted by MOHAMMED ABDUL HAMEED on 2:48 PM

B-Tree index :- B-tree is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.

This index is default index in oracle, Good for high cardinality (not identical or unique values like usernames, user-ids)

Bitmap index:- Bitmap index is a special kind of database index that uses bitmaps.

Excellent in Data warehouse environments Good for low cardinality values ( Less repeated values ex: Gender)

Function Based index:- Function-based indexes allow you to create an index based on a function or expression. 

Good for columns that have SQL functions applied to them. This can be used with either a B-tree or bitmap index.
           
Concatenate or Composite index: - A database composite index or multi-column index is an index that is based on several columns.

Reverse index:-  A form of B-tree index; useful to balance I/O in an index that has many sequential inserts.

Descending Index:- A form of B-tree index; used with indexes where corresponding column values are sorted in a descending order (the default order is ascending).

Global Partitioned Index:- Global index across all partitions in a partitioned table or regular table. This can be a B-tree index type and can’t be a bitmap index type.

Local Partitioned Index:- Local index based on individual partitions in a partitioned table. This can be either a B-tree or bitmap index type.

Indexes in Oracle - Part 1 Posted by MOHAMMED ABDUL HAMEED on 1:20 PM

An indexes are schema objects that contains an entry for each value that appears in the indexed columns of the table and provides fast access to rows.Index is a database object that makes data retrieval faster.

 We can say Indexes are the easiest way to improve the performance of long running queries with full tables scan. By properly indexing large tables query completion time can go from hours to minutes.

Indexes created on column and that column is called index key.

Indexes not only improve Select queries but also it improves Update & Delete.

Oracle 12.2 New Feature: SQL*Plus History Posted by MOHAMMED ABDUL HAMEED on 1:17 PM

SQL*Plus History is not enabled by default we need to enable it

Sql > set hist on

We can check the history is enable or not by using the below command :

Sql > show history
history is ON and set to "100"

Note :  The default length of the history is 100.

To change the length of the history we can use :

Sql > set hist 50

Check the History :

Sql > history
2 show history
3 select tablespace_name from dba_tablspaces;
4 select host_name from v$instance;


To run a command from the history use the history run command

Sql > hist 4 run

Host_Name
-----------------
mahameed

Help History :

SQL> help history
HISTORY
-------
Stores, lists, executes, edits of the commands
entered during the current SQL*Plus session.
 HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]
N is the entry number listed in the history list.
Use this number to recall, edit or delete the command.
 Example:
HIST 3 RUN - will run the 3rd entry from the list.
HIST[ORY] without any option will list all entries in the list.

Oracle Database Vault - Part 10 ( Granting access by using Realm ) Posted by MOHAMMED ABDUL HAMEED on 4:22 AM

To Allow any specific user to access the table.

To allow the user mahameeditpro to access the table emp.salinfo , the below mention steps need to followed :

Click on the Realm

Select the Realm and select Realm Authorizations -> Click Create

Select the specific user from the Drop down list -> Click "OK"

Now , Lets Login to the database by mahameeditpro and check the access.

Now you can access the particular table by using the specific user , Hope its helpful. 

Oracle Database Vault - Part 9 ( Creating a Realm) Posted by MOHAMMED ABDUL HAMEED on 2:08 AM

Realms : It is defined as boundary or protection zone around application data.

Click on "Create" and provide the appropriate Name and Description.

Click "Ok"-> Select new "Realm" and Click on "Edit" . 

Use the drop-down list to select the owner,object type and enter the object name.

To restrict access to an entire schema,leave the " % " in the object name field.

Click ok 

Now , if we try to query then it will return ORA-01031: insufficient privileges

Oracle Database Vault - Part 8 ( Assigning the Rule sets) Posted by MOHAMMED ABDUL HAMEED on 2:06 PM

By creating the Rule Set , we can unable the functionality of the SYS User.

Steps : Login to Database Vault Page.

Select Rule Sets

Click on Create

Provide the name as : SYS_ADMIN_USER -> OK

Select from List " SYS_ADMIN_USER" and click on edit

Scroll down to " Rules Associated to the Rule Set" and Click on "Create"

Use the name “SYS_ADMIN_USER”

Enter this in the expression  field – dvf.f$session_user = ‘SYS’

Click on OK to save it, then on OK again.

Go back to the home page, and click on “Command Rules”

Select the “CREATE USER” item and click on “Edit”

Leave the command as “CREATE USER”, and in the “RULE SET” drop-down choose “SYS_ADMIN_USER”

Click on OK

The SYS user can now create users in the database.

Oracle Database Vault - Part 7 ( Restrictions on SYS User Privileges) Posted by MOHAMMED ABDUL HAMEED on 9:36 AM

By enabling database vault it restrict the SYS user from performing some database administration task.

Oracle Database Vault - Part 6 ( Logging into the Database Vault Web Page ) Posted by MOHAMMED ABDUL HAMEED on 9:14 AM

Oracle Database Vault - Part 5 ( Granting the required privileges ) Posted by MOHAMMED ABDUL HAMEED on 9:09 AM

Oracle Database Vault - Part 4 ( Implementing Database Vault with DBCA ) Posted by MOHAMMED ABDUL HAMEED on 9:06 AM