How to Find Out the size of your Oracle Database

As an Oracle DBA this is the most important interview question and at any time you may face an issue where you need to find out the size of the Oracle Database. In detail, here you will get to know the actual size of the database that comprises of only data files when no redo and temp are generated. In second case, you may asked to find out the overall database size that contains all the data files, temp files and the redo logs (free+used space). Third, is the size occupied by data in this database or you can say  Database usage details which you can get using dba_segments. 

To Find the Actual size of the Database in GB

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

To Find the Size Occupied by Data in the Database 

Gives the size occupied by data in this database or Database usage details.

SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

To Find the Overall/Total Database Size

Overall database size is the sum of used space plus free space i.e. the size of the data files, temp files, log files and the control files. You can find out the total database size using simple query. This sql gives the total size in GB.  

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Read more ...

Schema and Non-Schema Objects in Oracle Database

Oracle database contains schema objects like views, tables, triggers etc., and several other types of objects which are also stored in the database but are not contained in a schema.

A schema is a collection of logical structures of data, or schema objects which is owned by a database user and has the same name as that of the user. Schema objects can be created and manipulated with SQL and include the following types of objects:

Types of Schema Objects

Schema objects are logical data storage structures which do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle Database stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles.

• Tables and index-organized tables
• Constraints
• Views
• Database links
• Database triggers
• Dimensions
• External procedure librarie
• Indexes and indextypes
• Java classes, Java resources, and Java sources
• Materialized views and materialized view logs
• Object tables, object types, and object views
• Operators
• Sequences
• Stored functions, procedures, and packages
• Synonym
• Tables and index-organized tables
• Clusters 

Types of NON-SCHEMA Objects

There are several other types of objects which are also stored in the database but are not contained in a schema are:

• Contexts
• Directories
• Parameter files (PFILEs) and server parameter files (SPFILEs)
• Profile
• Roles
• Rollback segments
• Tablespaces
• User

 For some objects, such as tables, indexes, and clusters, you can specify how much disk space Oracle Database allocates for the object within the tablespace's datafiles.

Read more ...

How To Find DBID in NOMOUNT State

Oracle Database identifier in short DBID is an internal, unique identifier for an Oracle database. Database administrator must note down the DBID in safe place, so that any miss-happening to the database could be easily identified and recovered. In case it is required to recover SPFILE or control file from autobackup, such as disaster recovery, you will need to set DBID. So lets see how to get DBID in NOMOUNT State.

Why DBID is important? 

• It is an unique identifier for a database.
• In case of backup and recovery RMAN distinguishes databases by DBID.
• When DBID of a database is changed, all previous backups and archived logs of the database become unusable.
• After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their log sequence to 1 
• You should make a backup of the whole database immediately after changing the DBID.

Let's take an example of getting it in nomount state:

First shut down the database using shut immediate command

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now startup database in nomount state

SQL> startup nomount
ORACLE instance started.
Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             402657268 bytes
Database Buffers          197132288 bytes
Redo Buffers                5640192 bytes

You can also set tracefile identifier for easily identification of tracefile.

SQL> alter session set tracefile_identifier=orahow;
Session altered.

Now, dump first ten block of datafile, because each block header contains dbid information.

SQL> alter system dump datafile 'D:\app\SantoshTiwari\oradata\TEST11\USERS01.DBF'
  2  block min 1 block max 10;
System altered.

Now find the location of Trace file.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      d:\app\santoshtiwari\diag\rdbm
                                                 s\test11\test11\trace

Now search for Db ID inside the trace file. In Linux you can use cat command with grep to find it:

cat filename | grep Db id

Here you can see the dump here:

Start dump data block from file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\USERS01.DBF minblk 1 maxblk 10
 V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3561501508=0xd4483344, Db Name='TEST11'
Activation ID=0=0x0
Control Seq=3522=0xdc2, File size=640=0x280
File Number=4, Blksiz=8192, File Type=3 DATA


In simple you can also get it using v$database:

SQL> select name, dbid from v$database;

NAME            DBID

--------- ----------

TEST11    3561501508

 DBID is also displayed by the RMAN client when it starts up and connects to your database. Typical output follows:

SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 6 19:59:06 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)

Read more ...