Daily maintenance and management of Oracle table space

Directory

• Daily maintenance and management of Oracle table space
  • 1. Create a data table space
  • 2. Create a temporary table space
  • 3. Create UNDO table Space
  • 4, table space expansion and modification size
  • 5. Table Space Renaming
  • 6. Deletion of table space
  • 7. Change the Read and write mode of table space
  • 8. Change the online mode of table space

Daily maintenance and management of Oracle Tablespace 1. Create a data table space

querying for tables and views : "Using version Oracle 11gR2"
1. View tablespace Information
Dba_tablespaces
V$tablespace
2. View data files
Dba_data_files
V$datafile

3. View Temporary tablespace
Dba_temp_files
V$tempfile

Current Data File location :

SQL> col file_name format a60;SQL> select file_name from dba_data_files;FILE_NAME------------------------------------------------------------+DGSYSTEM/kyeupdb/datafile/system.271.978829205+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323+DGSYSTEM/kyeupdb/datafile/users.275.978829391+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421+DGSYSTEM/kyeupdb/datafile/kye01.dbf6 rows selected.

• Create a table space for a single data file

SQL> select tablespace_name from dba_tablespaces;TABLESPACE_NAME------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERSTS_EXAMPLEKYE_TBS017 rows selected.SQL> create tablespace kye_tbs02 datafile ‘+DGSYSTEM/kyeupdb/datafile/kye02.dbf‘ size 2M  2  autoextend off  3  segment space management auto;Tablespace created.

Autoextend off-does not automatically expand
Segment Space management auto-automatic segment Management recommendation

• Create Multiple data files

SQL> CREATE TABLESPACE kye_tbs03 LOGGING DATAFILE ‘+DGSYSTEM/kyeupdb/datafile/kye03_1.dbf‘ SIZE 2M AUTOEXTEND OFF,  ‘+DGSYSTEM/kyeupdb/datafile/kye03_2.dbf‘ SIZE 2M AUTOEXTEND OFF  EXTENT MANAGEMENT LOCAL   SEGMENT SPACE MANAGEMENT AUTO;Tablespace created.SQL> create tablespace kye_tbs04 logging datafile  2  ‘+DGSYSTEM/kyeupdb/datafile/kye04_1.dbf‘ size 2M autoextend on next 1M maxsize 20M,  3  ‘+DGSYSTEM/kyeupdb/datafile/kye04_2.dbf‘ size 2M autoextend on next 1M maxsize 20M  4  extent management local  5  segment space management auto;Tablespace created.

• Creating large table Spaces

SQL> create bigfile tablespace kye_bigtbs1 datafile  2  ‘+DGSYSTEM/kyeupdb/datafile/kye_bigfile1.dbf‘ size 1G;Tablespace created.

2. Create a temporary table space

SQL> create temporary tablespace kye_tmptbs1  2  tempfile ‘+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf‘  3  size 5M autoextend off;Tablespace created.

3. Create UNDO table Space

SQL> create undo tablespace kye_undotbs1  2  datafile ‘+DGSYSTEM/kyeupdb/datafile/kye_undo1.dbf‘  3  size 10m autoextend off;Tablespace created.

4, table space expansion and modification size

• Table-Space Extensions

---View the table space created at this time sql> col name format A30;       Sql> select * from V$tablespace; ts# NAME INC BIG FLA ENC----------------------------------------------------0 SYSTEM Y     ES No Yes 1 sysaux Yes No Yes 2 UNDOTBS1 Yes No Yes 3 TEMP no no Yes     4 USERS Yes No Yes 5 ts_example Yes No Yes 7 kye_tbs01 Yes No Yes    8 KYE_TBS02 Yes No Yes 9 kye_tbs03 Yes No Yes kye_tbs04 Yes No Yes KYE_BIGTBS1 Yes Yes ts# NAME INC BIG FLA ENC-------------------------------------- --------------kye_tmptbs1 no no YES kye_undotbs1 Yes no YES13 rows selected. sql> col file_name format A55; Sql> select file_name from Dba_data_files; file_name-------------------------------------------------------+dgsystem/kyeupdb/datafile/ system.271.978829205+dgsystem/kyeupdb/datafile/sysaux.272.978829265+dgsystem/kyeupdb/datafile/undotbs1.273.978829323+dgsystem/ kyeupdb/datafile/users.275.978829391+dgsystem/kyeupdb/datafile/ts_example.277.978855421+dgsystem/kyeupdb/ datafile/kye01.dbf+dgsystem/kyeupdb/datafile/kye02.dbf+dgsystem/kyeupdb/datafile/kye03_1.dbf+dgsystem/kyeupdb/ Datafile/kye03_2.dbf+dgsystem/kyeupdb/datafile/kye04_1.dbf+dgsystem/kyeupdb/datafile/kye04_2.dbffile_ NAME-------------------------------------------------------+dgsystem/kyeupdb/datafile/kye_bigfile1.dbf+ Dgsystem/kyeupdb/datafile/kye_undo1.dbf13 rows selected. Sql> alter tablespace kye_tbs01 add datafile ' +dgsystem/kyeupdb/datafile/kye01_1.dbf ' size 2m autoextend off; Tablespace altered. Sql> alter tablespace KYE_TMPTBS1 add tempfile ' +dgsystem/kyeupdb/datafile/kye_tmptbs1_1.dbf ' size 2m autoextend off; Tablespace altered.

• Modify Size

SQL> col name format a55;SQL> select name,bytes/1024/1024 from v$tempfile;NAME                            BYTES/1024/1024------------------------------------------------------- ---------------+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379              1024+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf                5+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1_1.dbf                  2SQL> alter database tempfile ‘+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf‘ resize 6m;Database altered.SQL> select name,bytes/1024/1024 from v$tempfile;NAME                            BYTES/1024/1024------------------------------------------------------- ---------------+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379              1024+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf                6+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1_1.dbf                  2

5. Table Space Renaming

SQL> alter tablespace kye_bigtbs1 rename to kye_bigtbs;Tablespace altered.

6. Deletion of table space

Drop tablespace table space name;--delete table space directly without deleting the corresponding data file
Drop tablespace table space name including contents and datafiles; --plus this option is deleted along with the data file ( common )

SQL>  drop tablespace kye_tbs04 including contents and datafiles;Tablespace dropped.SQL> drop tablespace kye_bigtbs including contents and datafiles cascade constraints;Tablespace dropped.

7. Change the Read and write mode of table space

SQL> alter tablespace kye_tbs03 read only;Tablespace altered.SQL> alter tablespace kye_tbs03 read write;Tablespace altered.

You can also modify the table:

SQL> create table kye001 (id int,name varchar2(22));Table created.SQL> alter table kye001 read only;Table altered.SQL> alter table kye001 read write;Table altered.

8. Change the online mode of table space

alter tablespace kye_tbs01 offline;Tablespace altered.SQL> alter tablespace kye_tbs01 online;Tablespace altered.

As can be seen in the above test, directly can set the table space offline, but in the following operation can be directly set to the data file offline?
You can manipulate a data file:

alter database datafile 9 offline;alter database datafile ‘+DGSYSTEM/kyeupdb/datafile/kye03_2.dbf‘ online;alter database datafile 9 offline for drop;

An error occurred while working with the data file:
Offline is not allowed immediately unless media recovery is enabled. In Noarchivelog mode, offline data files are not immediately available.

Daily maintenance and management of Oracle table space