Faster Management of Oracle Datafiles

Today we are going to discuss with you the actual management operations of Oracle Datafiles. I saw the management information of Oracle Datafiles on the relevant website two days ago and thought it was quite good. I will share it with you, I hope it will help you in this regard.

 
 

  
  
SQL>alter tablespace ...add datafile ...  
  
  
SQL>alter tablespace ...add tempfile ...  
 
 

1. add files:

 
 

  
  
SQL>CREATE SMALLFILE TABLESPACE "STAGING" DATAFILE 
'/u01/app/oracle/oradata/orcl/staging01.dbf' SIZE 3M REUSE NOLOGGING  
  
  
EXTENT MANAGEMENT LOCAL  
  
  
SEGMENT SPACE MANAGEMENT AUTO  
 
 

1. Add a file

 
 

  
  
SQL>alter tablespace myts01  
  
  
add datafile 'd:\oracle\product\10.2.0\oradata\orcl\myts03.dbf' size 10M;  
 
 

2. Manage temporary tablespace for Oracle Datafiles to add files

 
 

  
  
SQL>alter tablespace temp  
  
  
add tempfile 'd:\oracle\product\10.2.0\oradata\orcl\mytemp.dbf' size 10M;  
  
  
==============================================================  
 
 

Ii. Change the data file size

1. Set the data file to automatically increase

 
 

  
  
SQL>create smallfile tablespace myts02  
  
  
datafile 'd:\oracle\product\10.2.0\oradata\orcl\myts02_1.dbf' size 10M  
  
  
autoextend on  
  
  
next 5M  
  
  
maxsize 50M;  
  
  
SQL>alter tablespace myts02  
  
  
add datafile 'd:\oracle\product\10.2.0\oradata\orcl\myts02_2.dbf' size 10M  
  
  
autoextend on  
  
  
next 4k  
  
  
maxsize 40M;  
  
  
SQL>alter database  
  
  
datafile 'd:\oracle\product\10.2.0\oradata\orcl\myts01_3.dbf'  
  
  
autoextend on  
  
  
next 2M  
  
  
maxsize 20M;  
 
 

Cancel automatic Growth

 
 

  
  
SQL>alter database  
  
  
datafile 'd:\oracle\product\10.2.0\oradata\orcl\myts01_3.dbf'  
  
  
autoextend off;  
 
 

2. Modify the data file size

 
 

  
  
SQL>alter database  
  
  
datafile 'd:\oracle\product\10.2.0\oradata\orcl\myts01_3.dbf' resize 100M;   
 
 

The above content describes the management of Oracle Datafiles, hoping to help you in this regard.