TABLESPACES

(1)永久表空间的创建
  create tablespace sale_ts 
    datafile '/opt/oracle/oradata/lh/sale01.dbf' size 1m autoextend on next 5m maxsize unlimited,
             '/opt/oracle/oradata/lh/sale02.dbf' size 1m autoextend on next 5m maxsize unlimited --对每一个文件单独指定
  extent management local
  segment space management auto;
 select * from v$tablespace
 select * from dba_data_files
 select * from dba_tablespaces
 sys:
   grant select any dictionary to scott;
 scott:
   create table t_objects
     tablespace sale_ts
     as select * from dba_objects;
 sys:
   select * from dba_data_files
     "autoextend on next 5m maxsize unlimited" 对每一个文件
   select file#,count(*) from v$bh
   where file# in (7,8) and objd=11430
   group by file# *********file#相当于file_id
  select * from dba_objects
    where object_name=upper('t_objects')   
 
 
 
 alter tablespace sale_ts  add  datafile
   '/opt/oracle/oradata/lh/sale03.dbf' size 1m autoextend on next 5m maxsize unlimited,
     '/opt/oracle/oradata/lh2/sale04.dbf' size 1m autoextend on next 5m maxsize unlimited; 
 alter database datafile
   '/opt/oracle/oradata/lh/test01.dbf' RESIZE 5M ;--3M->5M
 alter database datafile
   '/opt/oracle/oradata/lh/test01.dbf' RESIZE 3M ;--5M->3M
    
 
 create table test(id number,name varchar2(30))
   tablespace sale_ts;
 
 alter database default tablespace tbs1;
 alter database default temporary tablespace temp2;
 
 
  (2)临时表空间的创建
       create temporary tablespace temp2
         tempfile '/opt/oracle/oradata/lh/temp02.dbf'
         size 5m autoextend on;
       create temporary tablespace temp3
         tempfile '/opt/oracle/oradata/lh/temp03.dbf'
         size 50m autoextend on next 5m maxsize unlimited
         extent management local
       ;
      
       create user a identified by a
         default tablespace users
         temporary tablespace temp2;
        
       alter user scott
         temporary tablespace temp2;
      
       select * from dba_users;
       select * from v$tempfile
    select * from dba_temp_files
    select * from dba_tablespaces
    select * from dba_data_files 
         
  (3)回退表空间(undo tablespace)的创建
     create undo tablespace undotbs2
     datafile '/opt/oracle/oradata/lh/undotbs2.dbf'
     size 10m autoextend on maxsize unlimited;
     alter system set undo_tablespace=undotbs2;**使系统只有一个undo tablespace生效
     show parameter undo_tablespace
   
    select * from dba_tablespaces
     select * from dba_data_files
**在一个数据库中可以有多个undo tablespace，但是在同一时刻，只能有一个生效，所有用户都是使用一个undo tablespace

 (4)大文件表空间
 Use bigfile tablespaces for very large databases (VLDB)
  A bigfile tablespace(BFT)  contains a single file.
  The maximum file size ranges from 8 TB through 128 TB.
  Tablespaces are logically equivalent to data files.
  1 PB = 1,024 TB ; 1 EB = 1,024 PB = 1,048,576 TB = 2(60) bytes
 
  smallfile tbs的每个文件只能有4M blocks 128GB
  bigfile tbs的文件有4G blocks  128TB

 create bigfile tablespace "testbig"
    datafile '/opt/oracle/oradata/lh/big.dbf' size 10m
    logging
    extent management local
    segment space management auto;       
 select tablespace_name, bigfile from dba_tablespaces;
 select name, bigfile from v$tablespace;
 
 (5)表空间管理
   drop tablespace sale_ts ;
   drop tablespace sale_ts  including contents and datafiles;    级联删除表空间里有的段及数据文件 
     alter tablespace users offline;
     alter tablespace tbsname online;
     alter tablespace tbsname read only;
       dba_tablespaces.status= READ ONLY
     alter tablespace tbsname read write;
  
   mount:
   alter database datafile '...' offline drop;
     遇到数据库文件被删除,数据库打不开的情况,可把数据库启动到mount状态,删除数据文件,实际是删除该数据文件在控制文件中的记录,然后,打开数据库,再删除表空间（4.9）
    
create smallfile tablespace "test"
  datafile '/opt/oracle/oradata/olit/test01.dbf' size 5m
  autoextend on next 1m maxsize unlimited
  logging
  extent management local uniform size 16k
  segment space management manual ; 
 
alter database default tablespace users;
alter user u1  default tablespace sale_ts;
alter database default temporary tablespace temp2;
alter user u1  temporary tablespace temp;