Oracle session level, Managing tablespace, Managing

Oracle session level, Managing tablespace, Managing

Datafiles, Logical and physical structure


Session Level

Long Operations:

select sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,time_remaining remaning,elapsed_seconds lapsed,last_update_time last_time from v&session_longops where sid=73 order by last_update_time desc;

All active sessions
Select * from v&session where status='ACTIVE'and sid in (Sid, Serial #) order by sid;

Find session's sid or process id by it's sid or process id

Select sid, a.serial#, spid, a.username, status, taddr, a.programfrom v&session a, v&process b where a.paddr=b.addr and a.username is not nulland (sid=163 or spid=28179) order by status, sid;

Kill Session

Alter system kill session '&sid,&serial';

Log Management

Status of Logfile Groups

Select * from v&log;

Status of Logfiles

Select * from v&logfile order by group#;

status of the archiver

Select * from v&instance;

Restart the archiver

Alter system archive log start;



Switch online log

Alter system switch logfile;

Alter system set log_archive_max_processes=4;


Add logfile group

Alter database add logfile group 4 ('&logfilename1','&logfilename2') size 64M;

Drop logfile group and all members in it

Alter database drop logfile group &N;

Add logfile member

Alter database add logfile member '&logfilename' reuse to group 4;

Drop logfile member

Alter database drop logfile member '&logfilename';

Checking archivelog mode
Select dbid, name, resetlogs_time, log_mode from v&database;

Archiver destinations

Select * from v&archive_dest;

Altering Archive destination

Alter system set log_archive_dest_1='location=&path';
Alter system set log_archive_dest_state_1='enable';

Archived log info from the control file

Select * from v&archived_log;

The sequence# of last backed up log

Select thread#, max (sequence#) from v&archived_log where BACKUP_COUNT>0 group by thread#;

Redo size (MB) per day, last 30 days
Select trunc (first_time) arc_date, sum (blocks * block_size)/1048576 arc_sizefrom v&archived_log where first_time >= (trunc (sysdate)-30)group by trunc(first_time);

MANAGING TABLESPACES

A tablespace is a logical storage unit. Why we are say logical because a tablespace is not visible in the file system. Oracle store data physically is datafiles. A tablespace consist of one or more datafile.
Types of tablespace?
System Tablespace:• Created with the database • Required in all database • Contain the data dictionary
Non System Tablespace: • Separate undo, temporary, application data and application index segments Control the amount of space allocation to the user’s objects • Enable more flexibility in database administration
How to Create Tablespace?
CREATE TABLESPACE "tablespace name"DATAFILE clause SIZE ……. REUSEMENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)BLOCKSIZELOGGING NOLOGGING (Logging: By default tablespace have all changes written to redo, Nologging : tablespace do not have all changes written to redo)ONLINE OFFLINE (OFFLINE: tablespace unavailable immediately after creation)PERMANENT TEMPORARY (Permanent: tablespace can used to hold permanent object, temporary: tablespace can used to hold temp object)EXTENT MANAGEMENT clauseExample: CREATE TABLESPACE "USER1"DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSEBLOCKSIZE 8192
LOGGING
ONLINEPERMANENTEXTENT MANAGEMENT LOCAL

How to manage space in Tablespace?

Tablespace allocate space in extent.






Locally managed tablespace:

The extents are managed with in the tablespace via bitmaps. In locally managed tablespace, all tablespace information store in datafile header and don’t use data dictionary table for store information. Advantage of locally managed tablespace is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.

Extent Management [Local Dictionary]
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.
To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).
If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice.If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.

Dictionary Managed tablespace
When we declaring a tablespace as a Dictionary Managed, the data dictionary manages the extents. The Oracle server updates the appropriate tables (sys.fet$ and sys.uet$) in the data dictionary whenever an extent is allocated or deallocated.
How to Create a Locally Managed Tablespace?
The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.
CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;How to Create a Dictionary Managed Tablespace?The following is an example of creating a DICTIONARY managed tablespace in Oracle9i:CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT DICTIONARYDEFAULT STORAGE (INITIAL 64KNEXT 64KMINEXTENTS 2MAXEXTENTS 121PCTINCREASE 0);
What is Segment Space Management Options?
Two choices for segment-space management, one is manual (the default) and another auto.
Manual: This is default option. This option use free lists for managing free space within segments. What are free lists: Free lists are lists of data blocks that have space available for inserting new rows.
Auto: This option use bitmaps for managing free space within segments. This is typically called automatic segment-space management
Example:CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSEEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512KSEGMENT SPACE MANAGEMENT AUTOPERMANENTONLINE;
How to Convert between LMT and DMT Tablespace?
The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert
between LMT and DMT mode. Look at these examples:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');PL/SQL procedure successfully completed.
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');PL/SQL procedure successfully completed

Important Queries Related to Tablespace

How to retrieve tablespace default storage Parameters?
SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT "INITIAL_EXT",NEXT_EXTENT "NEXT_EXT",MIN_EXTENTS "MIN_EXT",MAX_EXTENTS "MAX_EXT",PCT_INCREASE FROM DBA_TABLESPACES;

How to retrieve information tablespace and associated datafile?
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES;
How to retrieve Statistics for Free Space (Extents) of Each Tablespace?

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,COUNT(*) "PIECES",MAX(blocks) "MAXIMUM",MIN(blocks) "MINIMUM",AVG(blocks) "AVERAGE",SUM(blocks) "TOTAL" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID;

How to drop a datafile from a Tablespace?Important:
Oracle does not provide an interface for dropping datafiles in the same way you would drop a schema object such as a table or a user.Reasons why you want to remove a datafile from a tablespace:• You may have mistakenly added a file to a tablespace. • You may have made the file much larger than intended and now want to remove it. • You may be involved in a recovery scenario and the database won't start because a datafile is missing.

Once the DBA creates a datafile for a tablespace, the datafile cannot be removed. If you want to do any critical operation like dropping datafiles, ensure you have a full backup of the database.
Step: 1

Determining how many datafiles make up a tablespace to determine how many and which datafiles make up a tablespace, you can use the following query:
SELECT file_name, tablespace_name FROM dba_data_files WHEREtablespace_name ='';

Case 1
If you have only one datafile in the tablespace and you want to remove it. You can simply drop the entire tablespace using the following:
DROP TABLESPACE INCLUDING CONTENTS;
The above command will remove the tablespace, the datafile, and the tablespace's contents from the data dictionary.Important: Oracle will not drop the physical datafile after the DROP TABLESPACE command. This action needs to be performed at the operating system.
Case 2
If you have more than one datafile in the tablespace, and you want to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above:
DROP TABLESPACE INCLUDING CONTENTS;
Case 3
If you have more than one datafile in the tablespace and you want to remove only one or two (not all) datafile in the tablespace or you want to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace.
Step: 1 Gather information on the current datafiles within the tablespace by running the following query in SQL*Plus:SELECT file_name, tablespace_name FROM dba_data_files WHEREtablespace_name ='';
Step: 2 you now need to identify which objects are inside the tablespace for the purpose of running an export. To do this, run the following query:SELECT owner, segment_name, segment_type FROM dba_segments WHERE tablespace_name=''
Step: 3 now, export all the objects that you wish to keep.
Step: 4 Once the export is done, issue the

DROP TABLESPACE INCLUDING CONTENTS.
Step: 5 delete the datafiles belonging to this tablespace using the operating system.

Step: 6 Recreate the tablespace with the datafile(s) desired, and then import the objects into that tablespace.
Case: 4
If you do not want to follow any of these procedures, there are other things that can be done besides dropping the tablespace.
• If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.
• If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE RESIZE;
Command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.
Important: The ALTER DATABASE DATAFILE OFFLINE DROP command is not meant to allow you to remove a datafile. What the command really means is that you are off lining the datafile with the intention of dropping the tablespace.

If you are running in archivelog mode, you can also use:

ALTER DATABASE DATAFILE OFFLINE;

Instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup.

(This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

MANAGING DATAFILES

What is datafile?

Datafiles are physical files of the OS that store the data of all logical structures in the database. Datafile must be created for each tablespace.
How to determine the number of datafiles?
At least one datafile is required for the SYSTEM tablespace. We can create separate datafile for other tablespace. When we create DATABASE, MAXDATAFILES may be or not specify in create database statement clause. Oracle assassin db_files default value to 200. We can also specify the number of datafiles in init file.
When we start the oracle instance, the DB_FILES initialization parameter reserve for datafile information and the maximum number of datafile in SGA. We can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance. Important: If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit. Example: if init parameter db_files set to 2 then you can not add more then 2 in your database.
If the value of DB_FILES is too high, memory is unnecessarily consumed. When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

Note:
If you add new datafiles to a tablespace and do not fully specify the filenames, the database creates the datafiles in the default database directory. Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.
How to add datafile in existing tablespace?
Alter tablespace add datafile ‘/............../......./file01.dbf’ size 10m autoextend on; How to resize the datafile? Alter database datafile '/............../......./file01.dbf' resize 100M; How to bring datafile online and offline?Alter database datafile '/............../......./file01.dbf' online;alter database datafile '/............../......./file01.dbf' offline; How to renaming the datafile in a single tablesapce?Step: 1 Take the tablespace that contains the datafiles offline. The database must be open. Alter tablespace offline normal; Step: 2 Rename the datafiles using the operating system. Step: 3 Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

Alter tablespace rename datafile '/...../..../..../user.dbf' to '/..../..../.../users1.dbf';
Step 4: Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. How to relocate datafile in a single tablesapce?Step: 1 Use following query to know the specific file name or size.
Select file_name, bytes from dba_data_files where tablespace_name=''; Step: 2 Take the tablespace containing the datafiles offline: alter tablespace offline normal;
Step: 3 Copy the datafiles to their new locations and rename them using the operating system. Step: 4 Rename the datafiles within the database.
ALTER TABLESPACE RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf' TO '/u03/oracle/rbdb1/users01.dbf','/u04/oracle/rbdb1/users02.dbf';
Step:5 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. How to Renaming and Relocating Datafiles in Multiple Tablespaces?

Step: 1 Ensure that the database is mounted but closed. Step: 2 Copy the datafiles to be renamed to their new locations and new names, using the operating system. Step: 3 Use ALTER DATABASE to rename the file pointers in the database control file. ALTER DATABASE RENAME FILE
'/u02/oracle/rbdb1/sort01.dbf','/u02/oracle/rbdb1/user3.dbf'TO '/u02/oracle/rbdb1/temp01.dbf',
'/u02/oracle/rbdb1/users03.dbf;

Step: 4 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.


Overview of Logical Structure of a Database

This section discusses logical storage structures: data blocks, extents, segments, and tablespaces. These logical storage structures enable Oracle Database to have fine-grained control of disk space use.This section includes the following topic

Oracle Database Data Blocks Extents Segments Tablespaces

Oracle Database Data BlocksAt the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to four other block sizes. A database uses and allocates free database space in Oracle Database data blocks.
ExtentsThe next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
SegmentsAbove extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a table, index, rollback segment, or for temporary use by a session, transaction, or SQL parser. In relation to physical database structures, all extents belonging to a segment exist in the same tablespace, but they may be in different data files.When the extents of a segment are full, Oracle Database dynamically allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

TablespacesA database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.
Each database is logically divided into two or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Every Oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. Oracle Database creates them automatically when the database is created. The system default is to create a small file tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespaces are created as small file tablespaces.
Oracle Database also lets you create bigfile tablespaces, which are made up of single large file rather than numerous smaller ones. Bigfile tablespaces let Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. As a result, Oracle Database can scale up to 8 exabytes in size. With Oracle-Managed Files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafile.

Overview of Physical Structure of a Database

The following sections explain the physical database structures of an Oracle database, including datafiles, control files, redo log files, archive log files, parameter files, alert and trace log files, and backup files.This section includes the following topics

Datafiles
Control Files Online Redo Log Files Archived Redo Log Files Parameter Files Alert and Trace Log Files Backup Files
Datafiles
Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

Datafiles have the characteristics:A datafile can be associated with only one database. Datafiles can be defined to extend automatically when they are full. One or more datafiles form a logical unit of database storage called a tablespace.
Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access
some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.
Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the background process database writer process (DBWn).
Datafiles that are stored in temporary tablespaces are called tempfiles. Tempfiles are subject to some restrictions.Control FilesEvery Oracle database has a control file. A control file contains entries that specify the physical structure of the database, including the following information:Database name

Names and locations of datafiles and redo log files Timestamp of database creation Oracle Database can multiplex the control file, that is, simultaneously maintain a number of identical control file copies, to protect against a failure involving the control file.
Every time an instance of an Oracle database is started, its control file identifies the datafiles, tempfiles, and redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle Database to reflect the change. A control file is also used in database recovery.
Online Redo Log File

Every Oracle Database has a set of two or more online redo log files. These online redo log files, together with archived copies of redo log files, are collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records), which record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

To protect against a failure involving the redo log itself, Oracle Database lets you create a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.

Archived Redo Log Fileswhen online redo log files are written to disk, they become archived redo log files. Oracle recommends that you enable automatic archiving of the redo log. Oracle Database automatically archives redo log files when the database is in ARCHIVELOG mode.

Parameter FilesParameter files contains a list of configuration parameters for that instance and database. Both parameter files (pfiles) and server parameter files (spfiles) let you store and manage your initialization parameters persistently in a server-side disk file. A server parameter file has these additional advantages:The file is concurrently updated when some parameter values are changed in the active instance. The file is centrally located for access by all instance in a Real Application Services database. Oracle recommends that you create a server parameter file as a dynamic means
maintaining initialization parameters.
Alert and Trace Log FilesEach server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Support Services. Trace file information is also used to tune applications and instances. The alert file, or alert log, is a special trace file. The alert log of a database is a chronological log of messages and errors.The following features provide automation and assistance in the collection and interpretation of trace and alert file information:

The Automatic Diagnostic Repository (ADR) is a system-managed repository for storing and organizing trace files and other error diagnostic data. ADR provides a comprehensive view of all the critical errors encountered by the database and maintains all relevant data needed for problem diagnosis and eventual resolution. When the same type of incident occurs too frequently, ADR performs flood control to avoid excessive dumping of diagnostic information.
The Incident Packaging Service (IPS) extracts diagnostic and test case data associated with critical errors from the ADR and packages the data for transport to Oracle.
Backup FilesTo restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file.User-managed backup and recovery requires you to actually restore backup files before you can perform a trial recovery of the backups.

Server-managed backup and recovery manages the backup process, such as scheduling of backups, as well as the recovery process, such as applying the correct backup file when recovery is needed.

Oracle Architecture

Memory Structures and Processes

The mechanisms of ORACLE execute by using memory structures and processes. All memory structures exist in the main memory of the computers that constitute the database system. Processes are jobs or tasks that work in the memory of these computers.

Memory Structures

ORACLE creates and uses memory structures to complete several jobs. For example, memory is used to store program code being executed and data that is shared among users. Several basic memory structures are associated with ORACLE: the system global area (which includes the database and redo log buffers, and the shared pool) and the program global area. System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance. An ORACLE instance contains the SGA and the background processes. The SGA is allocated when an instance starts and deallocated when the instance shuts down. Each instance that is started has its own SGA. The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process. A PGA is created by ORACLE when a server process is started. The information in a PGA depends on the configuration of ORACLE.

Processes

A process is a ``thread of control'' or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. An ORACLE database system has two general types of processes: user processes and ORACLE processes. A user process is created and maintained to execute the software code of an application program (such as a PRO*C program) or an ORACLE tool (such as SQL*PLUS). The user process also manages the communication with the server processes. User processes communicate with the server processes through the program interface. ORACLE processes are called by other processes to perform functions on behalf of the invoking process. ORACLE creates a server process to handle requests from connected user processes. ORACLE also creates a set of background processes for each instance Database Structures The relational model has three major aspects: Structures Structures are well-defined objects that store the data of a database. Structures and the data contained within them can be manipulated by operations. Operations Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations on a database must adhere to a pre-defined set of integrity rules. Integrity Rule Integrity rules are the laws that govern which operations are allowed on the data and structures of a database. Integrity rules protect the data and the structures of a database. An ORACLE database has both a physical and a logical structure. By separating physical and logical database structure, the physical storage of data can be managed without affecting the access to logical storage structures.

Logical Database Structure

An ORACLE database's logical structure is determined by: one or more tablespaces. the database's schema objects (e.g., tables, views, indexes, clusters, sequences, stored procedures). The logical storage structures, including tablespaces, segments, and extents, dictate how the physical space of a database is used. The schema objects and the relationships among them form the relational design of a database. Tablespaces and Data Files Tablespaces are the primary logical storage structures of any ORACLE database. The usable data of an ORACLE database is logically stored in the tablespaces and physically stored in the data files associated with the corresponding tablespace. Figure 2 illustrates this relationship. Although databases, tablespaces, data files, and segments are closely related, they have important differences: databases and tablespaces An ORACLE database is comprised of one or more logical storage units called tablespaces. The database's data is collectively stored in the database's tablespaces. tablespaces and data files Each tablespace in an ORACLE database is comprised of one or more operating system files called data files. A tablespace's data files physically store the associated database data on disk. databases and data files A database's data is collectively stored in the data files that constitute each tablespace of the database. For example, the simplest ORACLE database would have one tablespace, with one data file. A more complicated database might have three tablespaces, each comprised of two data files (for a total of six data files). schema objects, segments, and tablespaces When a schema object such as a table or index is created, its segment is created within a designated tablespace in the database. For example, suppose a table is created in a specific tablespace using the CREATE TABLE command with the TABLESPACE option. The space for this table's data segment is allocated in one or more of the data files that constitute the specified tablespace. An object's segment allocates space in only one tablespace of a database.
A database is divided into one or more logical storage units called tablespaces. A database administrator can use tablespaces to do the following: Control disk space allocation for database data. Assign specific space quotas for database users. Control availability of data by taking individual tablespaces online or offline. Perform partial database backup or recovery operations. Allocate data storage across devices to improve performance. Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database. You can query these data dictionary tables to obtain pertinent information about the database; for example, the names of the tables that are owned by you or ones to which you have access.
Data files associated with a tablespace store all the database data in that tablespace. One or more datafiles form a logical unit of database storage called a tablespace. A data file can be associated with only one tablespace, and only one database. After a data file is initially created, the allocated disk space does not contain any data; however, the space is reserved to hold only the data for future segments of the associated tablespace - it cannot store any other program's data. As a segment (such as the data segment for a table) is created and grows in a tablespace, ORACLE uses the free space in the associated data files to allocate extents for the segment. The data in the segments of objects (data segments, index segments, rollback segments, and so on) in a tablespace are physically stored in one or more of the data files that constitute the tablespace. Note that a schema object does not correspond to a specific data file; rather, a data file is a repository for the data of any object within a specific tablespace. The extents of a single segment can be allocated in one or more data files of a tablespace ; therefore, an object can ``span'' one or more data files. The database administrator and end-users cannot control which data file stores an object. Data Blocks, Extents, and Segments ORACLE allocates database space for all data in a database. The units of logical database allocations are data blocks, extents, and segments.
At the finest level of granularity, an ORACLE database's data is stored in data blocks (also called logical blocks, ORACLE blocks, or pages). An ORACLE database uses and allocates free database space in ORACLE data blocks.

Extents

The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks that are allocated for storing a specific type of information. Segments The level of logical database storage above an extent is called a segment. A segment is a set of extents which have been allocated for a specific type of data structure, and all are stored in the same tablespace. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment. ORACLE allocates space for segments in extents. Therefore, when the existing extents of a segment are full, ORACLE allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk, and may or may not span files. An extent cannot span files, though.
ORACLE manages the storage space in the data files of a database in units called data blocks. A data block is the smallest unit of I/O used by a database. A data block corresponds to a block of physical bytes on disk, equal to the ORACLE data block size (specifically set when the database is created - 2048). This block size can differ from the standard I/O block size of the operating system that executes ORACLE. The ORACLE block format is similar regardless of whether the data block contains table, index, or clustered data.

ORACLE manages the storage space in the data files of a database in units called data blocks. A data block is the smallest unit of I/O used by a database. A data block corresponds to a block of physical bytes on disk, equal to the ORACLE data block size (specifically set when the database is created - 2048). This block size can differ from the standard I/O block size of the operating system that executes ORACLE. The ORACLE block format is similar regardless of whether the data block contains table, index, or clustered data.

Header (Common and Variable)

The header contains general block information, such as block address, segment type, such as data, index, or rollback. While some block overhead is fixed in size (about 107 bytes), the total block overhead size is variable.

Table Directory

The table directory portion of the block contains information about the tables having rows in this block.

Row Directory

This portion of the block contains row information about the actual rows in the block (including addresses for each row piece in the row data area). Once the space has been allocated in the row directory of a block's header, this space is not reclaimed when the row is deleted.

Row Data

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

Free Space

Free space is used to insert new rows and for updates to rows that require additional space (e.g., when a trailing null is updated to a non-null value). Whether issued insertions actually occur in a given data block is a function of the value for the space management parameter PCTFREE and the amount of current free space in that data block.

Space Used for Transaction Entries

Data blocks allocated for the data segment of a table, cluster, or the index segment of an index can also use free space for transaction entries.
Two space management parameters, PCTFREE and PCTUSED, allow a developer to control the use of free space for inserts of and updates to the rows in data blocks. Both of these parameters can only be specified when creating or altering tables and clusters (data segments). In addition, the storage parameter PCTFREE can also be specified when creating or altering indicies (index segments).
The PCTFREE parameter is used to set the percentage of a block to be reserved (kept free) for possible updates to rows that already are contained in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
pctfree 20
This states that 20\% of each data block used for this table's data segment will be kept free and available for possible updates to the existing rows already within each block.
After a data block becomes full, as determined by PCTFREE, the block is not considered for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. Before this value is achieved, the free space of the data block can only be used for updates to rows already contained in the data block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
pctused 40
In this case, a data block used for this table's data segment is not considered for the insertion of any new rows until the amount of used space in the blocks falls to 39\% or less (assuming that the block's used space has previously reached PCTFREE).
No matter what type, each segment in a database is created with at least one extent to hold its data. This extent is called the segment's initial extent.
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 incremented size of the previous extent in that segment.
Every non-clustered table in an ORACLE database has a single data segment to hold all of its data. The data segment for a table is indirectly created via the CREATE TABLE/SNAPSHOT command.
Storage parameters for a table, snapshot, or cluster control the way that a data segment's extents are allocated. Setting these storage parameters directly via the CREATE TABLE/SNAPSHOT/CLUSTER or ALTER TABLE/SNAPSHOT/CLUSTER commands affects the efficiency of data retrieval and storage for that data segment.

Physical Database Structure

An ORACLE database's physical structure is determined by the operating system files that constitute the database. Each ORACLE database is comprised of these types of files: one or more data files, two or more redo log files, and one or more control files. The files of a database provide the actual physical storage for database information