2009.01.18_26_27_28 Managing Tablespaces&DataFiles

■ Tablespaces and Data Files
Oracle stores data logically in tablespace and physically in data files

■ Tablespaces
・Can belong to only one database at a time
・consist of one or more datafiles;
・are further divided in logical units of storage
■ Data Files
・Can belong to only one tablespace and one database
・be a respository for schema object data
■Types of tablespaces
①・SYSTEM tablespace
　・NON-SYSTEM tablespace
②permanent,undo,temporary
Create tablespaces:
SQL>select * from v$tablespace
SQL>desc dba_data_files;
SQL>col file_name format a40;
SQL>select file_name,tablespace from dba_data_files
SQL>create tablespace paul datafile '/u01/oradata/wilson/paul01.dbf'size 20M
SQL>!
$cd /u01/oradata/wilson/
SQL>select * from v$tablespace;
SQL>desc dba_data_files
SQL>select file_name,tablespace from dba_data_files

■Space Management in Tablespace
・Dictionary-managed tablespace(before v8.15 only this)
・Locally　managed tablespace(afer 9i added)recommended
 with a locally-managed tablespace,a bitmap stored in each data file is used to manage the extents.To free space,the system sets a bit back to 0.
Bit and Byte:1 byte = 8 bits
 Migrate a dictionary managed SYSTEM tablespace to locally managed:
SQL>exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
　①backup of your database
　②
　③
　④
　⑤
　⑥in restricted mode
　⑦execute
■Undo Tablespaces
■Temporary Tablespaces
used for sort operations;...
Default temporary tablespace:
・create database　XXX
・alter database defaulte temporary tabelspace　XXX
SQL>desc database_properties;
SQL>col property_name format a20
SQL>col property_value format a20
SQL>select property_name, property_value from database_properties
SQL>select * from v$tablespace;
SQL>desc dba_tablespaces;
SQL>select tablespace_name,contents,extent_management from dba_tablespaces;
SQL>create temporary tablespace mytemp tempfile '/u01/oradata/wilson/mytemp01.dbf' size 100M extent management local;
SQL>alter database defaulte temporary tabelspace　mytemp；
SQL>select * from database_properties
Default temporary tablespace restrictions:
cannot be ①Dropped until after a new default is made avaiable②Taken offline③Altered to a permanent tablespace
■Read-Only Tablespaces
SQL>Alter tablespace userdata Read only;
cause a checkpoint;
data only for read;
Objects can be dropped from tablespace;
SQL>create tablespace wenchuan datafile '/u01/oradata/allen/wenchuan1.dbf' size 20M extent management local uniform size 128k;
SQL>create DZ identified by DZ DEFAUL TABLESPACE wenchuan ;
SQL>grant connect ,resource to DZ;
SQL>alter tablespace wenchuan read only;
SQL>alter tablespace wenchuan read write;
■Taking a Tablespaces Offline
cannot be taken offline:
    SYSTEM tablespace;Tablespaces with active undo segements;Default temporary tablespace;
SQL>alter tablespace userdata offline;
SQL>alter tablespace userdata online;