Calculate all Datafile Size Individually

Whenever you say the size of the database it means the summation of the datafiles. An oracle database consists of data files, redo logfiles, control files, temporary files. Database's size comes actually from these datafiles. To know about the size of all datafiles:

SQL>select sum(bytes)/1024/1024 from dba_data_files;

Size of all Temp Files:
SQL>select sum(bytes)/1024/1024 "Meg" from dba_temp_files;

Size of the on-line redo-logs:
SQL>select sum(bytes)/1024/1024 "Meg" from v$log;

Size of the control files:
SQL>select sum(BLOCK_SIZE*FILE_SIZE_BLOCK/1024/1024) "Meg" from v$controlfile;

To get the total size of the database:
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

Read More

Temporary Tablespace Infomation using Query

1) To know how much space is used in temporary segments:

SQL>Select extent_size*8192*used_extents/1024/1024/1024"space used in GigaByte" from v$sort_segment;

Space used in GigaByte
----------------------
196.5847621

SQl>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_blocks, total_blocks from v$sort_segment; 

Tablespace_name Extent_size Total_extents Used_extents Free_extents Max_used_blocks Total_blocks
--------------- ----------- ------------- ------------- ----------- ---------------- ------------
Test        128         11896          4526           7370      1449216          1522688

2) To know SQL ID and Which type of Sort Segment issue:

SQL>select username,sqladdr, sql_id, tablespace, segtype, extents,blocks from v$tempseg_usage;

3) To know which SQL is using Temporary segments query:

SQL>select s.sql_text, t.username, t.tablespace, t.segtype, t.blocs, t.extents 

from v$sql s, v$tempseg_usage t

where t.sql_id=s.sql_id;

4) Information about tablespace containing sort segments:

SQL>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size from v$sort_segmrnt;

5)The users who is performng sort operation in Temp Segments:

SQL>select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid, a.serial#,a.username, a.osuser, a.status from v$session a,v$sort_usage b
where a.saddr=b.session_addr
order by b.tablespace, b.segfile#, b.segblk#,b.blocks;

Read More

Free space in Temporary Tablespace

A default temporary tablespace is specified in a database at the time of the database creation,By using DEFAULT TEMPORARY TABLESPACE in the create database statement.You can drop the default temporary tablespace,then system tablespace will be used as default temporary tablespace.Default temporary tablespace cannot make permanent or take it offline.The DBA_FREE_SPACE view allows you to show about free space in a tablespace but it shows only about permanent tablespace. You have to query V$TEMP_SPACE_HEADER view to know about the free space of temporary tablespace.You can see the use of DBA_FREE_SPACE and V$TEMP_SPACE_HEADER view as follows:

1) If you have TEST as permanent tablespace and TEMP as temporary tablespace and you run query DBA_FREE_SPACE then it will show TEST in list the list but TEMP is not listed.
 

SQL>select tablespace_name from dba_free_space;

TABLESPACE_NAME
----------------
UNDO1
SYSAUX
SYSTEM
TEST

4 rows selected.

2) To know information about free space of temporary tablespace

SQL>select tablespace_name.file_id,bytes_used,bytes_free from v$temp_space_header;

TABLESPACE_NAME   FILE_ID  BYTES_USED BYTES_FREE
----------------- -------- --------- ------------
TEMP              5894714  19625687
 

Read More

Data Dictionary Creation In Oracle

Oracle creates data dictionary automatically when you create database by using Database Configuration Assistant method.But when you create database manually then you must run several scripts to create data dictionary objects.
There are three scripts you use to create data dictionary objects:

 

• SQL>@?/rdbms/admin/catalog.sql: Creates the data dictionary and public synonyms for many of its views.
• SQL>@?/rdbms/admin/catproc.sql: Creates all data dictionary objects required for PL/SQL.
• SQL>@?/rdbms/admin/catclust.sql: Creates REAL Application Clusters data dictionary views.

The data dictionary base tables are the first objects created in any Oracle database. They are created in system tablespace.

Read More

Optimizer Statistics

To know about the database and the objects in the database we use optiimizer statistics.In simple optimzer statistics provides information about the database and the objects in the database. For every SQL statement there is execution plan, that execution plan is decided by query optimizer.Optimzer statistics include the Table statistics, Index statistics, Column statistics, System statistics:

- Table statistics

• Number of rows
• Number of blocks
• Average row length 

- Column statistics

• Number of distinct values(NDV) in column
• Number of nulls in column
• Data distribution

- Index statistics 

• Number of leaf blocks
• Levels
• Clustering factor 

- System statistics

• I/O performance and utilization 
• CPU performance and uitilization 

By using data dictionary you can view the optimizer statistics that are stored in the data dictionary and the views are:

• DBA_TABLES
• DBA_INDEXES
• DBA_CLUSTERS
• DBA_TAB_STATISTICS
• DBA_IND_STATISTICS
• DBA_TAB_PARTITIONS
• DBA_OBJECT_TABLES

Read More

Unregister Target Database and Reovery Catalog with RMAN

You can register multiple databases in a recovery catalog; that means you can keep multiple database repository information in a single recovery catalog. But one restriction is each database DBID must be different as RMAN distinguish one database from another by DBID.

So whenever you just copy one database with user managed copy or by RMAN restore and recover then both database DBID is same. So they can't be register in same recovery catalog.

Unregistering a Target Database from the Recovery Catalog
To unregister a target database from the recovery catalog, just follow the procedure.
1)Connect to Recovery Catalog database.
 

rman target / CATALOG cat_user/cat_passwd@catdb
Recovery Manager: Release 10.2.0.1.0
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb1 (DBID=1489461517)
connected to recovery catalog database

2)Issue UNREGISTER DATABASE command.

RMAN> UNREGISTER DATABASE;

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

But remember when a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the time of re-registration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost.

Resynchronizing the Recovery Catalog

 
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP. If you want to manually resynchronize issue RESYNC CATALOG command. In case of resynchronization RMAN compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed.

RMAN> RESYNC CATALOG;
starting full resync of recovery catalog
full resync complete

Related Post:-

• Create Recovery Catalog
• Register Target Database and Recovery Catalog

Read More

Register Target Database and Recovery Catalog with RMAN

After creating the recovery catalog the first step in using a recovery catalog with a target database is registering the database in the recovery catalog.To register database you have to follow fews steps and they are:-
1) The recovery catalog database must be open.
2) Connect RMAN to both the target database and recovery catalog database.

rman target/catalog cat_user/cat_passwd@catdb

Recovery Manager: Release 10.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation, All rights reserved.

connected to target database: catdb (DBID=1489461517)
connected to recovery catalog database

3) If this target database has not registered then register the target database, it should be registered in the connected recovery catalog

RMAN>REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete.

Related Post:- 

• Create Recovery Catalog
• Unregister Target Database and Recovery Catalog with RMAN

Read More

Create Recovery Catalog

A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations. Recovery catalog is optional. RMAN can be used either with or without a recovery catalog. An experienced DBA suggest that the Enterprise Manager instance schema and RMAN catalog schema  placed in the same utility database on a server separate from the main servers. There are few steps to create recovery catalog as follow:

•     Configure Recovery Catalog
•     Create Recovery Catalog owner
•     Create Recovery Catalog  

 1)Create a database called cat and connect sqlplus SYS/pass@cat as SYSDBA

 2)Create a tablespace where all information of receovery catalog will be collected.

SQL>create tablespace cat_tbs datafile '/opt/cat/cattbs.dbf' size 100m;  

Tablespace created.

 3)Create a user in the recovery catalog database

SQL>create user cat_user identified by cat_passwd

 temporary tablespace temp default tablespace cat_tbs 

 quota unlimited on cat_tbs;  

User Created.

 4)Grant the recovery catalog owner role to the user.This will provide all privileges to maintain and query recovery catalog.

SQL>grant recovery_catalog_owner to cat_user;

Grant succeeded.

 5)Connect to the database that contains the catalog owner.

 rman catalog cat_user/cat_passwd@catdb

 6)Connect from the RMAN utility prompt

rman
RMAN>connect catalog cat_user/cat_passwd

7)Run the create catalog command to create the catalog.May it take several minutes.

 RMAN>create catalog;

recovery catalog created

Related Post:-

• Register Target Database and Recovery Catalog  with RMAN
• Unregister Target Database and Recover Catalog with RMAN  

Read More

Using tablespace with a temporary tablespace group

A tablespace group enables a user to consume temporary space from multiple tablespaces.It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.
The following statement adds a tablespace to an existing group. It creates and adds tablespace temp to group1, so that group1 contains tablespaces temp2 and temp3.

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE '/opt/oracle/data/temp301.dbf' SIZE 25M
     TABLESPACE GROUP group1;
The following statement also adds a tablespace to an existing group, but in this case because tablespace temp2 already belongs to group1, it is in effect moved from group1 to group2:

ALTER TABLESPACE temp2 TABLESPACE GROUP group2;
Now group2 contains both temp and temp2, while group1 consists of only temp3.

You can remove a tablespace from a group as shown in the following statement:

ALTER TABLESPACE temp3 TABLESPACE GROUP '';

Below syntax assigns a user to a default temporary tablespace group.

ALTER USER USER TEMPORARY TABLESPACE group1; 

Read More

ORA-12906 cannot drop default temporary tablespace

If you try to drop default temporary tablespace of database as it will result in error as:-

SQL>SELECT TABLESPACE_NAME FROM DBA_TEMP_FILES;
TABLESPACE_NAME
---------------
TEMP

SQL>alter tablespace temp offline;
alter tablespace temp offline
*
Error at line 1;
ORA-12905:default temporary tablepspace cannot be offline

SQL>drop tablespace temp including contents and datafiles;
drop tablespace TEMP including contents and datafiles
*
Error at line2:
ORA-12906:cannot drop default Temporary tablespace

In order to solve this problem you must assign database default tenporary tablespace to a new one. To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.

To create a new one,

SQL>create temporary tablespace temp1 tempfile'/opt/nit/temp01/dbf' size 10M;
 Tablespace created.

Make this tablespace to database default temporary tablespace,
SQL>alter database default temporary tablespace temp1;
Database altered.

SQL>drop tablespace temp including contents and datafiles;
Tablespace dropped. 

Read More

Recover or Recreate TEMPORARY Tablespace in Oracle

In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode.

Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users.

In order to do that follow the steps here:

1)Find out the temporary datafiles.
 

SQL> select file_name,file_id, tablespace_name from dba_temp_files;

 
FILE_NAME                              FILE_ID     TABLESPACE_NAME
--------------------------------------------- ------------ ------------------------------
/oradata2/temp2.dbf                          1            TEMP01
/oradata2/temp.dbf                            2            TEMP02
/oradata2/temp3.dbf                          4            TEMP03

2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.

3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp tempfile '/opt/nit/temp.dbf' size 10M;
Tablespace created.

SQL> alter database default temporary tablespace temp;
Database altered.

4)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also. 

SQL>select temporary_tablespace,username from dba_users
          where temporary_tablespace<>'TEMP';

TEMPORARY_TABLESPACE           USERNAME
---------------------------------------------  -------------------------
TEMP                                                     US1
TEMP2                                                   US2

5)Explicitly assign temporary tablespace for users US1 and US2.
SQL> alter user us1 temporary tablespace temp;
User altered.

SQL> alter user us2 temporary tablespace temp;
User altered.
6)Drop the old temporary tablespace.

SQL> drop tablespace temp01;
Tablespace dropped.

SQL> drop tablespace temp02;
Tablespace dropped.

SQL> drop tablespace temp03;
Tablespace dropped.

Read More

Space Allocation in a TEMPORARY Tablespace

Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:

 SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users.To see the default temporary tablespace for a database:

SQL>SELECT * FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 

Space Allocation in a TEMPORARY Tablespace

You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$SORT_USAGE view identifies the current sort users in those segments.

When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace

The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.

SELECT * from DBA_TEMP_FREE_SPACE;
 TABLESPACE_NAME    TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
----------------------------------- ------------------------------ ------------------------------- ------------------------
TEMP                                250609664               250609664             249561088

Benefits Of TEMPORARY Tablespace:

Temporary tablespace group has the following benefits:

• A temporary tablespace allows multiple default temporary tablespaces to be specified at the database level.
• A temporary tablespace allows the user to use multiple temporary tablespaces in different sessions at the same time.
• A temporary tablespace allows a single SQL operation to use multiple temporary tablespaces for sorting.

Read More

TEMPORARY Tablespace And TEMPFILE

TEMPORARY TABLESPACE

 

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.If you join two large tables and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT etc.
Temporary tablespaces are used to store the following:

• Intermediate sort results
• Temporary tables and temporary indexes
• Temporary LOBs
• Temporary B-trees

By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement. You can assign a temporary tablespace to each database user with the CREATE USER or ALTER USER statement.

SQL>CREATE USER NITIN DEFAULT TABLESPACE tbs TEMPORARY TABLESPACE temp;
SQL>ALTER USER NITIN TEMPORARY TABLESPACE TEMP;

Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up:

TEMPFILE

Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:

SQL>ALTER TABLESPACE temp ADD TEMPFILE '/opt/nitin/temp.dbf' SIZE 100M;

Read More

Refresh Materialized View

Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log or direct loader log keeps a record of changes to the master tables.

Materialized views can be refreshed either on demand or at regular time intervals. Alternatively, materialized views in the same database as their master tables can be refreshed whenever a transaction commits its changes to the master tables.
Materialized View Logs

A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.

Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.

CREATE MATERIALIZED VIEW V1 REFRESH FAST FOR UPDATES AS
SELECT * FROM TAB; 

Read More

Materialized Views

Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing:
 

In data warehouses, materialized views are used to compute and store aggregated data such as sums and averages. Materialized views in these environments are typically referred to as summaries because they store summarized data. They can also be used to compute joins with or without aggregations. If compatibility is set to Oracle9i or higher, then materialized views can be used for queries that include filter selections.
 

Cost-based optimization can use materialized views to improve query performance by automatically recognizing when a materialized view can and should be used to satisfy a request. The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views.
 

In distributed environments, materialized views are used to replicate data at distributed sites and synchronize updates done at several sites with conflict resolution methods. The materialized views as replicas provide local access to data that otherwise has to be accessed from remote sites.
    In mobile computing environments, materialized views are used to download a subset of data from central servers to mobile clients, with periodic refreshes from the central servers and propagation of updates by clients back to the central servers.

Materialized views are similar to indexes in several ways:

• They consume storage space.
• They must be refreshed when the data in their master tables changes.
• They improve the performance of SQL execution when they are used for query rewrites.
• Their existence is transparent to SQL applications and users.

Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement.

A materialized view can be partitioned. You can define a materialized view on a partitioned table and one or more indexes on the materialized view.

Read More

Extents Overview

An extent is a contiguous set (side-by-side) of Oracle data blocks allocated to a segment within a tablespace. The size of an extent is controlled by storage parameters used when you CREATE or ALTER the segment (INITIAL, NEXT and PCT_INCREASE) and tablespace parameters. 

When Extents Are Allocated

 

When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks under the table's segment.

If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.

 Extent Allocation:-

SQL> SELECT tablespace_name, extent_id, blocks, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'EMP';

TABLESPACE_NAME                 EXTENT_ID     BLOCKS      BYTES
------------------------------ ---------- ---------- -------------- ----------
USERS                                                  0                     8               65536
USERS                                                  1                     8               65536


When Extents Are Deallocated

In general, the extents of a segment do not return to the tablespace until you drop the schema object whose data is stored in the segment. Exceptions to this include the following:
 

• The owner of a table or cluster, or a user with the DELETE ANY privilege, can truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.
• A database administrator (DBA) can deallocate unused extents using the following SQL syntax:  ALTER TABLE table_name DEALLOCATE UNUSED; 
• Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.

When extents are freed, Oracle modifies the bitmap in the datafile (for locally managed tablespaces) or updates the data dictionary (for dictionary managed tablespaces) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.

Read More

Row Chaining, Row Migrating, PCTFREE, PCTUSED

Row Chaining  

The row is too large to fit into an EMPTY data block that is known as chained row. Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. If  you have a 2k blocksize but your row size is 4k, we will use 3 blocks to store that row in pieces.  Any table with a long/long raw will have chained rows.Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases isnot avoided. Any table whose rowsize exceeds the  blocksize will have chained rows.  Any table with more then 255 columns will have chained rows.You can see if you "have it" by looking at the system wide statistic:

select * from v$sysstat where name like 'table pop cont%';

Row Migrating

A row that was moved to another block due to an update making it too large to fit on its original block with the other rows there is known as migrated row.Oracle tries to find another Blockwith enough free space to hold the entire row.If such block is available Oracle moves the entire ROW to the NEW BLOCK.Oracle keeps the original Row piece of a Migrated row row to POINT to the NEW BLOCK containing the actual row.The ROWID of the MIGRATED rows does not change. INDEXES are not updated and they point to the ORIGINAL row LOCATION.

Migration and Chaining have negative effects on performance. INSERT and UPDATE statements that cause migration and chaining perform very poorly since due to additional PROCESSING. Queries that use an Index to select migrated or chained rows must perform additional I/O's.

 

PCTFREE Parameter

The PCTFREE parameter specifies the minimum percentage of data block to be reserved as free space. If you used PCTFREE 20 then at least 20% size of data block will be reserved as free space. For example if you use data block size 8K (DB_BLOCK_SIZE=8192) then PCTFREE 20 will reserved 1638 bytes as free space in a data block. This parameter is used to update to the existing rows already within each block.You can specify this parameter which issuing CREATE TABLE statement.

   

 

PCTUSED Parameter

This parameter specifies the minimum percentage of a block that can be used for row data plus overhead(data block header, table directory, and row directory) before new rows are added to the block.

After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls below the parameter PCTUSED.

If we set PCTUSED 40 in the CREATE TABLE statement then data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (Here the block's used space has previously reached PCTFREE).

Related Post:- Row Format

Read More

Row Format In DB

Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece.If all of a row's data cannot be inserted into a single data block or an update to an existing row causes the row to outgrow its data block, Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

 

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row's pieces are chained together using the rowids of the pieces.Each row piece, chained or unchained, contains a row header and data for all or some of the row's columns.
The row header contains information about:

•     Row pieces
•     Chained row pieces only)
•     Columns in the row piece
•     Cluster keys

A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.

Related Post:- Oracle Data Block Format

Read More

Oracle Data Block Format

The Format of Oracle Data Block is as follows:

1. Header 
2. Table Directory 
3. Row Directory 
4. Row Data
5.  Free Space 

 

Header  

The header contains general block information, such as the block address and the type of segment (for example, data or index).
 

Table Directory

This portion of the data block contains information about the table having rows in this block.
 

Row Directory

This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to' have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block. 

Row Data

This portion of the data block contains table or index data. Rows can span blocks.

Related Post:- Row Format
 

Free Space

- Free space is allocated for insertion of new rows and for updates to rows that require additional space.
- In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries.

- A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. 

- The space required for transaction entries in most operating systems require approximately 23 bytes.

Read More

Oracle Data Block Overview

• Oracle manages the storage space in the datafiles of a database in units called data blocks.  
• Data block is the smallest unit of data used by a database.  
• At the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.  
• The standard block size is specified by the initialization parameter DB_BLOCK_SIZE. 
• You can specify of up to five nonstandard block sizes. 
• The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. 
• Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

Related Topic: Data Block Format

Read More

Rules To Specify Values In An Initialization Parameter

•Parameter within initialization file can be specified in any order.

•If a line starts with pound sign(#) the rest words of the line is ignored. # indicates it is comment.

•If OS is case sensitive then value of filename within initialization parameter is case sensitive.

•To enter several parameters in a line use spaces between the parameters. Like,
db_name=dbase sga_target=300M sga_max_size=300M

•The parameter that take multiple values enter multiple values enclosed in parentheses and separated by commas. Like,
control_files=('/oradata1/arju/control01.ctl','/oradata1/arju/control02.ctl')

Alternatively you can enter multiple values without parentheses and commas. Like,
control_files='/oradata1/arju/control01.ctl' '/oradata1/arju/control02.ctl'

•You can use the IFILE initialization parameter to embed the contents of another initialization parameter file into the current initialization parameter file.

•If you specify a parameter value in multiple location then the last executed value override the prior one since the parameter is executed in sequential order. For example, if my pfile contains value in the following order
sga_target=300M
open_cursor=100
sga_target=400M
then sga_target=400M is in effect.

•Enclose in quotation marks any parameter value that contains a special character.
For example you can specify DB_DOMAIN in any of following ways,

DB_DOMAIN = 'ARJU.ORACLE#.COM'
DB_DOMAIN = ARJU.ORACLE\#.COM

Related Topic:-

• Initialization Parameter File (PFILE)

Read More

Solution Of Startup Files With An Error ORA-00119 and ORA-00132

Error Description: Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'


Cause of The problem: If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.

PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.

Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.

Solution of The problem 

A)Correct the tnsnames.ora

i)Determine if the tns alias is good by using tnsping.

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name

So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')

and run tnsping utility,

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters

So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.

SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))

iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.


B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;

2)Open the pfile and remove the LOCAL_LISTENER entry.

$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora

3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;

4)Start the database.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.

In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';

In my system,
SQL>ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';

SQL> show parameter local

NAME TYPE VALUE 

local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))

Read More

Solution OF An Error ORA-27102

Scenario of The problem: 

In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,

SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument

Reason of The problem:

The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.

Solution of The problem:

1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,

SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.

SQL> alter system set sga_target=1600M;
System altered.

SQL>shutdown

Now set ORACLE_SID and start the instance.

bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;

And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.

And start the database with the pfile.

SQL>STARTUP PFILE='pfile_name';

Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;

Read More

Oracle Initialization Parameters File Types

There are different types of initialization parameters in oracle, such as,

1)Derived Parameters
2)Operating System-Dependent Parameters
3)Variable Parameters

1)Derived Parameters:

As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.

For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.

2)Operating System-Dependent Parameters 

The valid value of value ranges of these parameters are host Operating System dependent. For example, the value of the parameter DB_BLOCK_SIZE, has an operating system-dependent default value.

3)Variable Parameters 

The variable initialization parameters offer the most potential for improving system performance.
Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits.

Read More

Oracle Database Configuration Assistant

The Database Configuration Assistant (DBCA) an Oracle supplied tool that enables you to create an Oracle database, configure database options for an existing Oracle database, delete an Oracle database, or manage database templates. DBCA is launched automatically by the Oracle Universal Installer, but it can be invoked standalone from the Windows operating system start menu (under Configuration Assistants) or by entering the following on the UNIX command line:

dbca

The Database Configuration Assistant (DBCA) is a graphical user interface that interacts with the Oracle Universal Installer when you choose to create a database automatically when you install the Oracle 9i Server. The DBCA is a Java-based interface that can be launched from any platform that has access to a Java engine.

During installation of the server software, DBCA is launched by the Universal Installer and can automatically create a starter database if you want it to. You can choose at this point whether to use the DBCA at this time, use it later to assist you with database creation, or create your database manually.

With the DBCA, you can not only create a database, you can also configure database options, delete existing databases, and manage templates.

To launch the DBCA on Unix, type dbca at the Unix prompt. In Windows, you can launch the assistant from the Windows operating system Start menu where it is found under Configuration Assistants.

On a Unix platform, you need to be running an XWindows session either on the terminal or on your PC to run the DBCA product.

When you start the DBCA, you are presented with a screen with four options:

• Create a Database 
• Configure Database Options in a Database 
• Delete a Database 
• Manage Templates    

Create a Database

This option allows you to create a new database or a database template. The default template allows you to choose to create a database either with or without data files.

Without datafiles contains only the structure of the database, but allows you to specify or change all the database parameters.

With datafiles contains both the structure and the physical datafiles of the database. All the log files and control files are automatically created for the database, but you can add or remove control files and/or log groups. You can also change the destination and name of any or all of the data files. You cannot add or remove datafiles, tablespaces, or rollback segments. You cannot change initialization parameters.

Whenever you create a database with the DBCA, you need to specify the global database name as well as the unique SID. What's more, you need to decide what added features you want in your resulting database. These added features that you can choose to add to your installation include the following:

• Oracle Spatial 
• Oracle Intermedia (or Oracle Text) 
• Oracle OLAP Services 
• Example Schemas 
• Human Resources 
• Order Entry 
• Product Media 
• Sales History 
• Shipping 
• When you are creating a database with the DBCA, you have the option of creating either a typical or a custom database.

A custom database allows you to customize the creation of your database. This option is only for experienced database administrators with advanced knowledge of the database creation process. This advanced knowledge includes experience with the data, control, and redo settings; tablespace and extent sizing; database memory parameters and other initialization parameters; archive log formats and destinations; trace file destinations; and character set values.

A typical database creation with minimal user input allows you to choose the following different types of databases to mimic.

The steps involved in creating a database with DBCA include the following:

• Create Database Create the database immediately. 
• Save as a Database Template Save creation parameters as a template and that template is added to the list of available templates. 
• Generate Database Creation Scripts Save the database creation parameters as a script file for later use. 
• Determine the type of database that you want to create: 

1. Online Transaction Processing (OLTP) An OLTP database typically must be capable of processing many thousands of transactions (read, write, update, delete) from many concurrent users every day. Performance, in an OLTP, is in terms of throughput of transactions and availability of data. 
2. Data Warehouse These databases must be capable of processing a wide variety of queries; however, they are typically read-only queries. These processes range from simple queries that fetch a few records, to queries that many long-running complex queries take hours to process. Data Warehouse database performance is measured in terms of response time. 
3. Multipurpose Many environments require a combination of the two different kinds of processing. A mixed database supports both OLTP and Data Warehousing environments. This is the default database that will be installed using DBCA.

 

Database Configuration Options

The Configure Database option lets you add options to your database that had not previously been configured. These include (if they are available for your installation) JVM, InterMedia, Visual Information Retrieval, Spatial, Oracle OLAP services, advanced replication, and SQL*Plus help.

The Configure Database option is not available in the 9i Real Application Clusters installation.

Additional database configuration options, some shown earlier in Figure 3.2, include the following:

• Delete a Database Allows you to delete a database. 
• Manage Templates Allows you to create a template though one of three methods:

1.  From predefined template settings Create a new template from the predefined template settings; add or change any settings, parameters, storage characteristics; or use custom scripts.

2.  From an existing database, structure only Contains the structural information similar to an existing database. The structure, in this case, means all the database options as well (the tablespaces, datafiles, and initialization parameters) specified in the source database. No schemas or data will be a part of the created database.

3.  From an existing database, structure and data Create a database that copies the structure as well as the data of the existing database. User-defined schemas and their data will be a part of the template. You are not allowed to add or remove any data files, tablespaces, or rollback segments.

• Required Parameters Besides the information already covered, the creation of an Oracle Database and Oracle instance requires some further decisions to be made, particularly if you are choosing the custom database creation option. Figure 3.4 shows you a tabbed page on which you can choose various SGA settings, character sets to define for your database, data block sizing decisions, file locations, and archive log locations for the database.

Choosing database parameters.

• Archive Parameters Archive redo logs are used for database recovery and often for implementation of a standby database. Running your database in archive log mode enables the archiving of redo log files before those redo files are reused. This is done for the purpose of recovery facilitation.

 It is often not necessary, or wise, to put a Data Warehouse database into archive log mode. Because Data Warehouses are updated infrequently, and typically by means of a bulk SQL*Ldr job, creating logs for restoration purposes in this case is often simply a waste of resources.

• Data Block Sizing DB_BLOCK_SIZE helps to define the default database block size and determine the SORT_AREA_SIZE. DB_BLOCK_SIZE, the parameter that gets set during the use of this parameter, can be specified only at database creation time. SORT_AREA_SIZE is the amount of memory used for sorting operations. 

Remember that the only time you can set the DB_BLOCK_SIZE of the database, regardless of the method used to create the database, is at database creation time. If for any reason you determine later that you have chosen an incorrect value for this parameter, the database has to be rebuilt.

•  File Locations By using the UDUMP, BDUMP, and CDUMP parameters, you can specify the particular locations for trace files.

 More information about the Initialization Parameter file can be found in Chapter 4, "Managing an Instance."

• Database Storage This helps you to specify the storage parameters for the various files in your database creation. You can make custom alterations to the control files (do you want to multiplex?), tablespaces (do you want to have dictionary managed or locally managed?), datafiles, undo segments (do you want the new, and Oracle suggested, Automatic Undo Management, or do you want to manage rollback segments manually?), and redo log groups (how many log groups do you want, where do you want them, and do you want to multiplex them?).

Related Post:-  

• Steps to create Database using DBCA
• Advantage of using DBCA

Read More