[Oracle] Oracle数据库中表空间的基本管理操作小结

create [smallfile|bigfile] tablespace <identName> datafile '<path&name>' [extent management local uniform] size <n>k|m|g|t

select property_name,property_value from database_properties where property_name like '%TBS%';

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

select tablespace_name,file#,v.status,v.enabled from dba_files d,v$datafile v where d.file_id=v.file#;
  alter tablespace <tablespace_name> read only|read write|offline|online;

alter database set default smallfile|bigfile tablespace;

alter tablespace datafile '<path & name>'|file# autoextend on|off [next <size>|maxsize <size&#8234;>];
     select tablespace_name, file_name, autoextensible from dba_data_files;

alter tablespace datafile '<path & name>'|file# resize <size>;
     alter tablespace datas add datafile '<path & name>' size <size>;

alter tablespace rename file '<old>' to '<new>';

alter tablespace rename file '<old>' to '<new>';--->startup

drop tablespace <name> [including contents [and datafiles]];

select occupant_name,schema_name from v$sysaux_occupants;

(alter tablespace sysaux begin backup;ho cp ?/sysaux01.dbf ?/bak/;alter tablespace sysaux end backup;)

rman(rman target /;backup tablespace sysaux;)

show parameter undo_tablespace;

create smallfile|bigfile undo tablespace <name> datafile <path&name> size <size>;

drop tablespace undo including contents and datafiles;

undo space=(undo_retention*undo_blocks_per_second * db_block_size) + db_block_size
  show parameter undo_retention;--->undo_retention
  show parameter db_block_size;--->db_block_size
  select sum(undoblks)/sum((end_time - begin_time)*10800) from v$undostat;--->undo_blocks_per_second

max(block_id) * db_block_size
  show parameter db_block_size;--->db_block_size

select max(block_id) from dba_extents where file_id=2;--->block_id