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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Oracle Data Block Format

The Format of Oracle Data Block is as follows: Header  Table Directory  Row Directory  Row Data  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...

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

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

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 mountORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-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...

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> startupORA-27102: out of memorySolaris-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...

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

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

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More