Alert Log

The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.

Oracle will automatically create a new alert log file whenever the old one is deleted.

Location of Alert log file :

Oracle will write the alert.log file to the directory as specified by the BACKGROUND_DUMP_DEST parameter. If this parameter is not set, the alert.log will be created in the ORACLE_HOME/rdbms/trace directory.

SQL> show parameter BACKGROUND_DUMP_DEST

User can write to Alert log file:

 -- Write message to alert.log
 exec dbms_system.ksdwrt(2, 'Look Ma, I can write to the alert.log file!');
 PL/SQL procedure successfully completed.

 -- Flush the buffer
 exec dbms_system.ksdfls;
 PL/SQL procedure successfully completed.

Proper usage of Oracle Indexes

Reverse key Indexes:

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table).  For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed. 

Note: An Oracle reverse key index DOES NOT change the functionality of the index, and it's not the same as reversing the index key values.

In general, an Oracle reverse key index relieve data block contention (buffer busy waits) when inserting into any index where the index key is a monotonically increasing value which must be duplicated in the higher-level index nodes.

With the index key reversal, only the lowest-level index node is changed, and not all of the high-order index nodes, resulting in far faster insert speed.  For updates, Oracle updates the index nodes with each update statement.
Depending on the size of your update batch, it's often faster to drop, update and then re-build the index. If you have more than one CPU, you might consider doing a parallel index rebuild for faster speed. It splits-up the full-table scan:

Bitmap Index vs. B-tree Index: Which and When?

Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values--such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems.
In fact, a bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.

Query to get all Function based Indexes:

SELECT table_name,
  index_name,
  column_expression
FROM DBA_IND_EXPRESSIONS
WHERE TABLE_OWNER IN ( 'SCOTT')
  AND TABLE_NAME = 'EMP'
ORDER BY index_name,
  column_position;

BLOB Datatype

BLOB : Binary Large Object  is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.

Example: 
create table TABLE_BLOB
( id number,
  data blob);
  
 insert into table_blob values(1,utl_raw.cast_to_raw('Dhanunjay')); 
  
 select * from TABLE_BLOB ;

Tablespace Management

A Tablespace is a

• Logical storage unit
• Bridge between objects and physical files
• Collection of Physical files ( Eg : System.dbf, Sysaux.dbf, UNDO.dbf, userdata.dbf )

 Oracle contains 3 types of tablespaces, which is an allocation of space in the database that can contain schema objects.

• A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
• An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
• A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files.

Prerequisites to create a Tablespace:

• You must have the CREATE TABLESPACE system privilege 
• To create the SYSAUX tablespace, you must have the SYSDBA system privilege.

Syntax: 

SQL>  CREATE TABLESPACE <TBS>  DATAFILE <'/DISK2/ORADATA/RAC18/DATA.dbf'> SIZE 10M;

SQL> DROP TABLESPACE USERS;
With above statement, Tablespace will be dropped logically. Physically the datafiles will not be dropped. It just disconnects the link.
If we want to drop contents and datafiles, then we need to fire below query
SQL> DROP TABLESPACE USERS INCLUDING  CONTENTS AND DATAFILES;

BIGFILE | SMALLFILE

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (222) blocks.

SQL>  CREATE BIGFILE  TABLESPACE <TBS>  DATAFILE <'/DISK2/ORADATA/RAC18/DATA.dbf'> SIZE 10M;

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restriction on Bigfile Tablespaces You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE clause.

Uses of Tablespaces:

• Availability
• Performance

Working with Tablespaces:

• We can have 65536 tablespaces in database
• We can see the details of tablespaces in DBA_TABLESPACES
  SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
• Status of a tablespace can be Off line or On line ( READ ONLY or READ WRITE )
  SQL> ALTER TABLESPACE USERS READ ONLY;
  SQL> ALTER TABLESPACE USERS READ WRITE;
  SQL> ALTER TABLESPACE USERS OFFLINE;
• We can see the details of datafiles in DBA_DATA_FILES
  SQL> SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024  FROM DBA_DATA_FILES;
• To reuse a physical file
  SQL> CREATE TABLESPACE TBS DATAFILE  '</loc> ' REUSE;

Resizing the tablespace:

• SQL> ALTER TABLESPACE USERS ADD DATAFILE </loc> SIZE 20M;
• SQL> ALTER TABLESPACE USERS DROP DATAFILE </loc> ;
• SQL> ALTER DATABASE DATAFILE 4 RESIZE 40M;
  OR
  SQL> ALTER DATABASE DATAFILE  </loc>  RESIZE 40M;
• SQL> ALTER DATABSE DATAFILE 4 AUTO EXTEND ON;

Temporary Tablespaces:

It is allocation of space in the database that can contain schema objects for the duration of a session.

How to get Create Table script in Oracle

SELECT DBMS_METADATA.GET_DDL('TABLE', 'TABLE_NAME') FROM dual;

Boolean Datatype in Oracle

There is no such type of datatype called Boolean in Oracle

Drop a Database

SQL> SHUT IMMEDIATE;

SQL> STARTUP MOUNT EXCLUSIVE RESTRICT;

SQL> DROP DATABASE ;

Oracle Database Creation in Silent Mode

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL

Enter SYS user password:

Enter SYSTEM user password:

Enter DBSMNP user password:

Enter SYSMAN user password:

Copying Database files

1% Complete

37% Complete

Creating and starting Oracle Instance

40% Complete

62% Complete

Completing Database Creation

66% Complete

100% Complete

Look at th Log file "D:\Oracle......log" for further details.

Foreign key relationship between two different schemas

Let use assume 2 schemas scott1 and scott2.
In scott1 we have emp table and in scott2 we have dept table. Now take the requirement to establish foreign key relation ship between scott1.emp and scott2.dept;

SQL> GRANT REFERENCES ON SCOTT2.DEPT TO SCOTT1

SQL> CONN SCOTT1/PASSWORD

SQL> ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPTID FOREIGN KEY (SCOTT_DEPT_ID) REFERENCES SCOTT2.DEPT (ID);