Tuesday, November 27, 2012

0 TABLESPACE(5): Datafile operations

Tablespace can have more than 1 data files.

Add an datafile to the tablespace

Below example is creating the new tablespace and add the datafile to the tablespace.

create tablespace mytbs1 datafile size 10M autoextend on next 10M MAXSIZE 100M;
-- Add datafile: Only for small file tablespace
alter tablespace mytbs1 add datafile size 10M  autoextend on next 10M MAXSIZE 100M;
 
col TABLESPACE_NAME format a15
col FILE_NAME format a40
select  TABLESPACE_NAME,FILE_ID,FILE_NAME
from DBA_DATA_FILES
Where TABLESPACE_NAME='MYTBS1';
 
select  TABLESPACE_NAME,SUM(BYTES)/1024/1024 SIZE_MB
from DBA_DATA_FILES
Where TABLESPACE_NAME='MYTBS1'
group by TABLESPACE_NAME;

image

Drop datafile from tablespace

We can not drop the datafile that has the data on it. We would get the ORA-03262: the file is non-empty error. We also need to provide the full path of the data file.

create table system.po1 tablespace MYTBS1 AS SELECT * from dba_objects;
col TABLESPACE_NAME format a15
col FILE_NAME format a40
select  TABLESPACE_NAME,FILE_ID,FILE_NAME
from DBA_DATA_FILES
Where TABLESPACE_NAME='MYTBS1';
 
-- we can not drop the datafile as it has data on it.
alter tablespace mytbs1 drop datafile '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_mytbs1_8cc1yxn5_.dbf';
-- add new datafile
alter tablespace mytbs1 add datafile size 10M  autoextend on next 10M MAXSIZE 100M;
-- we are able to drop the newly add datafile
alter tablespace mytbs1 drop datafile '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_mytbs1_8cc27clk_.dbf';

image

Resize Datafile

We can not change the datafile size small then its own data.

Check the current free space on each datafile

col TABLESPACE_NAME format a15
col FILE_NAME format a40
SELECT
  DS.TABLESPACE_NAME,
  DS.BYTES FREE_SPACE,
  DF.BYTES TOTAL_SPACE,
  DF.FILE_NAME
FROM
  DBA_FREE_SPACE DS ,
  DBA_DATA_FILES DF
WHERE
  DS.TABLESPACE_NAME   = DF.TABLESPACE_NAME
AND DS.FILE_ID         =DF.FILE_ID
AND DS.TABLESPACE_NAME = 'MYTBS1'

image

If we try to alter the space small then it is require, we would get the ORA-03214: File Size specified is smaller than minimum required

alter database datafile '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_mytbs1_8cc1yxcp_.dbf' resize 1M;

image

Move\rename datafile

Tablespace that can be offline
  • Alter tablespace offline
  • Use OS command to move the datafile
  • alter tablespace rename datafile
  • alter tablespace online
---- Tablespace can be offline
alter tablespace mytbs1 offline;
---- use OS command to move/rename the file
! mv /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_mytbs1_8cc1yxn5_.dbf /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/mytbs1_new.dbf
alter tablespace mytbs1 rename datafile '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_mytbs1_8cc1yxn5_.dbf' to '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/mytbs1_new.dbf';
alter tablespace mytbs1 online;

image

image

Tablespace that can not be offline ( SYSTEM)

System tablespace can not be offline while the database is open.

image

  • Find the datafile location
  • shutdown database instance
  • startup mount
  • alter database rename file
  • alter database open
col TABLESPACE_NAME format a15
col FILE_NAME format a40
select  TABLESPACE_NAME,FILE_ID,FILE_NAME
from DBA_DATA_FILES
Where TABLESPACE_NAME='SYSTEM';
 
shutdown immediate
---- use OS command to move/rename the file
startup mount 
! mv /home/oracle/app/oracle/oradata/orcl/system01.dbf /home/oracle/app/oracle/oradata/orcl/system01_new.dbf
alter database rename file '/home/oracle/app/oracle/oradata/orcl/system01.dbf' to '/home/oracle/app/oracle/oradata/orcl/system01_new.dbf';
alter database open;

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates