Oracle Table Space Management

Oracle Table Space Management
1. Classification:
Data table Space Datatablespace
Temporary tablespace temprary tablespace
Roll back table space undo Tablespace
Index Table Space Index tablespace
Local surface space: extent management Local
Segment Automatic Management: Segement Space Management Auto



2. Data table Space
2.1 Created: Create Tablespace USER1 datafile '/data/user1.dbf ' size 20G;
2.2 Change auto-expansion: ALTER DATABASE datafile '/DATA/USER1.DBF ' autoextend on next 5M maxsize 50G;
2.3 Turn off auto-expansion: ALTER DATABASE datafile '/DATA/USER1.DBF ' autoextend off;
2.4 Expansion table space: Alter tablespace USER1 add datafile '/data/user2.dbf ' size 10G;
ALTER DATABASE datafile '/DATA/USER1.DBF ' resize 50G;
ALTER DATABASE datafile '/DATA/USER1.DBF ' autoextend on next 5M maxsize 50G;
2.5 table space online and offline; alter tablespace USER1 offline;
Alter tablespace USER1 Online;
2.6 tablespace renaming: Alter tablespace USER1 rename to USER11;
2.7 Tablespace Read only: Alter tablespace USER1 Read only;
2.8 Delete Table space: Drop tablespace USER including contents;

3. Temporary tablespace:
3.1 Creating a temporary tablespace: Create temprary tablespace TEMP1 tempfile '/data/temp1.dbf ' size 100M autoextend on next 5M maxsize 150 M
3.2 Change temporary tablespace: ALTER DATABASE default Temprary tablespace TEMP1;
3.3 Temporary table space group: Alter tablespace TEMP1 tablespace group GROUP1;
3.4 Default temporary table space changed to temporary tablespace group: ALTER DATABASE default Temprary tablespace GROUP1;
3.5 Remove TEMP1 from GROUP1: Alter tablespace TEMP1 tablespace Group ';

4. Roll Back table space
4.1 Create rollback tablespace: Create UNDO tablespace UNDB1 datafile '/data/undo1.dbf ' size 300M autoextend on next 5M maxsize 350M;
4.2 Rollback Table Space Automatic management: Undo_management=auto
Undo_tablespace=undb1
4.3 Delete rollback table space: Drop tablespace UNDB1 including contents;
4.4 Rollback Table Space Add data file: Alter tablespace UNDB1 add datafile '/data/undb2.dbf ' size 10M autoextend on next 2M maxsize 20M;
4.5 Toggle Rollback table space: Show parameter undo_tablespace;
alter system Set Undo_tablespace = UNDB2 Scope=both;

This article is from the "Common Documents" blog, so be sure to keep this source http://yujianglei.blog.51cto.com/7215578/1561191

Oracle Table Space Management