Oracle Study Note:tablespace and Data Files

1.how to create a tablespace this employs the most common features

1 CreateTablespace Tb_name #Createbigfile tablespace tb_name2DataFile '/U01/DBFile/Orcl/tb_name.dbf '3 size 100m4Autoextend onMaxSize 1000m #don ' t recommend UseThe Autoextend feature.if  UseThis feature,suggest always specify a corresponding MAXSIZE.5Extent management local #A locally managed tablespace uses A bitmapinchThe datafile  toEfficiently determine whether an extent is inch  Use6Uniform size 128k #instruct Oracle toAllocate size forEach extent via the UNIFORM SIZE[size]Clause.thedefaultUniform extent size is1MB7SegmentSpaceManagement auto; #the SEGMENTSPACEMANAGEMENT AUTO clause instructs Oracle toManage theSpacewithin the block.8nologging; #you can turnoffThe generation ofRedo fordirect path loading.9         defaultRow store compress advanced;

2.renameing a tablespace. When you rename a tablespace,oracle updates the name of the tablespace in the data dictionary,control files,and data file Headers. Keep in mind, renaming a tablespace doesn ' t rename any associated data files.

1 SQL>alter to New_name

3.alter a tablespace logging mode

1 SQL>alter tablespace tb_name nologging;

4.confirm the tablespace logging mode by querying the Dba_tablespaces view

1 SQL>Select from Dba_tablespaces;

5.changing a tablespace ' s write mode

1Sql> AlterTablespace Tb_nameRead  only; #you can ' t make a tablespace thatcontainsActiverollbackSegmentRead- only.2Sql> AlterTablespace Tb_nameReadwrite;3Sql> Alter Tabletable_nameRead  only; #modify Individual Tables

6.dropping a tablespace

1 SQL>alter to first it offline. 2 SQL>dropandcascade constraints;

7.using Oracle Managed Files

A) db_create_file_dest

b) db_create_online_log_dest_n

c) db_recovery_file_dest

1 SQL>alterset db_create_file_dest='/u01 ';

8.enabling default table compression within a tablespace

1 SQL>alterdefault  row store compress advanced; 2 SQL>alter[Basic | nocompres]

9.displaying Oracle error messages and action

1 01653

10.altering tablespace Size

1Sql> Alter DatabaseDataFile '/U01/user01.dbf ' resize 1g;2Sql> AlterTablespace usersAddDataFile '/U02/users02.dbf ' size 100m;3Sql> AlterTablespace big_data Resize 1T; # withBigfile Tablespace,you has theoption  ofUsing theALTERTablespace statement toResize the datafile. This works because onlyOne datafilecan be associated withA bigfile tablespace.

11.to add space to a temporary tablespace

1Sql> SelectName,bytes fromV$tempfile; #first Query the V$tempfileView  toVerify the CurrentSize andLocation of Temporarydata files.2Sql> Alter DatabaseTempfile ' u01/temp01.dbf ' resize 500m;3Sql> AlterTablespaceTemp AddTempfile ' u01/TEMP02.BDF ' size 5000m;

12.toggling data files offline and online

1Sql> Altertablespace tb_name offline;2Sql> Alter DatabaseDataFile4Offline for Drop#ifYourDatabaseIsn ' tinchArchivelog Mode,you must specifyALTERDataFile ... OFFLINE for DROP  whenTaking a datafileOffline. whenYou UseThe OFFLINE for DROPclause, no Checkpiont isTaken onThe datafile, this means need toPerform media recovery onThe datafilebefore bringing it online.3Sql>Recovery datafile4;

13.renaming or relocating a data file.new in Oracle 12c is the ALTER DATABASE MOVE datafile commend.this commend allows yo U to rename or move datafiles without any downtime.

1Sql> Alter DatabaseMove DataFile ' u01/USER01.DBF ' to‘/U01/user02.dbf ';2Sql> Alter DatabaseMove DataFile4  to' U02/USER01.DBF ' Keep; # toKeep a copy ofThe originalfile.3Sql> Alter DatabaseMove DataFile4  to‘/U01/USER02.DBF ' reuse; #specify the REUSE clause toOverwrite an existingfile.

Oracle Study Note:tablespace and Data Files