Tablespaces and Datafiles

    You are here:  
  1. Home
  2. ORACLE DBA
  3. General
  4. File Management
  5. Tablespaces and Datafiles
Hits: 6751
Rating:
( 1 Rating )

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Structure

dba_tablespaces  describes each tablespace
dba_data_files     lists each datafile in the database
dba_temp_files    describes each tempfile in the database

Datafile resize, autoextend

SQL> ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF' RESIZE 128M;
SQL> ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF' AUTOEXTEND ON NEXT 128M;

Create Tablespace

FS

SQL> CREATE TABLESPACE "DATA" DATAFILE '/mu11/ORCL/data_01.dbf' SIZE 32767M;
SQL> CREATE TABLESPACE "DATA" DATAFILE '/mu11/ORCL/data_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M;

ASM

SQL> CREATE TABLESPACE "DATA" DATAFILE '+DATA' SIZE 32767M;
SQL> CREATE TABLESPACE "DATA" DATAFILE '+DATA' SIZE 128M AUTOEXTEND ON NEXT 128M;

Cerate/Add Datafile

FS

SQL> ALTER TABLESPACE USERS ADD DATAFILE '/u02/oracle/orcl1/users03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M;
SQL> ALTER TABLESPACE USERS ADD DATAFILE '/u02/oracle/orcl1/users03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE 3G;

ASM

SQL> ALTER TABLESPACE "DATA_IDX" ADD DATAFILE '+DATA' SIZE 3G AUTOEXTEND OFF;
SQL> ALTER TABLESPACE "DATA_IDX" ADD DATAFILE '+DATA' SIZE 32767M AUTOEXTEND ON;

TEMPFILE

FS

SQL> ALTER TABLESPACE "COLVIR_TEMP" ADD TEMPFILE '/mu11/ORCL/tmp04.dbf' SIZE 32767M;

ASM

SQL> ALTER TABLESPACE "COLVIR_TEMP" ADD TEMPFILE '+DATA' SIZE 32767M;

Undo

SQL> ALTER TABLESPACE "UNDOTBS1" ADD DATAFILE '+DATA' SIZE 30G AUTOEXTEND ON;
SQL> ALTER TABLESPACE "UNDOTBS1" DROP DATAFILE '+DATA/orcl/datafile/undotbs1.670.899115317';

Show Size (Gb/Mb)

SQL> SELECT TABLESPACE_NAME, 'GB'||' '||ROUND(SUM(BYTES/1024/1024/1024)) "USED_SIZE" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

SQL> SELECT TABLESPACE_NAME, 'MB'||' '||ROUND(SUM(BYTES/1024/1024)) "USED_SIZE" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

Show Free size (Gb/Mb) 

SQL> SELECT TABLESPACE_NAME, 'GB'||' '||ROUND(SUM(BYTES/1024/1024/1024)) "FREE_SIZE" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

SQL> SELECT TABLESPACE_NAME, 'MB'||' '||ROUND(SUM(BYTES/1024/1024)) "FREE_SIZE" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

Show Size, Used, Free

Gb
SQL>
SELECT * FROM
  (SELECT tablespace_name FROM dba_tablespaces)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(SUM(bytes/1024/1024/1024),0) AS total_Gb
     FROM dba_data_files
     GROUP BY tablespace_name)
  USING (tablespace_name)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(sum(bytes/1024/1024/1024),0) AS used_Gb
     from dba_segments
     GROUP BY tablespace_name)
  USING (tablespace_name)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(SUM(bytes/1024/1024/1024),0) AS free_Gb
     FROM dba_free_space
     GROUP BY tablespace_name)
  USING (tablespace_name);

Mb
SQL>
SELECT * FROM
  (SELECT tablespace_name FROM dba_tablespaces)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(SUM(bytes/1024/1024),0) AS total_Mb
     FROM dba_data_files
     GROUP BY tablespace_name)
  USING (tablespace_name)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(sum(bytes/1024/1024),0) AS used_Mb
     from dba_segments
     GROUP BY tablespace_name)
  USING (tablespace_name)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(SUM(bytes/1024/1024),0) AS free_Mb
     FROM dba_free_space
     GROUP BY tablespace_name)
  USING (tablespace_name);

Path to DATAfiles, TABLECPACES plus size

Gb
SQL>
COL FILE_NAME FORMAT A40
COL TABLESPACE_NAME FORMAT A20
COL "GB" FORMAT 999,999,999
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024/1024 GB FROM DBA_DATA_FILES;
       FILE_NAME                                TABLESPACE_NAME GB
       ---------------------------------------- -------------------- ------------
       /u02/ORCL/users01.dbf               USERS              32
       /u02/ORCL/undotbs01.dbf           UNDOTBS1         1
       /u02/ORCL/sysaux01.dbf             SYSAUX              1
       /u02/ORCL/system01.dbf            SYSTEM              2
       /u02/ORCL/users02.dbf              USERS              32
       /u02/ORCL/users03.dbf              USERS              32
       /u02/ORCL/users04.dbf              USERS              32
       /u02/ORCL/users05.dbf              USERS              32
       /u02/ORCL/users06.dbf              USERS              32

Mb
SQL>
COL FILE_NAME FORMAT A40
COL TABLESPACE_NAME FORMAT A20
COL "MB" FORMAT 999,999,999
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB FROM DBA_DATA_FILES;

Which files and where

SQL>
SET LINESIZE 200;
SET PAGESIZE 0;
COL NAME FORMAT A60;
SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
       1  /u02/oracle/oradata/orcl/data/system01.dbf    SYSTEM
       2  /u02/oracle/oradata/orcl/data/sysaux01.dbf    ONLINE
       3  /u02/oracle/oradata/orcl/undo/undotbs01.dbf ONLINE
       4  /u02/oracle/oradata/orcl/data/users01.dbf      ONLINE

1) Create new tablespace for indexes and data

SQL> CREATE TABLESPACE "MY_DATA" DATAFILE '/u02/oracle/oradata/orcl/data/my_data01.dbf' SIZE 2G AUTOEXTEND OFF;

If we need, we can add more space for data

SQL> ALTER TABLESPACE “MY_DATA” ADD DATAFILE  '/u02/oracle/oradata/orcl/data/my_data02.dbf' SIZE 2G AUTOEXTEND OFF;

SQL> CREATE TABLESPACE "MY_INDEXES" DATAFILE '/u02/oracle/oradata/orcl/indexes/my_indexes01.dbf' SIZE 2G AUTOEXTEND OFF;

If we need, we can add more space for indexes

SQL> ALTER TABLESPACE “MY_INDEXES” ADD DATAFILE  '/u02/oracle/oradata/orcl/indexes/my_indexes02.dbf' SIZE 2G AUTOEXTEND OFF;

2) Sometimes we need to create more space for undo

SQL> CREATE UNDO TABLESPACE "UNDO" DATAFILE '/u02/oracle/oradata/orcl/undo/undo01.dbf' SIZE 1G AUTOEXTEND ON;

Set default Undo tablespace

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = "UNDO";

Delete old Undo tablespace

SQL> drop tablespace UNDOTBS1;

SQL> host rm /u02/oracle/oradata/orcl/undo/undotbs01.dbf

3) Crete new tablespace for temp files

SQL> CREATE TEMPORARY TABLESPACE "MY_TEMP" TEMPFILE '/u02/oracle/oradata/orcl/temp/my_temp01.dbf' SIZE 2G AUTOEXTEND OFF;

Add new file for temp tablespace

SQL> ALTER TABLESPACE “MY_TEMP” ADD TEMPFILE '/u02/oracle/oradata/orcl/temp/my_temp02.dbf' SIZE 2G AUTOEXTEND OFF;

 

Change files location

SQL> HOST mkdir -p /u02/oracle/oradata/orcl/data
SQL> HOST mkdir -p /u02/oracle/oradata/orcl/indexes
SQL> HOST mkdir -p /u02/oracle/oradata/orcl/undo
SQL> HOST mkdir -p /u02/oracle/oradata/orcl/temp

SQL> SELECT NAME FROM V$DATAFILE;
       NAME
       --------------------------------------------------------------------------------
       /u02/oracle/oradata/orcl/system01.dbf
       /u02/oracle/oradata/orcl/sysaux01.dbf
       /u02/oracle/oradata/orcl/undotbs01.dbf
       /u02/oracle/oradata/orcl/users01.dbf

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

SQL> HOST mv /u01/oracle/oradata/orcl/system01.dbf /u02/oracle/oradata/orcl/data/system01.dbf
SQL> HOST mv /u01/oracle/oradata/orcl/sysaux01.dbf /u02/oracle/oradata/orcl/data/sysaux01.dbf
SQL> HOST mv /u01/oracle/oradata/orcl/users01.dbf /u02/oracle/oradata/orcl/data/users01.dbf
SQL> HOST mv /u01/oracle/oradata/orcl/undotbs01.dbf /u02/oracle/oradata/orcl/undo/undotbs01.dbf

Path and name of Temfiles

SQL> 
COL FILE# FORMAT 999;
COL NAME FORMAT A40;
SELECT FILE#, NAME FROM V$TEMPFILE;
       NAME
       --------------------------------------------------------------------------------
       /u02/oracle/oradata/orcl/temp01.dbf

Path, name and tablespaces of tempfiles

SQL>
SET LINESIZE 200;
COL FILE_NAME FORMAT A60;
COL TABLESPACE_NAME FORMAT A15;
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024/1024 GB FROM DBA_TEMP_FILES;
          FILE_NAME                                              TABLESPACE_NAME     GB
          -----------------------------------------------------       -----------     ----------
          /SNAP/tmpredo/ORCL/temp/temp.dbf               TEMP           ,125
          /SNAP/tmpredo/ORCL/temp/temp_01.dbf         TEMP_01       30

SQL> HOST mv /u01/oracle/oradata/orcl/temp01.dbf /u02/oracle/oradata/orcl/temp/temp01.dbf

SQL> ALTER DATABASE RENAME FILE '/u01/oracle/oradata/orcl/system01.dbf' TO '/u02/oracle/oradata/orcl/data/system01.dbf';
SQL> ALTER DATABASE RENAME FILE '/u01/oracle/oradata/orcl/sysaux01.dbf' TO '/u02/oracle/oradata/orcl/data/sysaux01.dbf';
SQL> ALTER DATABASE RENAME FILE '/u01/oracle/oradata/orcl/users01.dbf'  TO '/u02/oracle/oradata/orcl/data/users01.dbf';
SQL> ALTER DATABASE RENAME FILE '/u01/oracle/oradata/orcl/undotbs01.dbf' TO '/u02/oracle/oradata/orcl/undo/undotbs01.dbf';
SQL> ALTER DATABASE RENAME FILE '/u01/oracle/oradata/orcl/temp01.dbf' TO '/u02/oracle/oradata/orcl/temp/temp01.dbf';

SQL> ALTER DATABASE OPEN;

 

(!) Restore datafile from backup (!)

RMNAN> RESTORE DATAFILE '/u01/oradata/devdb/dev1_01.dbf’;

Newsletter

Enter your email to join our mailing list

Tag Cloud

Oracle DBA Exadata Linux

Quick Contact

Search