Tablespace

TABLESPACE DEFINITION : A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer. Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user data is placed in other tablespaces. The files in a tablespace usually share a common characteristic. For example, a database for a wholesale distributor might consist of tablespaces entitled ACCOUNTING, ORDERS, SHIPPING, SERVICE, and SYSTEM. Each tablespace might contain only one data file, or thousands of files, or anything in between. Tablespaces can be created, deleted, and merged, just as can the folders on the hard drive of a computer. Create Tablespace  create tablespace giri datafile 'D:\oracle\product\10.2.0\oradata\giriraj\giri.dbf' size 10m autoextend on; To view about Tablespaces : select * from v$tablespace; To Assign Default Tablespace : alter database default tablespace giri;  To View username and its TS : select username, default_tablespace from dba_users;  Assign TS to Particular User : alter user giriraj default tablespace system;  Switch to On / Off line : alter tablespace giri offline;  To Drop TS : drop tablespace sat; ( For Empty TS )  drop tablespace sat including contents and datafiles;  To Rename TS : alter tablespace giri rename to giriraj;  To Resize Datafile : alter database datafile 'd:/giri.dbf' resize 5m;  To view the Tablespace Size : select * from dba_data_files; / Desc dba_data_files; To Estimate Size of TS : select owner, sum(bytes/1024/1024/1024) from dba_segments group by owner;  To Add Data File : alter tablespace giri add datafile 'd:/ndf.dbf' size 5m;  To View TS With How many Datafiles ( count ) : select tablespace_name, count(*) from dba_data_files group by tablespace_name;  To See how many DATA FILES in TS : select tablespace_name, file_name from dba_data_files where tablespace_name like '%GIRI%'

 To Change Read only / Read write mode : alter tablespace giri read only / read write;

Undo Tablespace

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo. Undo records are used to:

• • • • •

Roll back transactions when a ROLLBACK statement is issued Recover the database Provide read consistency Analyze data as of an earlier point in time by using Oracle Flashback Query Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.  To see undo Parameters : show parameter undo; SQL> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- --------undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1

 To change undo_managemnet to MANUAL / AUTO : alter system set undo_management=manual scope=spfile; (In Mount Stage )  Change UNDO_RETENTION at any time using the ALTER SYSTEM statement: o ALTER SYSTEM SET UNDO_RETENTION = 2400; To create undo tablespace

create undo tablespace satundo datafile 'd:/sadata.dbf' size 4m;

 To make it as default

alter system set undo_tablespace=satundo;

View

Description

V$UNDOSTAT

Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.

V$ROLLSTAT

For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace

V$TRANSACTION

Contains undo segment information

DBA_UNDO_EXTENTS

Shows the status and size of each extent in the undo tablespace.

DBA_HIST_UNDOSTAT

Contains statistical snapshots of V$UNDOSTAT information. Please refer to Oracle Database 2 Day DBA for more information

Change retention to guarantee : alter tablespace satundo retention guarantee; Temporary table spaces Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), 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, MINUS, Sort-Merge joins, etc The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

Create Temp Tablespace

SQL> CREATE TEMPORARY TABLESPACE temp

Set Default Temporary Tablespaces: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; To see the default temporary tablespace for a database, execute the following query: SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; V$TEMPFILE, DBA_TEMP_FILES, monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE Free space: V$TEMP_SPACE_HEADER

Add temp file:  alter tablespace temp1 add tempfile 'd:\abc.dbf' size 2m;

Listing Tablespaces and Default Storage Parameters To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:

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; TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT ---------- ----------- -------- ------- ------- -----------SYSTEM 106496 106496 1 99 1 TEMP 106496 106496 1 99 0 USERS 57344 57344 1 99 1

PCT_INCREASE

Listing the Datafiles and Associated Tablespaces of a Database To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:

SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES; FILE_NAME BLOCKS TABLESPACE_NAME --------------------- ------------------/U02/ORACLE/IDDB3/DBF/RBS01.DBF 1536 RBS Displaying Statistics for Free Space (Extents) of Each Tablespace To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

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; TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE ---------- ------- ------ ------- ------- ------- -----SYSTEM 1 1 119 119 119 119 TEMP 4 1 6399 6399 6399 6399 TESTTBS 5 5 6364 3 1278 6390 USERS 3 1 363 363 363 363

TOTAL

Viewing Tablespace Information View

Description

V$TABLESPACE

Name and number of all tablespaces from the control file.

DBA_TABLESPACES, USER_TABLESPACES Descriptions of all (or user accessible) tablespaces. DBA_TABLESPACE_GROUPS

Displays the tablespace groups and the tablespaces that belong to them.

DBA_SEGMENTS, USER_SEGMENTS

Information about segments within all (or user accessible) tablespaces.

DBA_EXTENTS, USER_EXTENTS

Information about data extents within all (or user accessible) tablespaces.

DBA_FREE_SPACE, USER_FREE_SPACE

Information about free extents within all (or user accessible) tablespaces.

V$DATAFILE

Information about all datafiles, including tablespace number of owning tablespace.

V$TEMPFILE

Information about all tempfiles, including tablespace number of owning tablespace.

DBA_DATA_FILES

Shows files (datafiles) belonging to tablespaces.

DBA_TEMP_FILES

Shows files (tempfiles) belonging to temporary tablespaces.

V$TEMP_EXTENT_MAP

Information for all extents in all locally managed temporary tablespaces.

V$TEMP_EXTENT_POOL

For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.

V$TEMP_SPACE_HEADER

Shows space used/free for each tempfile.

DBA_USERS

Default and temporary tablespaces for all users.

DBA_TS_QUOTAS

Lists tablespace quotas for all users.

V$SORT_SEGMENT

Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.

V$TEMPSEG_USAGE

Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.