Currently browsing category

Oracle

How To Install Oracle 10g Database Software on Windows

Oracle Database – What is a database? Database, in one of the simplest definitions can be said to be a huge collection of related data like numbers, characters etc. Normally, data will be stored in structures like tables which forms a part of the database. For creating and managing databases there …

What is Oracle – Meaning of the word Oracle

The word “Oracle” means – An authoritative person who divines the future . Oracle is  believed to be infallible. Oracle Corporation is software-hardware company whose main product is Oracle Database Software. Oracle Database Software is used to produce databases. Oracle software is proprietary, but they also owns the MySQL database software, …

Find Blocking Session, Locks, Waiting Session of Database

SQL> select l1.sid, ‘ IS BLOCKING ‘, l2.sid from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2; SQL> SELECT SID, DECODE(BLOCK, 0, ‘NO’, ‘YES’ ) BLOCKER, DECODE(REQUEST, 0, ‘NO’,’YES’ ) WAITER FROM V$LOCK WHERE REQUEST > 0 OR BLOCK > 0 ORDER BY …

How To Check If Database Got Corrupted?

Datablock level Corruption can be found out with the help of the following two queries. SQL> SELECT distinct ‘Data Block# ‘|| block# || ‘ of Data File ‘ || name || ‘ is corrupted.’ FROM v$backup_corruption a, v$datafile b WHERE a.file# = b.file#; 2 3 4 no rows selected SQL> …

How To Enable Audit Options In A Database

SQL> show parameter audit_trail NAME TYPE VALUE ———————————— ———– —— audit_trail string DB SQL> AUDIT ALTER ANY OUTLINE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER ANY ROLE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER DATABASE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER SEQUENCE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER …

SYS.AUD$ Purging

col OS_USERNAME for a10; col USERNAME for a10; col TIMESTAMP for a10; col OWNER for a10; col OBJ_NAME for a10; col ACTION for a20; select OS_USERNAME,USERNAME,TIMESTAMP,OWNER,OBJ_NAME,ACTION from dba_audit_trail order by TIMESTAMP; select Min(NTIMESTAMP#) “Last AUD$ Purge Date ” from sys.aud$ where OBJ$NAME !=’AUD$’; SQL> select min(timestamp#)from sys.aud$; MIN(NTIMESTAMP#) —————————————— 15-SEP-09 …

Auditing The Database for User Actions

Auditing is the monitoring and recording of selected user database actions. When you use standard auditing, Oracle Database writes the audit records to either to DBA_AUDIT_TRAIL (the SYS.AUD$ Table), the operating system audit trail, or to the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.In addition, the actions …

How To Drop and Recreate Temporary Tablespace In Oracle?

Oracle will not allow to drop the default temporary tablespace from a database. We have to assign a new default temp tablespace for the database to drop the current  tablespace. Suppose we need to drop and recreate the default temporary tablespace TEMP. Step 1: Create a new temporary tablespace temp1 …

Oracle SYS_CONTEXT Function

  How to check the database/instance name without having access to data dictionary views? How to get the client machine details from which I have connected to the Oracle database? How can I know my session details? These questions might have come up in the mind of many oracle database users …

How To Get The DDL For An Oracle User With All Roles And Privileges?

  Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package DBMS_METADATA can be used to generate the user creation DDL. >> To generate the User creation script SQL> SELECT dbms_metadata.get_ddl(‘USER’,’TEST’) FROM dual;   DBMS_METADATA.GET_DDL(‘USER’,’TEST’) …

How To Change Owner Of A Table In Oracle

Oracle provides no direct method to change the owner of a table. But we often face such situations to change the schema owner of the table. We have some work around to satisfy this requirement. Lets see them. 1. Export the table and import it into the new schema using …

How To Monitor Index Usage In Oracle

Most of the time we are skeptical of the index usage. Though the index is created for the table, we may not be able to determine if it is actually used. Inorder to determine if the index is used, we can use the MONITORING USAGE clause in the alter index …

Invisible Index In Oracle 11g

Oracle 11g offers a new feature called Invisible Indexes by which an index can be marked as invisible by the Oracle. Once an index is set as invisible, Oracle optimizer no longer considers it. This is a very useful feature where we do not want the index to take effect …

Active Session History In Oracle

Active Session History (ASH) is a quick solution to do performance analysis of the Oracle session based on time, module, action or SQL_ID and also to identify transient performance problem that lasts for few minutes. Each active session running in the database will be sampled (divided into smaller units) and the …