Shulou(Shulou.com)05/31 Report--

This article mainly shows you "Oracle 11G how to achieve table space operations", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "Oracle 11G how to achieve table space operations" this article bar.

Query Tablespace Information

1.1: Query the data dictionary view DBA_TABLESPACES to obtain basic information such as the name, status, management method, area allocation method, segment management method, and table space type of each table space in the database.

SELECT TABLESPACE_NAME,STATUS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,CONTENTSFROM DBA_TABLESPACES;

Result:

1.2: Query the data dictionary view DBA_FREE_SPACE to obtain statistics on the vacancy of each table space in the database:

SELECT TABLESPACE_NAME"TABLESPACE",FILE_ID,COUNT(*)"PIECES",MAX(blocks)"MAXIMUM",MIN(blocks)"MINIMUM",AVG(blocks)"AVERAGE", SUM(blocks)"TOTAL"FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME,FILE_ID;

1.3: Query the data dictionary view DBA_TEMP_FREE_SPACE to get the table space usage information for temporary table spaces:

SELECT * FROM DBA_TEMP_FREE_SPACE

2. create tablespace

CREATE TABLESPACE syntax:

CREATE [BIGFILE|SMALLFILE] [TEMPORARY|UNDO] TABLESPACE tbs_name

DATAFILE|TEMPFILE path/filename SIZE integer [K|M] [REUSE]

[AUTOEXTEND [OFF|ON] NEXT integer [K|M]

MAXSIZE [UNLIMITED|integer [K|M]]

[TABLESPACE GROUP tablespace_group_name]

[EXTENT MANAGEMENT DICTIONARY|LOCAL]

[AUTOALLOCATE|UNIFORM SIZE integer [K|M]]

2.1 Create a locally managed permanent table space TBS1, with automatic management of partitions and segments:

CREATE TABLESPACE TBS1 DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_1.DBF' size 50M;

2.2 Create a locally managed persistent table space TBS2, with custom allocation of regions, size 512k, and automatic management of segments.

CREATE TABLESPACE TBS2 DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS2_1.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

2.3 Create a locally managed permanent table space TBS4, with customized allocation of areas, size of 512K, and manual management of segments:

CREATE TABLESPACE TBS4 DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS4_1.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512KSEGMENT SPACE MANAGEMENT MANUAL;

2.4 Create an index table space INDX, store all index information, table space files automatically expand 5M at a time, up to 100M:

CREATE TABLESPACE INDX DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/INDX_1.DBF' SIZE 50MAUTOEXTEND ON NEXT 5M MAXSIZE 100M;

2.5 Create a TBS5 table space with 10M for the first extent, 10M for the second extent, and 10% growth for the subsequent extent

CREATE TABLESPACE TBS5 DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS5_1.DBF' SIZE 50MDEFAULT STORAGE(INITIAL 10M NEXT 10M PCTINCREASE 10)SEGMENT SPACE MANAGEMENT MANUAL;

3. Creating Large File Tablespaces

3.1 Query whether databases create tablespaces for large or small files by default:

SELECT PROPERTY_NAME,PROPERTY_VALUEFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME='DEFAULT_TBS_TYPE';

3.2 Create a large file table space BIGTBS1:

CREATE BIGFILE TABLESPACE BIGTBS1 DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/BIGTBS1_1.DBF' SIZE 50MAUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

3.3 Large File Tablespace Information Query:

SELECT T.NAME TBSNAME,RFILE#,D.NAME FNAMEFROM V$TABLESPACE T,V$DATAFILE DWHERE T.TS#=D.TS# AND T.NAME='BIGTBS1'

3.4 Creating a non-standard block table space

ALTER SYSTEM SET DB_16K_CACHE_SIZE=16384;CREATE TABLESPACE TBS16DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS16_1.DBF' SIZE 60MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 16K;SELECT TABLESPACE_NAME,BLOCK_SIZE FROM DBA_TABLESPACES;

4. Maintenance Tablespaces

4.1 Add Tablespace Files

ALTER TABLESPACE TBS1 ADD DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_2.DBF' SIZE 10M;

4.1.2 Add TEMP Tablespace File

ALTER TABLESPACE TEMP ADD TEMPFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP02.DBF' SIZE 10M;

4.2 Change data file size

ALTER DATABASE DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_2.DBF' RESIZE 20M;

4.3 Change how a file expands:

ALTER DATABASE DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS2_1.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

4.5 Delete a table space file:

ALTER TABLESPACE TEMPDROP TEMPFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP02.DBF';

For normal tablespaces, simply replace TEMPFILE with DATAFILE

5. Set default tablespaces:

5.1 To view the current default tablespaces:

SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIESWHERE PROPERTY_NAME IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

5.2 Set default tablespaces:

ALTER DATABASE DEFAULT TABLESPACE TBS1;

5.2.1 Setting default temporary tablespaces:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

6. Manage temporary tablespaces

Creating a temporary table space is similar to creating a normal table space syntax, such as creating a TEMP2 temporary table space:

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/temp2_1.DBF' SIZE 20MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

You can add BIGFILE between create and temporary to create a large file temporary table space (CREATE BIGFILE TEMPORARY...)

6.1 Temporary tablespace group:

CREATE TEMPORARY TABLESPACE TEMP4 TEMPFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP4_1.DBF' SIZE 20MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 2MTABLESPACE GROUP TEMP_GROUP1;

Temporary tablespaces can be added to a group or removed from a tablespace lease using the alter tablespace statement

ALTER TABLESPACE TEMP2 TABLESPACE GROUP TEMP_GROUP1;ALTER TABLESPACE TEMP4 TABLESPACE GROUP '';ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GROUP1;

6.2 Shrink temporary tablespaces:

ALTER TABLESPACE temp SHRINK SPACE;ALTER TABLESPACE TEMP2 SHRINK TEMPFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/temp2_1.DBF';

7 undo Tablespace

7.1 Create undo tablespaces:

CREATE UNDO TABLESPACE UNDOTBS2DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/UNDOTBS2_1.DBF' SIZE 20MAUTOEXTEND ON NEXT 2M MAXSIZE 100M;

Adding undo tablespaces files is the same as doing normal tablespaces

Switch undo tablespaces:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

7.2 UNDO information retention time setting:

Set the minimum retention time of fallback information by initializing the parameter UNDO_RETENTION in seconds.

show parameter UNDO

You can find out the current UNDO_RETENTION setting and modify the default value:

ALTER SYSTEM SET UNDO_RETENTION=1800;

Re-enabling the RETENTION GUARANTEE feature of the undo table space ensures that UNDO information is retained for at least the time specified by UNDO_RETENTION.

ALTER TABLESPACE UNDOTBS2 RETENTION GUARANTEE;

The above is all the content of "Oracle 11G how to achieve table space operations" this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.