Renaming datafies in single/multiple tablespaces

Procedure for Renaming Datafiles in a Single Tablespace

To rename datafiles in a single tablespace, complete the following steps:

1.Take the tablespace that contains the datafiles offline. The database must be open.

For example:

ALTER TABLESPACE users OFFLINE NORMAL;

2. Rename the datafiles using the operating system.

3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:

ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

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

Procedure for Relocating Datafiles in a Single Tablespace

Here is a sample procedure for relocating a datafile.

Assume the following conditions:

* An open database has a tablespace named users that is made up of datafiles all located on the same disk.

* The datafiles of the users tablespace are to be relocated to different and separate disk drives.

* You are currently connected with administrator privileges to the open database.

* You have a current backup of the database.

Complete the following steps:

1. If you do not know the specific file names or sizes, you can obtain this information by issuing the following query of the data dictionary view DBA_DATA_FILES:

SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
2> WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME BYTES
------------------------------------------ ----------------
/u02/oracle/rbdb1/users01.dbf 102400000
/u02/oracle/rbdb1/users02.dbf 102400000

2. Take the tablespace containing the datafiles offline:

ALTER TABLESPACE users OFFLINE NORMAL;

3. Copy the datafiles to their new locations and rename them using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".

Note:
You can temporarily exit SQL*Plus to execute an operating system command to copy a file by using the SQL*Plus HOST command.

4. Rename the datafiles within the database.

The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

Use the ALTER TABLESPACE...RENAME DATAFILE statement.

ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf'
TO '/u03/oracle/rbdb1/users01.dbf',
'/u04/oracle/rbdb1/users02.dbf';

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

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege.

Note:
To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

To rename datafiles in multiple tablespaces, follow these steps.

1. Ensure that the database is mounted but closed.

Note:
Optionally, the database does not have to be closed, but the datafiles (or tempfiles) must be offline.

2. Copy the datafiles to be renamed to their new locations and new names, using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".

3. Use ALTER DATABASE to rename the file pointers in the database control file.

For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:

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;

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.

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

Query Tablespaces

SQL> select * from dba_tablespaces;

Using above you can retrieve the following information

• Tablespace block size
• Tablespace status : online, offline, or read-only
• Contents of the tablespace: undo, temporary, or permanent
• Whether it uses dictionary or locally managed extents
• Whether the segment space management is automatic or manual
• Whether it is a bigfile or smallfile tablespace

SQL> select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 37888 4194302 .903320743
SYSTEM 61560 4194302 1.46770547
TEMP 0 4194302 0
UNDOTBS1 288 4194302 .006866458
USERS 112 4194302 .002670289

SQL> select tablespace_name,status,contents,extent_management extents,segment_space_management free_
space from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS EXTENTS FREE_S
------------------------------ --------- --------- ---------- ------
SYSTEM ONLINE PERMANENT LOCAL MANUAL
UNDOTBS1 ONLINE UNDO LOCAL MANUAL
SYSAUX ONLINE PERMANENT LOCAL AUTO
TEMP ONLINE TEMPORARY LOCAL MANUAL
USERS READ ONLY PERMANENT LOCAL AUTO

SQL> select tablespace_name, ((bytes/1024)/1024) MB from dba_data_files;

TABLESPACE_NAME MB
------------------------------ ----------
USERS 5
SYSAUX 300
UNDOTBS1 55
SYSTEM 490

To get the default tablespace for a user
SQL> select username, default_tablespace from dba_users;

SQL> 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;

Working with OMF Tablespaces

Using OMF (Oracle Managed File), you don’t explicitly name datafiles or temp files; the database does this for you. To enable OMF, set the initialization parameter DB_CREATE_FILE_DEST to the directory where you want the database to create and manage your data and temp files.

ALTER SYSTEM SET
db_create_file_dest=’D:\sample\OMF’
SCOPE=BOTH;

When creating a tablespace with OMF, you simply omit the filename:
CREATE TABLESPACE ; [OR] CREATE BIGFILE TABLESPACE ;

By default the datafile created using OMF will have auto extend enabled and be 100MB unless you specify a different size.

Reference: Sybex Oracle 10g Admin I Study Guide

Creating Bigfile/Smallfile Tablespaces

You create tablespaces using CREATE DATABASE or CREATE TABLESPACE statement. Several choices when creating a tablespace include: whether to make tablespace a bigfile or smallfile, whether to manage extents locally or with dictionary, and whether to manage segment space manually or automatically. Additionally there are tablespaces for temporary and undo segments.

Bigfiles are intended for very large databases. When a very large database has thousands of read/write datafiles, operations that must update the datafile headers, such as checkpoints, can take relatively very long time. If you reduce the number of datafiles, this operation can complete faster.
CREATE BIGFILE TABLESPACE
DATAFILE ‘/sample/sample/dbf’ SIZE 25G;

Bigfile tablespace is new to Oracle 10g. Bigfile tablespaces can be as many as 232 data blocks in size. So, a bigfile tablespace using 8KB data blocks can be as much as 32 TB in size. Bigfile tablespace can have only one datafile.

Smallfile tablespace is the new name for old Oracle tablespace datafile option. With a smallfile tablespace you can have multiple datafiles for a tablespace. Each datafile can be as much as 222 data blocks in size. So, a smallfile tablespace using 8KB data blocks can be as much as 32 GB in size.
CREATE TABLESPACE
DATAFILE ‘/sample/sample/dbf’ SIZE 25G;

Reference: Sybex Oracle 10g Admin I Study Guide

Tablespace and Tablespace Management

Common use of tablespace is performance related. You can place tables into a tablespace that sits on datafiles residing on one set of disk drives and placing indexes for those tables into another tablespace that sits on datafiles residing on different disk drives. This reduces disk contention that might otherwise occur on heavily accessed tables.

Reference: Sybex Oracle 10g Admin I Study Guide

more

Rename datafile in Tablespace:

select name,status from v$datafile;

alter tablespace TEST offline;

alter tablespace TEST rename datafile '/database/test1/files/test01.dbf'
to '/database/test1/files/test02.dbf';

alter tablespace TEST online;

Other commands:

select file_name,status from dba_data_files;

http://www.md.chalmers.se/Support/Software/Databases/server.901/a90117/dfiles.htm#7527

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm

Tablespace Commands, Views and Transportable Tablespaces

Commands used to create a tablespace and alter a tablespace:

create tablespace datafile '/../../.dbf' size 10M;

SQL> create tablespace test datafile '/database/test1/files/test.dbf'
size 10m autoextend on next 64K
maxsize 25M
extent management local autoallocate
segment space management auto;

Temporary tablespaces ARE NOT a candidate for automatic segment-space management:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u07/app/oradata/ORA901/temp01.dbf' SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K
SEGMENT SPACE MANAGEMENT AUTO;

ERROR at line 4:
ORA-30573: AUTO segment space management not valid for this
type of tablespace

SQL> CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

alter tablespace add datafile '/../../.dbf' size 10M;

To see the tablespace and files:
---------------------------------

select file#,ts#,name,status from v$datafile;

Modifying Tablespaces:

• ALTER TABLESPACE old_name RENAME TO new_name;
• ALTER TABLESPACE tbs_name ADD DATAFILE '/oradata/sample.dbf' SIZE 2G;
• ALTER TABLESPACE tbs_name OFFLINE;
• ALTER TABLESPACE tbs_name RENAME DATAFILE '/oradata/sample.dbf' TO '/optware/oradata/sample.dbf';
• ALTER TABLESPACE tbs_name ONLINE;
• ALTER TABLESPACE tbs_name READ ONLY;
• ALTER TABLESPACE tbs_name READ WRITE;
• ALTER TABLESPACE tbs_name BEGIN BACKUP;
• ALTER TABLESPACE tbs_name END BACKUP;
• alter tablespace TEST drop datafile '/database/test1/files/test02.dbf'; //only when the datafile is empty

NOTE: If you drop a datafile that is non-empty, you will get ORA-03262 error, saying that the file is non-empty.

Data dictionary and dynamic performance views























































Transportable Tablespaces:
more