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)...

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   ...

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...

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...

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 statisticsNumber of rows Number of blocks Average row length  - Column statistics Number of distinct values(NDV) in column Number of nulls in...

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...

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...

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...

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...

Read More