2009.01.20_29_30 Managing Tablespaces&Data files

■Changing Strorage Settings

■Resizing a Tablespace

 ①Changing the size of a data file:

   -Automatically using AUTOEXTEND

   -Manually using ALTER DATABASE

 ②Adding a data file using ALTER TABLESPACE   (Recommend)

SQL>desc dba_free_

SQL>SELECT a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes-b.bytes)/a.bytes)*100,2)percent_used FROM

(SELECT tablespace_name,sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name)a,

(SELECT tablespace_name,sum(bytes) bytes,max(bytes) largest FROM dba_free_space GROUP BY tablespace_name)b

WHERE a.tablespace_name =b.tablespace_name

ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC

 □Enable Automatic Extension of data file

   commands:CREATE DATABASE;CREATE TABALESPACE;ALTER TABLESPACE...ADD DATAFILE

SQL>alter tablespace whenchuan add datafile '/u01/oradata/allan/wenchuan2.dbf'size 20M autoextend on next 10M maxsize 100M

SQL>desc DBA_DATA_FILES;

SQL>col file_name a10;

SQL>select file_name,tablespace_name,autoextensible from dba_data_files;

SQL>alter database datafile '/u01/oradata/allen/wenchuan1.dbf' autoextend on next 10M maxsize 100M

 □Manually Resizing data file

SQL>col file_name format a40

SQL>col tablespace_name format a15

SQL>select file_name,tablespace_name,bytes from dba_data_files

SQL>select file_name,tablespace_name,bytes from dba_temp_files

SQL>alter database datafile '/u01/oradata/allan/wenchuan1.dbf' resize 40M

SQL>alter database datafile 'u01/oradata/allan/example01.dbf' resize 1 M

 □Adding Data Files to a Tablespace

SQL>alter tablespace whenchuan  add datafile 'u01/oradata/allan/wenchuan01.dbf' size 100M

■Moving Data files

①SQL>alter tablespace wenchuan offline;

$cp 'u01/oradata/allan/example01.dbf' 'u01/oradata/example01.dbf'

SQL>alter tablespace wenchuan rename datafile ''u01/oradata/allan/example01.dbf' to 'u01/oradataexample01.dbf'

SQL>alter tablespace online;

②SQL>shutdown immediate;

$mv  'u01/oradata/allan/example01.dbf' 'u01/oradata/example01.dbf'

SQL>conn /as sysdba

SQL>startup mount;

SQL>alter database rename file 'u01/oradata/allan/example01.dbf' to 'u01/oradata/example01.dbf'

SQL>alter database startup;

■Dropping Data files

you cant drop a tablespace if it:

    is the SYSTEM tablespace;

    has active segments;

SQL>drop tablespace wenchuan including contents and datafiles;

■Get Tablespace Information

Tablespace information:

-DBA_TBALEPACES

-V$TABLESPACE

Data file information:

-DBA_DATA_FILES

-V$DATAFILE

Temp file information:

-DBA_TEMP_FILES

-v$tempfile