逻辑存储结构
一：逻辑存储结构简介
`
- 一个表空间对应着一个或者多个数据文件；
- 一个表空间对应着一个或者多个段。
- 一个段对应一个或者多个区
- 一个区对应多个块
- 一个oracle块对应多个操作系统块
- 数据库文件是由操作系统数据库构成
- 块是最小的储存单位；
- 区是最小的分配空间大小的单位
1：逻辑存储层次结构
- 一个段只能存在一个表空间里面
- 段是由区组成的。区可以来源于多个数据文件。
2：逻辑空间管理
SYS@orcl> desc dba_extents; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) EXTENT_ID NUMBER FILE_ID NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER RELATIVE_FNO NUMBER SYS@orcl> create tablespace my datafile 'a1.dbf' size 20m; Tablespace created. SYS@orcl> select SEGMENT_NAME,SEGMENT_TYPE from dba_extents where tablespace_name ='my'; no rows selected SYS@orcl> desc dba_tablespaces; Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NUMBER MAX_SIZE NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) FORCE_LOGGING VARCHAR2(3) EXTENT_MANAGEMENT VARCHAR2(10) ALLOCATION_TYPE VARCHAR2(9) PLUGGED_IN VARCHAR2(3) SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) DEF_TAB_COMPRESSION VARCHAR2(8) RETENTION VARCHAR2(11) BIGFILE VARCHAR2(3) PREDICATE_EVALUATION VARCHAR2(7) ENCRYPTED VARCHAR2(3) COMPRESS_FOR VARCHAR2(12) SYS@orcl> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where TABLESPACE_NAME='MY'; SEGMEN ------ AUTO SYS@orcl>
SYS@orcl> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where TABLESPACE_NAME='MY'; SEGMEN ------ AUTO SYS@orcl> create table tt_1(id int) pctfree 10 pctused 40; Table created. SYS@orcl> desc dba_tables; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) FLASH_CACHE VARCHAR2(7) CELL_FLASH_CACHE VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) COMPRESS_FOR VARCHAR2(12) DROPPED VARCHAR2(3) READ_ONLY VARCHAR2(3) SEGMENT_CREATED VARCHAR2(3) RESULT_CACHE VARCHAR2(7) SYS@orcl> select PCT_FREE,PCT_USED from dba_tables where TABLE_NAME='TT_1'; PCT_FREE PCT_USED ---------- ---------- 10 40 SYS@orcl> create table tt_2(id int) ; Table created. SYS@orcl> select PCT_FREE,PCT_USED from dba_tables where TABLE_NAME='TT_2'; PCT_FREE PCT_USED ---------- ---------- 10 40 SYS@orcl>
二：数据块概述
1：数据块和操作系统块
- oracle数据块与系统块的对应关系是一对多的关系；
数据库块大小
表空间块大小
SYS@orcl> show parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192 SYS@orcl>
2：数据块格式
- 数据块是由数据块头和数据块主体组成。
- 数据块主体：是由数据构成
- 当不断向数据库插入数据的时候，数据库空闲空间不断变小，同时，数据块头的占用空间也在不断的变大。即：数据块头的占用空间大小是随着数据插入量的变化而变化的。块头的大小也不是一成不变的。
- 数据块头则有：数据块地址（即：这个数据块的地址是什么。）、表目录(即：这个数据块中存放的是哪几个表的数据)、行目录(即：这个数据块存放的是哪一个表的那几行的数据)、(ITL SLOT)事务槽（这事务槽是存放在数据块头部空间的，要想使用数据块的时候必须要获得这事务槽的资源。只有获取这事务槽资源之后，才能向数据块中插入数据。）
- 在数据块当中都是以行判断的方式储存数据的。
数据块开销
行格式
- 行片段：是由行头和列的数据 组成；
- 行头则是由：行的内容，列数，id号，rowid 号，列的长度，列的值
3：数据块压缩
---查看数据文件名称 SYS@orcl> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl/datafile1.dbf /u01/app/oracle/oradata/orcl/ts1.dbf /u01/app/oracle/oradata/orcl/datafile3.dbf /u01/app/oracle/oradata/orcl/datafile4.dbf /u01/app/oracle/oradata/orcl/datafile5.dbf /u01/app/oracle/oradata/orcl/datafilebig_1.dfb /u01/app/oracle/oradata/orcl/ts2.dbf /u01/app/oracle/oradata/orcl/ts3.dbf /u01/app/oracle/oradata/orcl/tbsp_1.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/a1.dbf 15 rows selected. ---压缩表空间 SYS@orcl> create tablespace compress_01 datafile '/u01/app/oracle/oradata/orcl/compress_01.dbf' size 20m default compress for oltp; Tablespace created. SYS@orcl>
- 如果一个表空间有压缩方式及该表空间下的表也有压缩方式，则该表优先使用表的压缩方式进行数据压缩。
- 如果一个表空间有压缩方式而该表空间下的表没有创建压缩方式，则该表的数据则以表空间的压缩方式进行压缩数据。
- 如果一个表空间没有创建压缩方式，而该表空间下的表创建了压缩方式，则该表的数据以表的压缩方式进行数据的压缩。
---创建一个大文件的压缩表空间 SYS@orcl> create bigfile tablespace compress_02 datafile '/u01/app/oracle/oradata/orcl/compress_02.dbf' size 20m default compress for oltp; Tablespace created. ---创建一个普通表空间，然后修改普通表空间为压缩表空间 SYS@orcl> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test_01.dbf' size 20m; Tablespace created. ---修改普通表空间为压缩表空间 SYS@orcl> alter tablespace test default compress for oltp; Tablespace altered.----查询表空间压缩新SYS@orcl> select tablespace_name ,compress_for from dba_tablespaces; TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE TBS_TEST1 TBS_TEST_3 TBS_TEST4 TBS_TEST5 TBS_BIG_1 TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TEMP_1 TBS_EXAMPLE TEMP_01 TP1 TP2 TS_1 TS_2 TS_3 TBSP_1 MY COMPRESS_01 OLTP --压缩表空间 TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ COMPRESS_02 OLTP --压缩表空间 TEST OLTP --压缩表空间 24 rows selected. ----取消表空间的压缩 SYS@orcl> alter tablespace test default nocompress; Tablespace altered. --再次查询 SYS@orcl> select tablespace_name ,compress_for from dba_tablespaces; TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE TBS_TEST1 TBS_TEST_3 TBS_TEST4 TBS_TEST5 TBS_BIG_1 TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TEMP_1 TBS_EXAMPLE TEMP_01 TP1 TP2 TS_1 TS_2 TS_3 TBSP_1 MY COMPRESS_01 OLTP TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ COMPRESS_02 OLTP TEST 24 rows selected.
SYS@orcl> create tablespace test2 datafile '/u01/app/oracle/oradata/orcl/test02.dbf' size 20m autoextend on next 10m extent management local uniform size 1m; Tablespace created. ----在 compress_01 表空间里创建表 --1:在压缩表空间里创建 oltp 压缩表 SYS@orcl> create table t4 compress for oltp tablespace COMPRESS_01 as select * from scott.emp; Table created. ---在压缩表空间里创建非压缩表 SYS@orcl> create table t5 tablespace compress_01 as select * from scott.emp; Table created. ---对压缩表和非压缩表进行分析 SYS@orcl> analyze table t5 compute statistics; Table analyzed. SYS@orcl> analyze table t4 compute statistics; Table analyzed. ----结论：在 iltp压缩表空间里创建表 如果不指定默认的压缩方式，默认是按照表空间的压缩方式oltp方式来进行压缩 SYS@orcl> select table_name ,compress_for from dba_tables where table_name in ('T4','T5'); TABLE_NAME COMPRESS_FOR ------------------------------ ------------ T5 OLTP T4 OLTP ---创建表 SYS@orcl> create table t6 compress tablespace compress_01 as select * from scott.emp; Table created. ---结论：如果在oltp压缩表空间内创建表的时候，指定压缩方式为 basic compress ，那么创建的表是按照 basic compression 压缩 说明表级别的压缩方式会覆盖表空间级别的压缩方式 SYS@orcl> select table_name ,compress_for from dba_tables where table_name in ('T4','T5','T6'); TABLE_NAME COMPRESS_FOR ------------------------------ ------------ T5 OLTP T4 OLTP T6 BASIC ----压缩索引 SYS@orcl> create index ind_t4 on t4(empno,ename) tablespace compress_01; Index created. ---结论：虽然我们是在 压缩表空间里的创建索引，但是索引仍然是 disabled 没有压缩，所以如果要压缩索引，必须手动指明 comress SYS@orcl> create index ind_t5 on t5(empno,ename) tablespace compress_01; Index created. SYS@orcl> set linesize 500 ; SYS@orcl> select index_name ,compression,leaf_blocks,tablespace_name from user_indexes where table_name in ('T4','T5'); INDEX_NAME COMPRESS LEAF_BLOCKS TABLESPACE_NAME ------------------------------ -------- ----------- ------------------------------ IND_T5 DISABLED 1 COMPRESS_01 IND_T4 DISABLED 1 COMPRESS_01 SYS@orcl>
4：数据块的空间管理
数据块中的可用空间百分比
优化数据块中的可用空间
合并碎片空间
行链接和行迁移
创建居多大的非标准数据库来处理行链接的问题。
三：扩展区概述
1：分配扩展区
SYS@orcl> set linesize 50; SYS@orcl> desc dba_extents; Name Null? Type ----------------------- -------- ---------------- OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) EXTENT_ID NUMBER FILE_ID NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER RELATIVE_FNO NUMBER SYS@orcl> create table tt2(id int); Table created. SYS@orcl> set linesize 200; SYS@orcl> select SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,BLOCKS from dba_extents where SEGMENT_NAME='TT2'; SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCKS --------------------------------------------------------------------------------- ------------------------------ ---------- ---------- TT2 SYSTEM 0 8 SYS@orcl>
SYS@orcl> desc dba_segments; Name Null? Type ----------------------- -------- ---------------- OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) SEGMENT_SUBTYPE VARCHAR2(10) TABLESPACE_NAME VARCHAR2(30) HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER MAX_SIZE NUMBER RETENTION VARCHAR2(7) MINRETENTION NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL VARCHAR2(7) FLASH_CACHE VARCHAR2(7) CELL_FLASH_CACHE VARCHAR2(7) SYS@orcl> create table tt3 as select * from dba_users; Table created. SYS@orcl> select extents from dba_segments where segment_name='TT3'; EXTENTS ---------- 1 SYS@orcl>
2: 释放扩展区
3：扩展区的存储参数
四： 段概述
1：用户段
创建用户段
2：临时段
为查询分配临时段
为临时表及其索引分配临时段
3：撤销段
撤销段和事务
事务回滚
4：段空间和高水位标记
降低 高水位线(即：清空高水位线：删除表如何在创建表、压缩表)
高水位线 默认情况下是不会下降。只能手动进行下降操作。
---创建表 SYS@orcl> create table test3 as select * from dba_objects where 1 = 2; Table created. ---查看表数据 SYS@orcl> select * from test3; no rows selected ---查看表中分配块，区大小 ：查看表分区和数据块信息： SYS@orcl> SELECT segment_name, segment_type,blocks, extents FROM dba_segments WHERE segment_name = 'TEST3'; SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS --------------------------------------------------------------------------------- ------------------ ---------- ---------- TEST3 TABLE 8 1---：从结果得知： 此时表没有数据。分配了1个区 默认数据块大小为8个。此时数据块高水位线为0--blocks -- 分配数据块数 --extents -- 分配区块数 --3、分析表TEST3表 SYS@orcl> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; Table analyzed. --目的是：得到该表的一些更准确的信息--查询TEST3表高水位线 SYS@orcl> SELECT blocks,empty_blocks,num_rows FROM user_tables WHERE table_name = 'TEST3'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 0 7 0 ---从结果来看：此时分配的是0个数据块，是因为此时该表还没有数据信息 ，此时有7个数据块是可以用的e.有1个数据块默认作为数据块的头部信息了。
---此时向该表插入数据 SYS@orcl> insert into test3 select * from dba_objects; 75301 rows created. SYS@orcl> commit; Commit complete. SYS@orcl>
--6、重新分析表 、 SYS@orcl> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; Table analyzed. SYS@orcl>
---7、再次查看表中分配块，区大小SYS@orcl> SELECT blocks,empty_blocks,num_rows FROM user_tables WHERE table_name = 'TEST3';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
1068 83 75280----此时：该表的搞水位线值为：1068
---删除数据 SYS@orcl> delete from test3; 75301 rows deleted. SYS@orcl> commit ; SYS@orcl> select * from test3; no rows selected
----8:在对表进行分析。在查看表的高水位线值。 SYS@orcl> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; Table analyzed. SYS@orcl> SELECT blocks,empty_blocks,num_rows FROM user_tables WHERE table_name = 'TEST3'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 1068 83 0 ---从查询结果来看：表的高水位线是不会随着表数据量的减少而减少。只会无线增大并且保持最大值。清空表高水位线方式一： truncate ：
SYS@orcl> truncate table test3; Table truncated. SYS@orcl> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; Table analyzed. SYS@orcl> SELECT blocks,empty_blocks,num_rows FROM user_tables WHERE table_name = 'TEST3'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 0 7 0 SYS@orcl>------衍生出来的另外一种清空表高水位线的操作方式：
若想保留所有的数据：则需要先把表中的数据全部导出后，然后在 truncate 表 ，接着再把导出的所有数据导入到表中。
清空表高水位线方式二：表的压缩操作
---查看数据文件路径信息 SYS@orcl> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl/datafile1.dbf /u01/app/oracle/oradata/orcl/ts1.dbf /u01/app/oracle/oradata/orcl/datafile3.dbf /u01/app/oracle/oradata/orcl/datafile4.dbf /u01/app/oracle/oradata/orcl/datafile5.dbf /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/ts2.dbf /u01/app/oracle/oradata/orcl/ts3.dbf /u01/app/oracle/oradata/orcl/tbsp_1.dbf /u01/app/oracle/oradata/orcl/compress_01.dbf /u01/app/oracle/oradata/orcl/compress_02.dbf /u01/app/oracle/oradata/orcl/test_01.dbf /u01/app/oracle/oradata/orcl/test02.dbf 18 rows selected. ---创建表空间 SYS@orcl> CREATE TABLESPACE mytbs8 DATAFILE '/u01/app/oracle/oradata/orcl/mytbs08.dbf' size 20m SEGMENT SPACE MANAGEMENT AUTO; Tablespace created. ---创建表 SYS@orcl> create table test2 tablespace mytbs8 as select * from dba_objects where 1 = 2; --插入数据 insert into test2 select * from dba_objects; ---分析表 ANALYZE TABLE TEST2 ESTIMATE STATISTICS; Table created. SYS@orcl> 75302 rows created. SYS@orcl> SYS@orcl> Table analyzed. ---查看表的高水位线 SYS@orcl> SYS@orcl> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TEST2'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 1126 26 77725 ----删除100条数据 SYS@orcl> delete from test2 where rownum<10000; 9999 rows deleted. SYS@orcl> commit; Commit complete. ---分析表 SYS@orcl> ANALYZE TABLE TEST2 ESTIMATE STATISTICS; Table analyzed. ---查看表的高水位线 SYS@orcl> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TEST2'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 1126 26 65960 ---允许表进行行迁移 SYS@orcl> alter table test2 enable row movement; Table altered. --对表进行收缩 SYS@orcl> alter table test2 shrink space cascade; Table altered. ---分析表 SYS@orcl> ANALYZE TABLE TEST2 ESTIMATE STATISTICS; Table analyzed. ---查看表的高水位线 SYS@orcl> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TEST2'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 938 30 65299 SYS@orcl>
高水位线的影响：在做全表扫描的时候，会从高水位线的位置向下查询表的数据。如果高水位线向下有很大比例的空位置，则很大程度会影响表查询性能。
五：表空间概述
- 表空间是逻辑概念
- 物理文件是：数据文件和临时数据文件
- 表空间与数据文件的对应关系是：一对一或者一对多的对应关系、
- 表空间分为 永久表空间和临时表空间
- 永久表空间意味着数据是永久存放的。临时表空间意味着表空间的数据是临时存放的。
1：永久表空间
SYSTEM 表空间
SYSAUX(辅助) 表空间
UNDO (撤销)表空间
自动撤销管理模式
自动撤销保留
---查看表空间 SYS@orcl> select name from v$tablespace; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP EXAMPLE TBS_TEST1 TEMP_01 TBS_TEST_3 TBS_TEST4 TBS_TEST5 NAME ------------------------------ TBS_BIG_1 TEMP_1 TBS_EXAMPLE TP1 TP2 TS_1 TS_2 TS_3 TBSP_1 COMPRESS_01 COMPRESS_02 NAME ------------------------------ TEST TEST2 MYTBS8 25 rows selected. ---删除表空间 SYS@orcl> drop tablespace ts_3; Tablespace dropped. --删除表空间及其数据文件及其内容SYS@orcl> drop tablespace mytbs8 including contents and datafiles; Tablespace dropped. SYS@orcl>
2：临时表空间
3：表空间模式
读/写和只读表空间
读/写 模式
只读模式
联机和脱机表空间
4：表空间文件大小
5：总结
6：实验
1：查看表空间的段的管理方式，区的分配
SYS@orcl> select SEGMENT_SPACE_MANAGEMENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE,TABLESPACE_NAME from dba_tablespaces ; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ MANUAL LOCAL SYSTEM SYSTEM AUTO LOCAL SYSTEM SYSAUX MANUAL LOCAL SYSTEM UNDOTBS1 MANUAL LOCAL UNIFORM TEMP AUTO LOCAL SYSTEM USERS AUTO LOCAL SYSTEM EXAMPLE AUTO LOCAL UNIFORM TBS_TEST1 MANUAL LOCAL SYSTEM TBS_TEST_3 AUTO LOCAL SYSTEM TBS_TEST4 AUTO LOCAL SYSTEM TBS_TEST5 AUTO LOCAL SYSTEM TBS_BIG_1 SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ MANUAL LOCAL UNIFORM TEMP_1 MANUAL LOCAL UNIFORM TBS_EXAMPLE MANUAL LOCAL UNIFORM TEMP_01 MANUAL LOCAL UNIFORM TP1 MANUAL LOCAL UNIFORM TP2 AUTO LOCAL SYSTEM TS_1 AUTO LOCAL SYSTEM TS_2 AUTO LOCAL SYSTEM TBSP_1 AUTO LOCAL SYSTEM COMPRESS_01 AUTO LOCAL SYSTEM COMPRESS_02 AUTO LOCAL SYSTEM TEST SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ AUTO LOCAL UNIFORM TEST2 23 rows selected. SYS@orcl>
2：查看表空间的自动扩展
SYS@orcl> select TABLESPACE_NAME ,AUTOEXTENSIBLE from dba_data_files ; TABLESPACE_NAME AUTOEXTENSIBLE ------------------------------ -------------- USERS YES UNDOTBS1 YES SYSAUX YES SYSTEM YES EXAMPLE YES TBS_TEST1 NO TBS_TEST_3 YES TBS_TEST4 NO TBS_TEST5 YES TBS_BIG_1 NO TS_1 NO TABLESPACE_NAME AUT ------------------------------ -------------- TS_2 NO TBSP_1 YES COMPRESS_01 NO COMPRESS_02 NO TEST NO TEST2 YES 17 rows selected.
3：查看数据库的特性
SYS@orcl> col PROPERTY_NAME for a20; SYS@orcl> col PROPERTY_NAME for a20; SYS@orcl> col DESCRIPTION for a100; SYS@orcl> set linesize 300; SYS@orcl> select * from database_properties; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION -------------------- -------------------- ---------------------------------------------------------------------------------------------------- DICT.BASE 2 dictionary base tables version # DEFAULT_TEMP_TABLESP GROUP1 Name of default temporary tablespace ACE DEFAULT_PERMANENT_TA TBS_TEST1 Name of default permanent tablespace BLESPACE DEFAULT_EDITION ORA$BASE Name of the database default edition Flashback Timestamp GMT Flashback timestamp created in GMT TimeZone PROPERTY_NAME PROPERTY_VALUE DESCRIPTION -------------------- -------------------- ---------------------------------------------------------------------------------------------------- TDE_MASTER_KEY_ID DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade DST_PRIMARY_TT_VERSI 14 Version of primary timezone data file ON DST_SECONDARY_TT_VER 0 Version of secondary timezone data file SION DEFAULT_TBS_TYPE SMALLFILE Default tablespace type NLS_LANGUAGE AMERICAN Language NLS_TERRITORY AMERICA Territory PROPERTY_NAME PROPERTY_VALUE DESCRIPTION -------------------- -------------------- ---------------------------------------------------------------------------------------------------- NLS_CURRENCY $ Local currency NLS_ISO_CURRENCY AMERICA ISO currency NLS_NUMERIC_CHARACTE ., Numeric characters RS NLS_CHARACTERSET ZHS16GBK Character set NLS_CALENDAR GREGORIAN Calendar system NLS_DATE_FORMAT DD-MON-RR Date format NLS_DATE_LANGUAGE AMERICAN Date language NLS_SORT BINARY Linguistic definition NLS_TIME_FORMAT HH.MI.SSXFF AM Time format PROPERTY_NAME PROPERTY_VALUE DESCRIPTION -------------------- -------------------- ---------------------------------------------------------------------------------------------------- NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXF Time stamp format F AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format NLS_TIMESTAMP_TZ_FOR DD-MON-RR HH.MI.SSXF Timestamp with timezone format MAT F AM TZR NLS_DUAL_CURRENCY $ Dual currency symbol NLS_COMP BINARY NLS comparison NLS_LENGTH_SEMANTICS BYTE NLS length semantics NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception PROPERTY_NAME PROPERTY_VALUE DESCRIPTION -------------------- -------------------- ---------------------------------------------------------------------------------------------------- NLS_NCHAR_CHARACTERS AL16UTF16 NCHAR Character set ET NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters GLOBAL_DB_NAME ORCL Global database name EXPORT_VIEWS_VERSION 8 Export views revision # WORKLOAD_CAPTURE_MOD CAPTURE implies workload capture is in progress E WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress NO_USERID_VERIFIER_S 6396C58AB17AE007E9A7 PROPERTY_NAME PROPERTY_VALUE DESCRIPTION -------------------- -------------------- ---------------------------------------------------------------------------------------------------- ALT 289C0FE59274 DBTIMEZONE 00:00 DB time zone 36 rows selected. SYS@orcl>
创建一个新的临时表空间tem；并设置为默认表空间；然后在删除该默认表空间tem。在修改默认表空间为 temp ；在删除新创建的tem表空间
SYS@orcl> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl/datafile1.dbf /u01/app/oracle/oradata/orcl/ts1.dbf /u01/app/oracle/oradata/orcl/datafile3.dbf /u01/app/oracle/oradata/orcl/datafile4.dbf /u01/app/oracle/oradata/orcl/datafile5.dbf /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NAME ---------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/ts2.dbf /u01/app/oracle/oradata/orcl/tbsp_1.dbf /u01/app/oracle/oradata/orcl/compress_01.dbf /u01/app/oracle/oradata/orcl/compress_02.dbf /u01/app/oracle/oradata/orcl/test_01.dbf /u01/app/oracle/oradata/orcl/test02.dbf 17 rows selected. SYS@orcl> create temporary tablespace tem tempfile 'tem.dbf' size 20m ; Tablespace created. SYS@orcl> alter database default temporary tablespace tem; Database altered. SYS@orcl> SELECT name FROM V$TABLESPACE; NAME ---------------------------------------------------------------------------------------------------- SYSTEM SYSAUX UNDOTBS1 USERS TEMP EXAMPLE TBS_TEST1 TEMP_01 TBS_TEST_3 TBS_TEST4 TBS_TEST5 NAME ---------------------------------------------------------------------------------------------------- TBS_BIG_1 TEMP_1 TBS_EXAMPLE TP1 TP2 TS_1 TS_2 TEM TBSP_1 COMPRESS_01 COMPRESS_02 NAME ---------------------------------------------------------------------------------------------------- TEST TEST2 24 rows selected. SYS@orcl> drop tablespace tem; drop tablespace tem * ERROR at line 1: ORA-12906: cannot drop default temporary tablespace SYS@orcl> alter database default temporary tablespace temp; Database altered.
SYS@orcl> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl> startup ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 381683196 bytes Database Buffers 134217728 bytes Redo Buffers 5861376 bytes Database mounted. Database opened. SYS@orcl> drop tablespace tem; Tablespace dropped. SYS@orcl> select name from v$tablespace; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP EXAMPLE TBS_TEST1 TEMP_01 TBS_TEST_3 TBS_TEST4 TBS_TEST5 NAME ------------------------------ TBS_BIG_1 TEMP_1 TBS_EXAMPLE TP1 TP2 TS_1 TS_2 TBSP_1 COMPRESS_01 COMPRESS_02 TEST NAME ------------------------------ TEST2 23 rows selected. SYS@orcl>
4：查看临时表空间组
SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 SYS@orcl>
5：查询数据库包含的表空间信息
SYS@orcl> col name for a30; SYS@orcl> SELECT name FROM V$TABLESPACE; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP EXAMPLE TBS_TEST1 TEMP_01 TBS_TEST_3 TBS_TEST4 TBS_TEST5 NAME ------------------------------ TBS_BIG_1 TEMP_1 TBS_EXAMPLE TP1 TP2 TS_1 TS_2 TBSP_1 COMPRESS_01 COMPRESS_02 TEST NAME ------------------------------ TEST2 23 rows selected. SYS@orcl>
6：查询表空间及数据文件的信息
SYS@orcl> col FILE_NAME for a70; SYS@orcl> col TABLESPACE_NAME for a20; SYS@orcl> SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES; FILE_NAME TABLESPACE_NAME ---------------------------------------------------------------------- -------------------- /u01/app/oracle/oradata/orcl/users01.dbf USERS /u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM /u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE /u01/app/oracle/oradata/orcl/datafile1.dbf TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile3.dbf TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile4.dbf TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile5.dbf TBS_TEST5 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb TBS_BIG_1 /u01/app/oracle/oradata/orcl/ts1.dbf TS_1 FILE_NAME TABLESPACE_NAME ---------------------------------------------------------------------- -------------------- /u01/app/oracle/oradata/orcl/ts2.dbf TS_2 /u01/app/oracle/oradata/orcl/tbsp_1.dbf TBSP_1 /u01/app/oracle/oradata/orcl/compress_01.dbf COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_02.dbf COMPRESS_02 /u01/app/oracle/oradata/orcl/test_01.dbf TEST /u01/app/oracle/oradata/orcl/test02.dbf TEST2 17 rows selected. SYS@orcl>
7：查询DBA_DATA_FILES 视图以确定是否启用AUTOEXTEND
SYS@orcl> SELECT FILE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES; FILE_NAME AUT ---------------------------------------------------------------------- --- /u01/app/oracle/oradata/orcl/users01.dbf YES /u01/app/oracle/oradata/orcl/undotbs01.dbf YES /u01/app/oracle/oradata/orcl/sysaux01.dbf YES /u01/app/oracle/oradata/orcl/system01.dbf YES /u01/app/oracle/oradata/orcl/example01.dbf YES /u01/app/oracle/oradata/orcl/datafile1.dbf NO /u01/app/oracle/oradata/orcl/datafile3.dbf YES /u01/app/oracle/oradata/orcl/datafile4.dbf NO /u01/app/oracle/oradata/orcl/datafile5.dbf YES /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO /u01/app/oracle/oradata/orcl/ts1.dbf NO FILE_NAME AUT ---------------------------------------------------------------------- --- /u01/app/oracle/oradata/orcl/ts2.dbf NO /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES /u01/app/oracle/oradata/orcl/compress_01.dbf NO /u01/app/oracle/oradata/orcl/compress_02.dbf NO /u01/app/oracle/oradata/orcl/test_01.dbf NO /u01/app/oracle/oradata/orcl/test02.dbf YES 17 rows selected.
8：查询数据文件的基本信息
SYS@orcl> col name for a100; SYS@orcl> SELECT name,file#,status,bytes,checkpoint_change# last_scn FROM v$datafile; NAME FILE# STATUS BYTES LAST_SCN ---------------------------------------------------------------------------------------------------- ---------- ------- ---------- ---------- /u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM 786432000 6645207 /u01/app/oracle/oradata/orcl/sysaux01.dbf 2 ONLINE 891289600 6645207 /u01/app/oracle/oradata/orcl/undotbs01.dbf 3 ONLINE 256901120 6645207 /u01/app/oracle/oradata/orcl/users01.dbf 4 ONLINE 5242880 6645207 /u01/app/oracle/oradata/orcl/example01.dbf 5 ONLINE 362414080 6645207 /u01/app/oracle/oradata/orcl/datafile1.dbf 6 ONLINE 10485760 6645207 /u01/app/oracle/oradata/orcl/ts1.dbf 7 ONLINE 10485760 6645207 /u01/app/oracle/oradata/orcl/datafile3.dbf 8 ONLINE 104857600 6645207 /u01/app/oracle/oradata/orcl/datafile4.dbf 9 ONLINE 20971520 6645207 /u01/app/oracle/oradata/orcl/datafile5.dbf 10 ONLINE 62914560 6645207 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb 11 ONLINE 1073741824 6645207 NAME FILE# STATUS BYTES LAST_SCN ---------------------------------------------------------------------------------------------------- ---------- ------- ---------- ---------- /u01/app/oracle/oradata/orcl/ts2.dbf 12 ONLINE 10485760 6645207 /u01/app/oracle/oradata/orcl/tbsp_1.dbf 14 ONLINE 20971520 6645207 /u01/app/oracle/oradata/orcl/compress_01.dbf 15 ONLINE 20971520 6645207 /u01/app/oracle/oradata/orcl/compress_02.dbf 16 ONLINE 20971520 6645207 /u01/app/oracle/oradata/orcl/test_01.dbf 17 ONLINE 20971520 6645207 /u01/app/oracle/oradata/orcl/test02.dbf 18 ONLINE 20971520 6645207 17 rows selected. SYS@orcl>
9：查询表空间users 的大小
SYS@orcl> SELECT sum(bytes) FROM dba_data_files WHERE tablespace_name='USERS'; SUM(BYTES) ---------- 5242880 SYS@orcl>
10：查询scott 用户拥有的段类型和个数
SYS@orcl> SELECT SEGMENT_TYPE,COUNT(*) SEG_COUNT FROM dba_segments WHERE owner='SCOTT' GROUP BY SEGMENT_TYPE; SEGMENT_TYPE SEG_COUNT ------------------ ---------- INDEX PARTITION 2 TABLE PARTITION 1 INDEX 9 TABLE 8 SYS@orcl> SYS@orcl>
11：查询表空间的自动段空间管理
SYS@orcl> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; TABLESPACE_NAME SEGMEN -------------------- ------ SYSTEM MANUAL SYSAUX AUTO UNDOTBS1 MANUAL TEMP MANUAL USERS AUTO EXAMPLE AUTO TBS_TEST1 AUTO TBS_TEST_3 MANUAL TBS_TEST4 AUTO TBS_TEST5 AUTO TBS_BIG_1 AUTO TABLESPACE_NAME SEGMEN -------------------- ------ TEMP_1 MANUAL TBS_EXAMPLE MANUAL TEMP_01 MANUAL TP1 MANUAL TP2 MANUAL TS_1 AUTO TS_2 AUTO TBSP_1 AUTO COMPRESS_01 AUTO COMPRESS_02 AUTO TEST AUTO TABLESPACE_NAME SEGMEN -------------------- ------ TEST2 AUTO 23 rows selected.
12：通过DBA_SEGMENTS 视图，查看段的区和块的数目
查询分配给EMP 段的区和块的数目
SYS@orcl> SELECT segment_name,extents,blocks,bytes FROM dba_segments ; SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_USER1 1 8 65536 CON$ 6 48 393216 UNDO$ 1 8 65536 C_COBJ# 17 256 2097152 I_OBJ# 4 32 262144 PROXY_ROLE_DATA$ 1 8 65536 I_IND1 2 16 131072 I_CDEF2 4 32 262144 I_OBJ5 21 768 6291456 I_PROXY_ROLE_DATA$_1 1 8 65536 FILE$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_FILE#_BLOCK# 6 48 393216 I_FILE1 1 8 65536 I_CON1 9 72 589824 I_OBJ3 3 24 196608 I_TS# 1 8 65536 I_CDEF4 5 40 327680 C_TS# 5 40 327680 I_FILE2 1 8 65536 I_TS1 1 8 65536 I_UNDO2 1 8 65536 PROXY_DATA$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_USER2 1 8 65536 I_PROXY_DATA$ 1 8 65536 I_OBJ1 18 384 3145728 I_COL2 18 384 3145728 I_OBJ2 21 768 6291456 I_OBJ4 18 384 3145728 I_CCOL1 5 40 327680 C_FILE#_BLOCK# 17 256 2097152 C_USER# 1 8 65536 I_UNDO1 1 8 65536 BOOTSTRAP$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_TAB1 1 8 65536 I_CON2 3 24 196608 I_COBJ# 2 16 131072 OBJ$ 23 1024 8388608 I_COL3 17 256 2097152 C_OBJ# 27 1536 12582912 I_CDEF1 3 24 196608 I_ICOL1 3 24 196608 I_CDEF3 1 8 65536 I_CCOL2 5 40 327680 I_COL1 20 640 5242880 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_USER# 1 8 65536 I_PROXY_ROLE_DATA$_2 1 8 65536 OBJERROR$ 1 8 65536 OBJAUTH$ 14 112 917504 I_OBJAUTH1 17 256 2097152 I_OBJAUTH2 17 256 2097152 UGROUP$ 1 8 65536 I_UGROUP1 1 8 65536 I_UGROUP2 1 8 65536 SYN$ 17 256 2097152 VIEW$ 22 896 7340032 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- TYPED_VIEW$ 1 8 65536 SUPEROBJ$ 1 8 65536 I_SUPEROBJ1 1 8 65536 I_SUPEROBJ2 1 8 65536 SEQ$ 1 8 65536 I_VIEW1 2 16 131072 I_TYPED_VIEW1 1 8 65536 I_SYN1 7 56 458752 I_SYN2 17 256 2097152 I_SEQ1 1 8 65536 I_LOB1 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_LOB2 1 8 65536 I_COLTYPE1 2 16 131072 I_COLTYPE2 2 16 131072 I_SUBCOLTYPE1 1 8 65536 I_NTAB1 1 8 65536 I_NTAB2 1 8 65536 I_NTAB3 1 8 65536 I_REFCON1 1 8 65536 I_REFCON2 1 8 65536 I_OPQTYPE1 1 8 65536 PROPS$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- EDITION$ 1 8 65536 FIXED_OBJ$ 1 8 65536 I_FIXED_OBJ$_OBJ# 1 8 65536 MIGRATE$ 1 8 65536 DEPENDENCY$ 22 896 7340032 ACCESS$ 19 512 4194304 I_DEPENDENCY1 21 768 6291456 I_DEPENDENCY2 21 768 6291456 I_ACCESS1 19 512 4194304 VIEWCON$ 1 8 65536 SYS_IL0000000109C00005$$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- SYS_LOB0000000109C00005$$ 1 8 65536 I_VIEWCON1 1 8 65536 I_VIEWCON2 1 8 65536 I_ICOLDEP$_OBJ 1 8 65536 DUAL 1 8 65536 SYSAUTH$ 1 8 65536 OBJPRIV$ 1 8 65536 DEFROLE$ 1 8 65536 I_SYSAUTH1 1 8 65536 I_DEFROLE1 1 8 65536 ECOL$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- SYS_IL0000000123C00003$$ 1 8 65536 SYS_LOB0000000123C00003$$ 1 8 65536 ECOL_IX1 1 8 65536 EV$ 1 8 65536 I_EV1 1 8 65536 I_EV2 1 8 65536 EVCOL$ 1 8 65536 I_EVCOL1 1 8 65536 DEFERRED_STG$ 3 24 196608 I_DEFERRED_STG1 2 16 131072 SQLERROR$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- LINK$ 1 8 65536 TRUSTED_LIST$ 1 8 65536 COM$ 17 256 2097152 I_LINK1 1 8 65536 I_COM1 8 64 524288 DUC$ 1 8 65536 I_DUC 1 8 65536 RECYCLEBIN$ 1 8 65536 RECYCLEBIN$_OBJ 1 8 65536 RECYCLEBIN$_TS 1 8 65536 RECYCLEBIN$_OWNER 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- IND_ONLINE$ 1 8 65536 CONTEXT$ 1 8 65536 I_CONTEXT 1 8 65536 SQL_VERSION$ 1 8 65536 I_SQL_VERSION$_VERSION# 1 8 65536 JIJOIN$ 1 8 65536 I_JIJOIN$ 1 8 65536 I2_JIJOIN$ 1 8 65536 I3_JIJOIN$ 1 8 65536 JIREFRESHSQL$ 1 8 65536 SYS_IL0000000155C00003$$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- SYS_LOB0000000155C00003$$ 1 8 65536 I1_JIREFRESHSQL$ 1 8 65536 I2_JIREFRESHSQL$ 1 8 65536 TRIGGER$ 13 104 851968 TRIGGERCOL$ 2 16 131072 I_TRIGGER1 1 8 65536 I_TRIGGER2 1 8 65536 I_TRIGGERCOL1 3 24 196608 I_TRIGGERCOL2 3 24 196608 TRIGGERJAVAF$ 1 8 65536 TRIGGERJAVAS$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- TRIGGERJAVAC$ 1 8 65536 TRIGGERJAVAM$ 1 8 65536 I_TRIGGERJAVAF 1 8 65536 I_TRIGGERJAVAS 1 8 65536 I_TRIGGERJAVAC 1 8 65536 I_TRIGGERJAVAM 1 8 65536 I_VIEWTRCOL1 1 8 65536 TRIGGERDEP$ 1 8 65536 TRIGGERDEPIND$ 1 8 65536 INDREBUILD$ 1 8 65536 I_INDREBUILD1 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- COMPRESSION$ 1 8 65536 SYS_IL0000000182C00017$$ 1 8 65536 SYS_LOB0000000182C00017$$ 1 8 65536 I_COMPRESSION1 1 8 65536 TRANSIENT_IOT$ 1 8 65536 SQLLOG$_PKEY 1 8 65536 SMB$CONFIG 1 8 65536 I_SMB$CONFIG_PKEY 1 8 65536 SQL$ 1 8 65536 SYS_IL0000000192C00005$$ 1 8 65536 SYS_LOB0000000192C00005$$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_SQL$_PKEY 1 8 65536 SQL$TEXT 1 8 65536 SYS_IL0000000196C00005$$ 1 8 65536 SYS_LOB0000000196C00005$$ 1 8 65536 SYS_IL0000000196C00003$$ 1 8 65536 SYS_LOB0000000196C00003$$ 1 8 65536 I_SQL$TEXT_PKEY 1 8 65536 I_SQL$TEXT_HANDLE 1 8 65536 SQLOBJ$_PKEY 1 8 65536 SYS_IL0000000203C00009$$ 1 8 65536 SYS_LOB0000000203C00009$$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_SQLOBJ$NAME_TYPE 1 8 65536 SQLOBJ$DATA_PKEY 1 8 65536 SYS_IL0000000208C00007$$ 1 8 65536 SYS_LOB0000000208C00007$$ 1 8 65536 SYS_IL0000000208C00005$$ 1 8 65536 SYS_LOB0000000208C00005$$ 1 8 65536 SQLOBJ$AUXDATA 1 8 65536 SYS_IL0000000214C00036$$ 1 8 65536 SYS_LOB0000000214C00036$$ 1 8 65536 I_SQLOBJ$AUXDATA_PKEY 1 8 65536 I_SQLOBJ$AUXDATA_TASK 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- OBJECT_USAGE 1 8 65536 I_STATS_OBJ# 1 8 65536 PROCEDURE$ 4 32 262144 PROCEDUREINFO$ 17 256 2097152 ARGUMENT$ 27 1536 12582912 SOURCE$ 80 9216 75497472 IDL_UB1$ 102 31744 260046848 IDL_CHAR$ 25 1280 10485760 IDL_UB2$ 46 3968 32505856 IDL_SB4$ 20 640 5242880 ERROR$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- SETTINGS$ 18 384 3145728 I_PROCEDURE1 2 16 131072 I_PROCEDUREINFO1 17 256 2097152 I_ARGUMENT1 24 1152 9437184 I_ARGUMENT2 21 768 6291456 I_SOURCE1 27 1536 12582912 I_IDL_UB11 17 256 2097152 I_IDL_CHAR1 4 32 262144 I_IDL_UB21 7 56 458752 I_IDL_SB41 9 72 589824 I_ERROR1 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_SETTINGS1 17 256 2097152 NCOMP_DLL$ 1 8 65536 SYS_IL0000000242C00003$$ 1 8 65536 NATIVE_COMP_SHARED_OBJECTS 1 128 1048576 I_NCOMP_DLL1 1 8 65536 PROCEDUREJAVA$ 5 40 327680 I_PROCEDUREJAVA$ 1 8 65536 PROCEDUREC$ 1 8 65536 I_PROCEDUREC$ 1 8 65536 PROCEDUREPLSQL$ 6 48 393216 I_PROCEDUREPLSQL$ 6 48 393216 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- WARNING_SETTINGS$ 3 24 196608 I_WARNING_SETTINGS 4 32 262144 DIANA_VERSION$ 1 8 65536 I_DIANA_VERSION 1 8 65536 PLSCOPE_IDENTIFIER$ 3 24 196608 I_PLSCOPE_SIG_IDENTIFIER$ 3 24 196608 I_PLSCOPE_OBJ_IDENTIFIER$ 2 16 131072 PLSCOPE_ACTION$ 7 56 458752 I_PLSCOPE_SIG_ACTION$ 7 56 458752 I_PLSCOPE_OBJ_ACTION$ 3 24 196608 PENDING_TRANS$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_PENDING_TRANS1 1 8 65536 PENDING_SESSIONS$ 1 8 65536 PENDING_SUB_SESSIONS$ 1 8 65536 SMON_SCN_TO_TIME_AUX 18 384 3145728 SMON_SCN_TO_TIME_AUX_IDX 1 8 65536 SMON_SCN_TIME_TIM_IDX 2 16 131072 SMON_SCN_TIME_SCN_IDX 2 16 131072 MAP_FILE$ 1 8 65536 MAP_FILE_EXTENT$ 1 8 65536 MAP_SUBELEMENT$ 1 8 65536 MAP_ELEMENT$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- MAP_EXTELEMENT$ 1 8 65536 MAP_COMPLIST$ 1 8 65536 PROFILE$ 1 8 65536 PROFNAME$ 1 8 65536 RESOURCE_COST$ 1 8 65536 I_PROFNAME 1 8 65536 I_PROFILE 1 8 65536 JOB$ 1 8 65536 I_JOB_JOB 1 8 65536 I_JOB_NEXT 1 8 65536 RESOURCE_MAP 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- USER_ASTATUS_MAP 1 8 65536 RESOURCE_PLAN$ 1 8 65536 RESOURCE_CONSUMER_GROUP$ 1 8 65536 RESOURCE_CATEGORY$ 1 8 65536 RESOURCE_PLAN_DIRECTIVE$ 1 8 65536 RESOURCE_GROUP_MAPPING$ 1 8 65536 RESOURCE_MAPPING_PRIORITY$ 1 8 65536 RESOURCE_STORAGE_POOL_MAPPING$ 1 8 65536 RESOURCE_CAPABILITY$ 1 8 65536 RESOURCE_INSTANCE_CAPABILITY$ 1 8 65536 RESOURCE_IO_CALIBRATE$ 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- TSM_SRC$ 1 8 65536 I_TSM_SRC1$ 1 8 65536 I_TSM_SRC2$ 1 8 65536 TSM_DST$ 1 8 65536 I_TSM_DST1$ 1 8 65536 I_TSM_DST2$ 1 8 65536 SERVICE$ 1 8 65536 DIR$MIGRATE_OPERATIONS 1 8 65536 I_DIR$MIGRATE_UI 1 8 65536 I_DIR$MIGRATE_END_TIME 1 8 65536 I_DIR$MIGRATE_ALERT_SEQ_ID 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_DIR$MIGRATE_STATUS 1 8 65536 DIR$SERVICE_OPERATIONS 1 8 65536 I_DIR$SERVICE_UI 1 8 65536 I_DIR$SERVICE_END_TIME 1 8 65536 I_DIR$SERVICE_ALERT_SEQ_ID 1 8 65536 I_DIR$SERVICE_STATUS 1 8 65536 DIR$ESCALATE_OPERATIONS 1 8 65536 I_DIR$ESCALATE_UI 1 8 65536 I_DIR$ESCALATE_END_TIME 1 8 65536 I_DIR$ESCALATE_ALERT_SEQ_ID 1 8 65536 I_DIR$ESCALATE_STATUS 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- DIR$QUIESCE_OPERATIONS 1 8 65536 I_DIR$QUIESCE_UI 1 8 65536 I_DIR$QUIESCE_STATUS 1 8 65536 I_DIR$QUIESCE_END_TIME 1 8 65536 I_DIR$QUIESCE_ALERT_SEQ_ID 1 8 65536 DIR$INSTANCE_ACTIONS 1 8 65536 I_DIR$INSTANCE_JOB_NAME 1 8 65536 I_DIR$INSTANCE_ACTTYP 1 8 65536 I_DIR$INSTANCE_END_TIME 1 8 65536 DIR$RESONATE_OPERATIONS 1 8 65536 I_DIR$RESONATE_UI 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_DIR$RESONATE_STATUS 1 8 65536 I_DIR$RESONATE_END_TIME 1 8 65536 I_DIR$RESONATE_ALERT_NAME 1 8 65536 DIR$ALERT_HISTORY 1 8 65536 I_DIR$ALERT_HISTORY_NAME 1 8 65536 I_DIR$ALERT_HISTORY_ACTION_ID 1 8 65536 I_DIR$ALERT_HISTORY_REASON_ID 1 8 65536 I_DIR$ALERT_HISTORY_AT 1 8 65536 DIR$REASON_STRINGS 1 8 65536 I_DIR$REASON_STRINGS_UI 1 8 65536 DIR$DATABASE_ATTRIBUTES 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_DIR$DB_ATTRIBUTES_UI 1 8 65536 DIR$VICTIM_POLICY 1 8 65536 DIR$NODE_ATTRIBUTES 1 8 65536 I_DIR$NODE_ATTRIBUTES_ATTR 1 8 65536 DIR$SERVICE_ATTRIBUTES 1 8 65536 I_DIR$SERVICE_ATTRIBUTES_UI 1 8 65536 I_DIR$SERVICE_ATTRIBUTES_ATTR 1 8 65536 _default_auditing_options_ 1 8 65536 AUDIT$ 1 8 65536 I_AUDIT 1 8 65536 SYSTEM_PRIVILEGE_MAP 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_SYSTEM_PRIVILEGE_MAP 1 8 65536 TABLE_PRIVILEGE_MAP 1 8 65536 I_TABLE_PRIVILEGE_MAP 1 8 65536 STMT_AUDIT_OPTION_MAP 1 8 65536 I_STMT_AUDIT_OPTION_MAP 1 8 65536 USER_HISTORY$ 1 8 65536 RLS$ 1 8 65536 I_RLS 1 8 65536 I_RLS2 1 8 65536 RLS_SC$ 1 8 65536 I_RLS_SC 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- RLS_GRP$ 1 8 65536 I_RLS_GRP 1 8 65536 RLS_CTX$ 1 8 65536 I_RLS_CTX 1 8 65536 AUD$ 20 640 5242880 SYS_IL0000000384C00041$$ 1 8 65536 SYS_LOB0000000384C00041$$ 1 8 65536 SYS_IL0000000384C00040$$ 1 8 65536 SYS_LOB0000000384C00040$$ 1 8 65536 FGA$ 1 8 65536 I_FGA 1 8 65536 SEGMENT_NAME EXTENTS BLOCKS BYTES --------------------------------------------------------------------------------- ---------- ---------- ---------- I_FGAP 1 8 65536 FGACOL$ 1 8 65536 I_FGACOL 1 8 65536 FGA_LOG$ 1 8 65536 SYS_IL0000000394C00028$$ 1 8 65536 SYS_LOB0000000394C00028$$ 1 8 65536 SYS_IL0000000394C00013$$ 1 8 65536 SYS_LOB0000000394C00013$$ 1 8 65536 APPROLE$ 1 8 65536 I_APPROLE 1 8 65536 ENC$ 1 8 65536
13：使用DBA_EXTENTS 视图检查给定段的区的信息
SYS@orcl> SELECT extent_id,file_id,block_id,blocks FROM dba_extents WHERE owner = 'SCOTT' AND segment_name='EMP'; EXTENT_ID FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- ---------- 0 4 144 8 SYS@orcl>
14：使用DBA_FREE_SPACE 视图 查询所有表空间的自由空间
SYS@orcl> SELECT tablespace_name, count(*),max(blocks), sum(blocks) FROM dba_free_space GROUP BY tablespace_name; TABLESPACE_NAME COUNT(*) MAX(BLOCKS) SUM(BLOCKS) -------------------- ---------- ----------- ----------- TBS_TEST1 2 736 800 TS_1 1 1152 1152 COMPRESS_02 1 2432 2432 SYSAUX 156 5504 11624 UNDOTBS1 11 8704 23776 TBS_TEST_3 1 12672 12672 TBS_BIG_1 1 130944 130944 USERS 2 40 48 COMPRESS_01 1 2392 2392 TEST 1 2432 2432 SYSTEM 2 1920 1960 TABLESPACE_NAME COUNT(*) MAX(BLOCKS) SUM(BLOCKS) -------------------- ---------- ----------- ----------- EXAMPLE 3 4232 4544 TBS_TEST4 1 1408 1408 TS_2 1 1152 1152 TBS_TEST5 1 3776 3776 TEST2 1 2432 2432 TBSP_1 1 2432 2432 17 rows selected. SYS@orcl>
15：小文件表空间创建
- SMALLFILE | BIGFILE : 表示创建的是小文件表空间 还是 大文件表空间
- REUSE: 表示 若该文件存在，则清除该文件再重新建立该文件，若该文件不存在，则创建该文件。
- AUTOEXEND[ ON | OFF ]: 表示数据文件为自动扩展(ON) 或者 非自动扩展(OFF)，如果是自动扩展，则需要设置 next 的值。
- MAXSIZE: 表示当数据文件自动扩展时，允许数据文件扩展的最大长度字节数，如果指定 UNLIMITED 关键字，则不需要指定字节长度。
- MINIMUN EXTENT: 指定最小的长度，由操作系统和数据库的块决定
- ONLINE | OFFLINE: 创建表空间时可以指定为在线或者离线
- PERMANENT | TEMPORARY : 指定创建的表空间是 永久表空间或临时表空间，默认为永久性表空间。
- LOGGING | NOLOGGING : 指定该表空间内的表在加载数据时是否产生日志，默认为产生日志(LOGGING)。即使设置为NOLOGGING，但在进行 INSERT、UPDATE 和 DELETE 操作时，Oracle 仍会将操作信息记录到Redo Log Buffer 中。
- EXTENT MANAGEMENT DICTIONARY | LOCAL：指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理。Oracle不推荐使用数据字典表空间。
- AUTOALLOCATE | UNIFORM SIZE : 如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。若是按照等同大小进行，则默认每次扩展的大小为1MB.
- DEFAULT STORAGE: 指定以后要创建的表、索引及簇的储存参数值，这些参数将影响以后表等的储存参数值。
1： 创建一个一般默认属性的10M大小的表空间 tbs_01
SYS@orcl> set linesize 200; SYS@orcl> col name for a30; SYS@orcl> col datafile_name for a90; SYS@orcl> select t1.name,t2.name datafile_name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#; NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf USERS /u01/app/oracle/oradata/orcl/users01.dbf EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf TEST /u01/app/oracle/oradata/orcl/test_01.dbf TEST2 /u01/app/oracle/oradata/orcl/test02.dbf 17 rows selected. SYS@orcl> create tablespace tbs_01 datafile '/u01/app/oracle/oradata/orcl/tbs_01.dbf' size 10m; Tablespace created. SYS@orcl> select t1.name,t2.name datafile_name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#; NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf USERS /u01/app/oracle/oradata/orcl/users01.dbf EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf TEST /u01/app/oracle/oradata/orcl/test_01.dbf TEST2 /u01/app/oracle/oradata/orcl/test02.dbf 18 rows selected. SYS@orcl>
2： 创建一个含有多个数据文件的表空间 tbs_02
SYS@orcl> select t1.name,t2.name datafile_name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#; NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf USERS /u01/app/oracle/oradata/orcl/users01.dbf EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf TEST /u01/app/oracle/oradata/orcl/test_01.dbf TEST2 /u01/app/oracle/oradata/orcl/test02.dbf 18 rows selected. SYS@orcl> create tablespacetbs_02datafile '/u01/app/oracle/oradata/orcl/tbs_02_01.dbf' size 10m ,'/u01/app/oracle/oradata/orcl/tbs_02_02.dbf' size 10m; Tablespace created. SYS@orcl> select t1.name,t2.name datafile_name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbfTBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbfTBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NAME DATAFILE_NAME ------------------------------ ------------------------------------------------------------------------------------------ TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf TEST /u01/app/oracle/oradata/orcl/test_01.dbf TEST2 /u01/app/oracle/oradata/orcl/test02.dbf TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf USERS /u01/app/oracle/oradata/orcl/users01.dbf 20 rows selected. SYS@orcl>
3： 创建一个大小为10m 的自动扩展的表空间 tbs_03
SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 20 rows selected. SYS@orcl> create tablespacetbs_03datafile '/u01/app/oracle/oradata/orcl/tbs_03.dbf' size 10m autoextend on; Tablespace created. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 21 rows selected. SYS@orcl>
4： 创建一个大小为10m 的自动扩展且下一次扩展大小为2M及表空间最大值为200M 的表空间 tbs_04
SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 21 rows selected. SYS@orcl> create tablespace tbs_04 datafile '/u01/app/oracle/oradata/orcl/tbs_04.dbf' size 10 m autoextend on next 2 m maxsize 200m; Tablespace created. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 22 rows selected. SYS@orcl>
5： 创建一个大小为10m 的自动扩展且下一次扩展大小为2M及 表空间大小没有限制 的表空间 tbs_05
SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 22 rows selected. SYS@orcl> create tablespacetbs_05datafile '/u01/app/oracle/oradata/orcl/tbs_05.dbf' size 10 m autoextend on next 2mmaxsize unlimited; Tablespace created. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 23 rows selected. SYS@orcl>
--更改用户的表空间限额： --全局： grant unlimited tablespace to abc; --针对某个表空间： alter user abc quota unlimited on test; --回收： revoke unlimited tablespace from abc; alter user abc quota 0 on test;
6： 创建一个区管理是本地管理的自动扩展方式的表空间 tbs_06
SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 23 rows selected. SYS@orcl> create tablespace tbs_06 datafile '/u01/app/oracle/oradata/orcl/tbs_06.dbf' size 10 m extent management local autoallocate; Tablespace created. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 24 rows selected. SYS@orcl>
---查看表空间 的段的管理方式，区的分配 SYS@orcl> select segment_space_management,extent_management,allocation_type,tablespace_name from dba_tablespaces where tablespace_name ='TBS_06'; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ AUTO LOCAL SYSTEM TBS_06 SYS@orcl>
7： 创建一个区管理是本地管理的 扩展方式 为 extent management local uniform size 64k 的表空间 tbs_07
----oracle中表，索引，分区等都叫做段，每个段是有多个物理上不连续的区间组成；当段的空间不够是Oracle是通过增加区间来实现的。UNIFORM SIZE就是这个表空间中所有的区间是同样的大小，好处是防止碎片。 SYS@orcl> create tablespace tbs_07 datafile '/u01/app/oracle/oradata/orcl/tbs_07.dbf' size 10mextent managementlocaluniformsize64k; Tablespace created. /* 有一个initial为65M的表，当要分配新的extent时 1，如果所属的表空间为autoallocate，那么，首先，系统会在64K, 1M, 8M, and 64M找出比64M小，且最大的那个大小。这里便是64M，那么，分配完这一个extent后，还未到达指定的initial大小，继续在4个大小中找，这次符合的是1M 因此，会分配2个extent，大小分别为64M和1M 2，如果所属的表空间为uniform 20M 那么，每个extent固定是20M，要分配>=64M，则需要4个20M的extent ------------------------ 如果表空间还剩 1G的这空间未分配，此时，某个表要申请 2G ，有两种情况发生： 1. 如果表空间（数据文件）没有设置为自动增长，则直接报错，大概是 “不能分配 2G 的空间。 2.如果设置为自动增长，则自动再向 OS 申请 1G 的空间，这样就够了2G了。完成分配 2.1 如果数据文件的大小到了极限，则自动增长失败； 2.2 如果磁盘上也没有空间了，则自动增长失败； */ SYS@orcl> select segment_space_management,extent_management,allocation_type,tablespace_name from dba_tablespaces where tablespace_name ='TBS_07'; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ AUTO LOCAL UNIFORM TBS_07 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NOTBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbfNO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 25 rows selected. SYS@orcl>
8： 创建一个区管理是本地管理的 扩展方式 为 extent management local autoallocate segment space management auto 的表空间 tbs_08
/* EXTENT MANAGEMENT DICTIONARY | LOCAL：指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理。Oracle不推荐使用数据字典表空间。 AUTOALLOCATE | UNIFORM SIZE : 如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。若是按照等同大小进行，则默认每次扩展的大小为1MB. DEFAULT STORAGE: 指定以后要创建的表、索引及簇的储存参数值，这些参数将影响以后表等的储存参数值。*/ SYS@orcl> create tablespace tbs_08 datafile '/u01/app/oracle/oradata/orcl/tbs_08.dbf' size 10mextent managementlocalautoallocate segmentspacemanagement auto;Tablespace created. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 26 rows selected. SYS@orcl> select segment_space_management,extent_management,allocation_type,tablespace_name from dba_tablespaces where tablespace_name ='TBS_08'; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ AUTO LOCAL SYSTEM TBS_08 SYS@orcl>
9： 创建一个区管理是本地管理的 扩展方式 为 extent management local uniform segment space management auto; 的表空间 tbs_09
SYS@orcl> create tablespace tbs_09 datafile '/u01/app/oracle/oradata/orcl/tbs_09.dbf' size 10m extent management local uniform segment space management auto; Tablespace created. SYS@orcl> select segment_space_management,extent_management,allocation_type,tablespace_name from dba_tablespaces where tablespace_name ='TBS_09'; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ AUTO LOCAL UNIFORM TBS_09 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NOTBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbfNO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 27 rows selected. SYS@orcl>
10： 创建一个区管理是本地管理的 扩展方式 为 extent management local segment space management manual 的表空间 tbs_10
SYS@orcl> create tablespace tbs_10 datafile '/u01/app/oracle/oradata/orcl/tbs_10.dbf' size 10mextent managementlocalsegmentspacemanagement manual;Tablespace created. SYS@orcl> select segment_space_management,extent_management,allocation_type,tablespace_name from dba_tablespaces where tablespace_name ='TBS_10'; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ MANUAL LOCAL SYSTEM TBS_10 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 28 rows selected. SYS@orcl>
16： 创建大表空间
1： 创建一个 一般的大文件的表空间 tbs_10
SYS@orcl> create bigfile tablespace tbs_11 datafile '/u01/app/oracle/oradata/orcl/tbs_11.dbf' size 10m ; Tablespace created. SYS@orcl>
2：创建一个大表空间 初始化大小为10m 如果存在则自动替换，自动扩展每次扩展大小为10m 大小没有限制 在线状态 为本地自动段管理方式 的表空间 tbs_10
---创建一个大表空间 初始化大小为10m 如果存在则自动替换，自动扩展每次扩展大小为10m 大小没有限制 在线状态 为本地自动段管理方式 SYS@orcl> create bigfile tablespace tbs_12 datafile '/u01/app/oracle/oradata/orcl/tbs_12_01.dbf' size 10m reuse autoextend on next 10m maxsize unlimited online extent management local autoallocate segment space management auto; Tablespace created. SYS@orcl>
17：压缩表空间
1：：创建一个压缩default compress for oltp表空间 tbs_13
SYS@orcl> create tablespace tbs_13 datafile '/u01/app/oracle/oradata/orcl/tbs_13.dbf' size 10m defaultcompressforoltp; Tablespace created. SYS@orcl> select segment_space_management,extent_management,allocation_type,tablespace_name from dba_tablespaces where tablespace_name ='TBS_13'; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ AUTO LOCAL SYSTEM TBS_13 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 31 rows selected. SYS@orcl>
2：：创建一个大文件压缩default compress for oltp表空间 tbs_14
SYS@orcl> create bigfile tablespace tbs_14 datafile '/u01/app/oracle/oradata/orcl/tbs_14.dbf' size 10m default compress for oltp; Tablespace created. SYS@orcl> select segment_space_management,extent_management,allocation_type,tablespace_name from dba_tablespaces where tablespace_name ='TBS_14'; SEGMEN EXTENT_MAN ALLOCATIO TABLESPACE_NAME ------ ---------- --------- ------------------------------ AUTO LOCAL SYSTEM TBS_14 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_14 /u01/app/oracle/oradata/orcl/tbs_14.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO NAME DATAFILE_NAME AUT ---------------------------------------- -------------------------------------------------------------------------------- --- TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 32 rows selected. SYS@orcl>
3：：创建一个普通表空间 tbs_15 ,然后在修改为 压缩不空间
---创建一个普通表空间 SYS@orcl> create tablespace tbs_15 datafile '/u01/app/oracle/oradata/orcl/tbs_15.dbf' size 10m ; Tablespace created. ---修改为压缩表空间 SYS@orcl> alter tablespace tbs_15 default compress for oltp; Tablespace altered. ---查看表空间的压缩情况 SYS@orcl> select tablespace_name,compress_for from dba_tablespaces; TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE TBS_TEST1 TBS_TEST_3 TBS_TEST4 TBS_TEST5 TBS_BIG_1 TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TEMP_1 TBS_EXAMPLE TEMP_01 TP1 TP2 TS_1 TS_2 TBSP_1 COMPRESS_01 OLTP COMPRESS_02 OLTP TEST TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TEST2 TBS_01 TBS_02 TBS_03 TBS_04 TBS_05 TBS_06 TBS_07 TBS_08 TBS_09 TBS_10 TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TBS_11 TBS_12 TBS_13 OLTP TBS_14 OLTP TBS_15 OLTP 38 rows selected. ---取消表空间为压缩表空间 SYS@orcl> alter tablespace tbs_15 default nocompress; Tablespace altered. ---查询表空间的压缩情况 SYS@orcl> select tablespace_name,compress_for from dba_tablespaces; TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE TBS_TEST1 TBS_TEST_3 TBS_TEST4 TBS_TEST5 TBS_BIG_1 TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TEMP_1 TBS_EXAMPLE TEMP_01 TP1 TP2 TS_1 TS_2 TBSP_1 COMPRESS_01 OLTP --压缩表空间状态 COMPRESS_02 OLTP --压缩表空间状态 TEST TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TEST2 TBS_01 TBS_02 TBS_03 TBS_04 TBS_05 TBS_06 TBS_07 TBS_08 TBS_09 TBS_10 TABLESPACE_NAME COMPRESS_FOR ------------------------------ ------------ TBS_11 TBS_12 TBS_13 OLTP --压缩表空间状态 TBS_14 OLTP --压缩表空间状态 TBS_15 38 rows selected. SYS@orcl>
4：：在 tbs_15 表空间里创建表
---把 普通表空间 tbs_15 修改为压缩表空间 SYS@orcl> alter tablespace tbs_15 default compress; Tablespace altered. ---在压缩表空间 tbs_15 里创建 oltp 压缩表 t_tbs15 SYS@orcl> create table t_tbs15 compress for oltp tablespace tbs_15 as select * from scott.emp; Table created. ---在压缩表空间 tbs_15 里创建 非 oltp 压缩表 t1_tbs15 SYS@orcl> create table t1_tbs15 tablespace tbs_15 as select * from scott.emp; Table created. ----对压缩表和非压缩表进行分析 SYS@orcl> analyze table t_tbs15 compute statistics; Table analyzed. SYS@orcl> analyze table t1_tbs15 compute statistics; Table analyzed. ----结论：在 oltp 压缩表空间里创建表，如果不指定默认的压缩方式，默认是按照表空间额压缩方式oltp 方式来进行压缩 SYS@orcl> select table_name ,blocks,compress_for from dba_tables where table_name in ('T_TBS15','T1_TBS15'); TABLE_NAME BLOCKS COMPRESS_FOR ------------------------------ ---------- ------------ T1_TBS15 4 BASIC T_TBS15 4 OLTP ---创建表 t2_tbs15 SYS@orcl> create table t2_tbs15 compress tablespace tbs_15 as select * from scott.emp; Table created. -----结论：如果在 oltp 压缩表空间里创建表的时候，指定压缩方式为 basic compress ，那么创建的表示按照 basic compression 压缩；说明表级别的压缩方式会覆盖表空间的压缩方式 SYS@orcl> select table_name ,blocks,compress_for from dba_tables where table_name in ('T_TBS15','T1_TBS15','T2_TBS15'); TABLE_NAME BLOCKS COMPRESS_FOR ------------------------------ ---------- ------------ T1_TBS15 4 BASIC T_TBS15 4 OLTP T2_TBS15 BASIC ---压缩索引 SYS@orcl> create index ind_t_tbs15 on t_tbs15(empno,ename) tablespace tbs_15; Index created. SYS@orcl> create index ind_t1_tbs15 on t1_tbs15(empno,ename) tablespace tbs_15; Index created. ---结论：虽然是在压缩表空间里创建索引，都是索引仍然是 disabled ；没有压缩，所以如果要压缩索引，必须手动指明 compress SYS@orcl> select index_name,compression,leaf_blocks,tablespace_name from user_indexes where table_name in ('T_TBS15','T1_TBS15'); INDEX_NAME COMPRESS LEAF_BLOCKS TABLESPACE_NAME ------------------------------ -------- ----------- ------------------------------ IND_T_TBS15 DISABLED 1 TBS_15 IND_T1_TBS15 DISABLED 1 TBS_15 SYS@orcl>
18：创建一个临时表空间
临时表空间概念
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。
临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。
创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。
另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。
临时表空间信息
-----查看实例的临时表空间 SYS@orcl> col PROPERTY_NAME for a30; SYS@orcl> col PROPERTY_VALUE for a50; SYS@orcl> select PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ -------------------------------------------------- DEFAULT_TEMP_TABLESPACE TBS_TEMP_GROUP_1 ----- SYS@orcl> SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS; USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ HR GROUP3 SH TBS_TEMP_GROUP_1 SCOTT TBS_TEMP_GROUP_1 SPATIAL_WFS_ADMIN_USR TBS_TEMP_GROUP_1 SPATIAL_CSW_ADMIN_USR TBS_TEMP_GROUP_1 APEX_PUBLIC_USER TBS_TEMP_GROUP_1 OE TBS_TEMP_GROUP_1 DIP TBS_TEMP_GROUP_1 IX TBS_TEMP_GROUP_1 MDDATA TBS_TEMP_GROUP_1 PM TBS_TEMP_GROUP_1 USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ BI TBS_TEMP_GROUP_1 XS$NULL TBS_TEMP_GROUP_1 ORACLE_OCM TBS_TEMP_GROUP_1 DONGFANG TBS_TEMP_GROUP_1 XIFANG TBS_TEMP_GROUP_1 EAST TBS_TEMP_GROUP_1 MR TBS_TEMP_GROUP_1 DBSNMP TBS_TEMP_GROUP_1 SYSMAN TBS_TEMP_GROUP_1 OLAPSYS TBS_TEMP_GROUP_1 SI_INFORMTN_SCHEMA TBS_TEMP_GROUP_1 USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ OWBSYS TBS_TEMP_GROUP_1 ORDPLUGINS TBS_TEMP_GROUP_1 XDB TBS_TEMP_GROUP_1 ANONYMOUS TBS_TEMP_GROUP_1 CTXSYS TBS_TEMP_GROUP_1 ORDDATA TBS_TEMP_GROUP_1 OWBSYS_AUDIT TBS_TEMP_GROUP_1 APEX_030200 TBS_TEMP_GROUP_1 APPQOSSYS TBS_TEMP_GROUP_1 WMSYS TBS_TEMP_GROUP_1 EXFSYS TBS_TEMP_GROUP_1 USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ORDSYS TBS_TEMP_GROUP_1 MDSYS TBS_TEMP_GROUP_1 FLOWS_FILES TBS_TEMP_GROUP_1 SYSTEM TBS_TEMP_GROUP_1 SYS TBS_TEMP_GROUP_1 MGMT_VIEW TBS_TEMP_GROUP_1 OUTLN TBS_TEMP_GROUP_1 40 rows selected.
------查看临时表空间信息： SYS@orcl> SET LINESIZE 1200; SYS@orcl> COL NAME FOR A60; SYS@orcl> SELECT FILE# AS FILE_NUMBER ,NAME AS NAME ,CREATION_TIME AS CREATION_TIME ,BLOCK_SIZE AS BLOCK_SIZE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)" ,STATUS AS STATUS ,ENABLED AS ENABLED FROM V$TEMPFILE; FILE_NUMBER NAME CREATION_ BLOCK_SIZE FILE_SIZE(G) INIT_SIZE(G) STATUS ENABLED ----------- ------------------------------------------------------------ --------- ---------- ------------ ------------ ------- ---------- 1 /u01/app/oracle/oradata/orcl/temp.dbf 11-MAR-18 8192 .048828125 .048828125 ONLINE READ WRITE 6 /u01/app/oracle/oradata/orcl/temp_01.dbf 12-MAR-18 8192 .29296875 .29296875 ONLINE READ WRITE 2 /u01/app/oracle/oradata/orcl/temp_1.dbf 11-MAR-18 8192 .048828125 .048828125 ONLINE READ WRITE 3 /u01/app/oracle/oradata/orcl/temp_2.dbf 11-MAR-18 8192 3 2 ONLINE READ WRITE 4 /home/oracle/oracle_system_files_back/datafiles_bak/datafile 11-MAR-18 8192 2 2 ONLINE READ WRITE s_bak_20180311/temp_6.dbf 5 /u01/app/oracle/oradata/orcl/tbs_example .dbf 11-MAR-18 8192 .048828125 .048828125 ONLINE READ WRITE 7 /u01/app/oracle/oradata/orcl/tp1.dbf 12-MAR-18 8192 .09765625 .09765625 ONLINE READ WRITE 8 /u01/app/oracle/oradata/orcl/tp2.dbf 12-MAR-18 8192 .09765625 .09765625 ONLINE READ WRITE 9 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 24-MAY-18 8192 .009765625 .009765625 ONLINE READ WRITE FILE_NUMBER NAME CREATION_ BLOCK_SIZE FILE_SIZE(G) INIT_SIZE(G) STATUS ENABLED ----------- ------------------------------------------------------------ --------- ---------- ------------ ------------ ------- ---------- 10 /u01/app/oracle/oradata/orcl/tbs_17_01.dbf 24-MAY-18 8192 .009765625 .009765625 ONLINE READ WRITE 11 /u01/app/oracle/oradata/orcl/tbs_17_02.dbf 24-MAY-18 8192 .009765625 .009765625 ONLINE READ WRITE 12 /u01/app/oracle/oradata/orcl/tbs_19_group_temp.dbf 30-MAY-18 8192 .009765625 .009765625 ONLINE READ WRITE 13 /u01/app/oracle/oradata/orcl/tbs_20_group_temp.dbf 30-MAY-18 8192 .009765625 .009765625 ONLINE READ WRITE 13 rows selected. SYS@orcl>
官方文档关于V$TEMPFILE的介绍如下
Column
Datatype
Description
FILE#
NUMBER
Absolute file number
CREATION_CHANGE#
NUMBER
Creation System Change Number (SCN)
CREATION_TIME
DATE
Creation time
TS#
NUMBER
Tablespace number
RFILE#
NUMBER
Relative file number in the tablespace
STATUS
VARCHAR2(7)
Status of the file (OFFLINE|ONLINE)
ENABLED
VARCHAR2(10)
Enabled for read and/or write
BYTES
NUMBER
Size of the file in bytes (from the file header)
BLOCKS
NUMBER
Size of the file in blocks (from the file header)
CREATE_BYTES
NUMBER
Creation size of the file (in bytes)
BLOCK_SIZE
NUMBER
Block size for the file
NAME
VARCHAR2(513)
Name of the file
SYS@orcl> COL TABLESPACE_NAME FOR A30; SYS@orcl> COL FILE_NAME FOR A60; SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TEMP /u01/app/oracle/oradata/orcl/temp.dbf 6400 ONLINE YES .048828125 2 6400 .047851563 TEMP_01 /u01/app/oracle/oradata/orcl/temp_01.dbf 38400 ONLINE NO .29296875 .29296875 0 .291992188 TEMP_1 /u01/app/oracle/oradata/orcl/temp_1.dbf 6400 ONLINE YES .048828125 2 6400 .047851563 TEMP_1 /u01/app/oracle/oradata/orcl/temp_2.dbf 393216 ONLINE NO 3 3 0 2.99902344 TEMP_1 /home/oracle/oracle_system_files_back/datafiles_bak/datafile 262144 ONLINE YES 2 31.9999847 12800 1.99902344 s_bak_20180311/temp_6.dbf TBS_EXAMPLE /u01/app/oracle/oradata/orcl/tbs_example .dbf 6400 ONLINE YES .048828125 2 6400 .047851563 TP1 /u01/app/oracle/oradata/orcl/tp1.dbf 12800 ONLINE NO .09765625 .09765625 0 .096679688 TP2 /u01/app/oracle/oradata/orcl/tp2.dbf 12800 ONLINE NO .09765625 .09765625 0 .096679688 TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_17 /u01/app/oracle/oradata/orcl/tbs_17_01.dbf 1280 ONLINE NO .009765625 .009765625 0 .008789063 TBS_17 /u01/app/oracle/oradata/orcl/tbs_17_02.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_19_GROUP /u01/app/oracle/oradata/orcl/tbs_19_group_temp.dbf 1280 ONLINE NO .009765625 .009765625 0 .008789063 TBS_20_GROUP /u01/app/oracle/oradata/orcl/tbs_20_group_temp.dbf 1280 ONLINE NO .009765625 .009765625 0 .008789063 13 rows selected. SYS@orcl>DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.
Column
Datatype
NULL
Description
FILE_NAME
VARCHAR2(513)
Name of the database temp file
FILE_ID
NUMBER
File identifier number of the database temp file
TABLESPACE_NAME
VARCHAR2(30)
NOT NULL
Name of the tablespace to which the file belongs
BYTES
NUMBER
Size of the file (in bytes)
BLOCKS
NUMBER
Size of the file (in Oracle blocks)
STATUS
CHAR(9)
File status:
·
· AVAILABLE
RELATIVE_FNO
NUMBER
Tablespace-relative file number
AUTOEXTENSIBLE
VARCHAR2(3)
Indicates whether the file is autoextensible (YES) or not (NO)
MAXBYTES
NUMBER
maximum size of the file (in bytes)
MAXBLOCKS
NUMBER
Maximum size of the file (in Oracle blocks)
INCREMENT_BY
NUMBER
Default increment for autoextension
USER_BYTES
NUMBER
Size of the useful portion of the file (in bytes)
USER_BLOCKS
NUMBER
Size of the useful portion of the file (in Oracle blocks)
SYS@orcl> SELECT BYTES,BLOCKS, USER_BYTES, USER_BLOCKS, BLOCKS -USER_BLOCKS AS SYSTEM_USED FROM DBA_TEMP_FILES; BYTES BLOCKS USER_BYTES USER_BLOCKS SYSTEM_USED ---------- ---------- ---------- ----------- ----------- 52428800 6400 51380224 6272 128 314572800 38400 313524224 38272 128 52428800 6400 51380224 6272 128 3221225472 393216 3220176896 393088 128 2147483648 262144 2146435072 262016 128 52428800 6400 51380224 6272 128 104857600 12800 103809024 12672 128 104857600 12800 103809024 12672 128 10485760 1280 9437184 1152 128 10485760 1280 9437184 1152 128 10485760 1280 9437184 1152 128 BYTES BLOCKS USER_BYTES USER_BLOCKS SYSTEM_USED ---------- ---------- ---------- ----------- ----------- 10485760 1280 9437184 1152 128 10485760 1280 9437184 1152 128 13 rows selected.这四列中， BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，这一部分大小是128个block，如下图所示:
管理临时表空间
创建临时表空间
下面是一个简单的创建临时表空间的例子，具体很多细节可以参考官方文档，这里省略,不做过多介绍。
http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11366
SYS@orcl> create temporary tablespace tbs_16 tempfile '/u01/app/oracle/oradata/orcl/tbs_16_01.dbf' size 10m reuse autoextend on next 10m maxsize unlimited ; Tablespace created. SYS@orcl> create temporary tablespace tbs_17 tempfile '/u01/app/oracle/oradata/orcl/tbs_17_01.dbf' size 10m reuse , '/u01/app/oracle/oradata/orcl/tbs_17_02.dbf' size 10m reuse autoextend on next 10m maxsize unlimited ; Tablespace created. SYS@orcl>
增加数据文件
当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。
SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 SYS@orcl> SYS@orcl> alter tablespace TBS_16 add tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' size 10m autoextend on next 10m maxsize unlimited; Tablespace altered. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 SYS@orcl>
删除数据文件
SYS@orcl> alter tablespace TBS_16 drop tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf'; Tablespace altered. SYS@orcl>注意：这种删除临时表空间的写法会将对应的物理文件删除。
或者：
SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' drop including datafiles; Database altered. SYS@orcl>注意：删除临时表空间的临时数据文件时，不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件，否则需要手工删除物理文件。
调整文件大小
SYS@orcl> alter tablespace TBS_16 add tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' size 10m autoextend on next 10m maxsize unlimited; Tablespace altered. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' resize 20m; Database altered. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf 2560 ONLINE YES .01953125 31.9999847 1280 .018554688 SYS@orcl>
文件脱机联机
SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf 2560 ONLINE YES .01953125 31.9999847 1280 .018554688 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_01.dbf' offline; Database altered. SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' offline; Database altered. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf OFFLINE TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf OFFLINE SYS@orcl>默认临时表空间并不能脱机,否则会报错，如下所示
SQL> ALTER TABLESPACE TEMP OFFLINE;
ALTER TABLESPACE TEMP OFFLINE
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
默认的临时表空间组是可以使其任意一个表空间的数据文件进行脱机操作。
SYS@orcl> select PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ -------------------------------------------------- DEFAULT_TEMP_TABLESPACE TBS_TEMP_GROUP_1 SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_TEMP_GROUP_1' ; no rows selected SYS@orcl> select * from dba_tablespace_groups; SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_19_GROUP TBS_TEMP_GROUP_1 TBS_20_GROUP 6 rows selected. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_19_GROUP' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_19_GROUP /u01/app/oracle/oradata/orcl/tbs_19_group_temp.dbf 1280 ONLINE NO .009765625 .009765625 0 .008789063 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_19_group_temp.dbf' offline; Database altered. SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_19_GROUP TBS_TEMP_GROUP_1 TBS_20_GROUP 6 rows selected. SYS@orcl>
设置文件自动扩展
---- SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf OFFLINE TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf OFFLINE SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' autoextend on next 10m maxsize unlimited; alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' autoextend on next 10m maxsize unlimited * ERROR at line 1: ORA-00376: file 214 cannot be read at this time ORA-01110: data file 214: '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' online; Database altered. SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_01.dbf' online; Database altered. SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_01.dbf' autoextend on next 10m maxsize unlimited; Database altered. SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' autoextend on next 10m maxsize unlimited; Database altered. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf 2560 ONLINE YES .01953125 31.9999847 1280 .018554688 SYS@orcl>从上述操作可以看出：当数据文件处于脱机状态时 时是不能对该数据文件进行相应的操作。
即：修改数据文件为自动扩展是需要数据文件处于在线状态的
移动重命名文件
----查看临时表空间及文件信息 SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_02.dbf 2560 ONLINE YES .01953125 31.9999847 1280 .018554688 ---将表空间 文件2 的数据文件脱机 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' offline; Database altered. ---查看文件路径信息 SYS@orcl> ho ls /home/oracle database Desktop grid h:1dept.sql h:1emp.txt h:1.lst h:1spooltest.txt h:emp.txt oracle_system_files_back oyt.lst rlwrap-0.37 rlwrap-0.37.tar.gz SYS@orcl> ho ls /home/oracle/oracle_system_files_back archivelog controlfile_bak datafiles_bak install_zip logfile_bak redo_bak ---最终文件存放位置 （这步骤可以省略） SYS@orcl> ho ls /home/oracle/oracle_system_files_back/datafiles_bak datafiles_bak_20180227 datafiles_bak_20180311 ---移动或者重名命 相关的临时文件 SYS@orcl> ho mv /u01/app/oracle/oradata/orcl/tbs_16_02.dbf /home/oracle/oracle_system_files_back/datafiles_bak/tbs_16_02.dbf ----使用脚本alter database rename file SYS@orcl> ho ls /home/oracle/oracle_system_files_back/datafiles_bak datafiles_bak_20180227 datafiles_bak_20180311 tbs_16_02.dbf ----将临时表空间的临时文件联机 SYS@orcl> alter database rename file '/u01/app/oracle/oradata/orcl/tbs_16_02.dbf' to '/home/oracle/oracle_system_files_back/datafiles_bak/tbs_16_02.dbf'; Database altered. ---查看文件信息 SYS@orcl> alter database tempfile '/home/oracle/oracle_system_files_back/datafiles_bak/tbs_16_02.dbf' online; Database altered. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /home/oracle/oracle_system_files_back/datafiles_bak/tbs_16_0 2560 ONLINE YES .01953125 31.9999847 1280 .018554688 2.dbf SYS@orcl>
删除临时表空间
注意：不能删除当前用户的默认表空间，否则会报ORA-12906错误
或者不能删除默认的临时表空间组
SYS@orcl> select PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ -------------------------------------------------- DEFAULT_TEMP_TABLESPACE TBS_TEMP_GROUP_1 SYS@orcl> drop tablespace TBS_TEMP_GROUP_1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; drop tablespace TBS_TEMP_GROUP_1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS * ERROR at line 1: ORA-10917: TABLESPACE GROUP cannot be specified如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间
SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_16 /u01/app/oracle/oradata/orcl/tbs_16_01.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 TBS_16 /home/oracle/oracle_system_files_back/datafiles_bak/tbs_16_0 2560 ONLINE YES .01953125 31.9999847 1280 .018554688 2.dbf SYS@orcl> drop tablespace TBS_16 including contents and datafiles cascade constraints; Tablespace dropped. SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_16' ; no rows selected SYS@orcl>
切换临时表空间
1：查看临时表空间
2：创建中转的临时表空间
3：添加相应的数据文件
4：切换临时表空间。
5：删除旧的临时表空间数据文件
6：如果有必要，重新指定用户临时表空间为新建的临时表空间
ALTER USER ODS TEMPORARY TABLESPACE TMP; ALTER USER EDS TEMPORARY TABLESPACE TMP; ALTER USER ETL TEMPORARY TABLESPACE TMP; ALTER USER DM TEMPORARY TABLESPACE TMP;
收缩临时表空间
排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。
SQL> ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G; SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'
监控临时表空间
1：查看临时表空间使用情况：
SYS@orcl> SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME", TT.TOTAL - TU.USED AS "FREE(G)", TT.TOTAL AS "TOTAL(G)", ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS "USED(%)", ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)" FROM (SELECT TABLESPACE_NAME, SUM(BYTES_USED) / 1024 / 1024 / 1024 USED FROM GV_$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) TU , (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) TT; TABLESPACE_NAME FREE(G) TOTAL(G) USED(%) FREE(%) ------------------------------ ---------- ---------- ---------- ---------- TBS_EXAMPLE .047851563 .048828125 2 98 TBS_EXAMPLE .096679688 .09765625 1 99 TBS_EXAMPLE .047851563 .048828125 2 98 TBS_EXAMPLE 5.04785156 5.04882813 .019 99.981 TBS_EXAMPLE TBS_EXAMPLE .008789063 .009765625 10 90 TBS_EXAMPLE .096679688 .09765625 1 99 TBS_EXAMPLE .291992188 .29296875 .333 99.667 TBS_EXAMPLE .018554688 .01953125 5 95 TP1 .043945313 .048828125 10 90 TP1 .092773438 .09765625 5 95
2：查看临时表空间使用情况：
SYS@orcl> set linesize 300; SYS@orcl> COL TEMP_FILE FOR A60; SYS@orcl> SELECT ROUND((F.BYTES_FREE + F.BYTES_USED)/1024/1024/1024, 2) AS "TOTAL(GB)", 2 ROUND(((F.BYTES_FREE + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)", 3 D.FILE_NAME AS "TEMP_FILE", 4 ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)" , 5 ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2) AS "TOTAL(GB)", 6 ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" , 7 ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)" 8 FROM SYS.V_$TEMP_SPACE_HEADER F ,DBA_TEMP_FILES D ,SYS.V_$TEMP_EXTENT_POOL P 9 WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME 10 AND F.FILE_ID(+) = D.FILE_ID 11 AND P.FILE_ID(+) = D.FILE_ID; TOTAL(GB) FREE(GB) TEMP_FILE USED(GB) TOTAL(GB) FREE(GB) USED(GB) ---------- ---------- ------------------------------------------------------------ ---------- ---------- ---------- ---------- .05 .05 /u01/app/oracle/oradata/orcl/temp.dbf 0 .05 .05 0 .01 .01 /u01/app/oracle/oradata/orcl/tbs_17_01.dbf 0 .01 .01 0 .01 .01 /u01/app/oracle/oradata/orcl/tbs_17_02.dbf 0 .01 .01 0 /u01/app/oracle/oradata/orcl/tbs_19_group_temp.dbf 0 0 .01 .01 /u01/app/oracle/oradata/orcl/tbs_20_group_temp.dbf 0 .01 .01 0 .05 .05 /u01/app/oracle/oradata/orcl/tbs_example .dbf 0 .05 .05 0 .1 .1 /u01/app/oracle/oradata/orcl/tp2.dbf 0 .1 .1 0 3 3 /u01/app/oracle/oradata/orcl/temp_2.dbf 0 3 3 0 .05 .05 /u01/app/oracle/oradata/orcl/temp_1.dbf 0 .05 .05 0 .29 .29 /u01/app/oracle/oradata/orcl/temp_01.dbf 0 .29 .29 0 .1 .1 /u01/app/oracle/oradata/orcl/tp1.dbf 0 .1 .1 0 TOTAL(GB) FREE(GB) TEMP_FILE USED(GB) TOTAL(GB) FREE(GB) USED(GB) ---------- ---------- ------------------------------------------------------------ ---------- ---------- ---------- ---------- 2 2 /home/oracle/oracle_system_files_back/datafiles_bak/datafile 0 2 2 0 s_bak_20180311/temp_6.dbf 12 rows selected. SYS@orcl>
3：查看临时表空间对应的临时文件的使用情况:
SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME , 2 BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED , 3 BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE 4 FROM V$TEMP_SPACE_HEADER 5 ORDER BY 1 DESC; TABLESPACE_NAME TABLESAPCE_USED TABLESAPCE_FREE ------------------------------ --------------- --------------- TP2 .004882813 .092773438 TP1 .004882813 .092773438 TEMP_1 .001953125 2.99804688 TEMP_1 .001953125 1.99804688 TEMP_1 .00390625 .044921875 TEMP_01 .000976563 .291992188 TEMP .034179688 .014648438 TBS_EXAMPLE .000976563 .047851563 TBS_20_GROUP .001953125 .0078125 TBS_17 .001953125 .0078125 TBS_17 .000976563 .008789063 11 rows selected. SYS@orcl>
4：查找消耗临时表空间资源比较多的SQL语句
SYS@orcl> col USERNAME for a20; SYS@orcl> col SID for a10; SYS@orcl> col EXTENTS for a20; SYS@orcl> col TABLESPACE for a20; SYS@orcl> col SQL_TEXT for a90; SYS@orcl> col EXTENTS for a40; SYS@orcl> SELECT se.username, se.sid, su.extents, su.blocks * to_number(rtrim(p.value)) as Space, tablespace, segtype, sql_text FROM v$sort_usage su, v$parameter p, v$session se, v$sql s WHERE p.name = 'db_block_size' AND su.session_addr = se.saddr AND s.hash_value = su.sqlhash AND s.address = su.sqladdr ORDER BY se.username, se.sid; USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT -------------------- ---------- ---------- ---------- -------------------- --------- ------------------------------------------------------------------------------------------ SYS 191 1 1048576 TEMP DATA select * from dba_tablespace_groups SYS@orcl>
19：Oracle 加密表空间
1：加密表空间与 wallet关系
2： TDE(Transparent Data Encryption 透明数据加密 ) 使用场景
3： TDE(Transparent Data Encryption 透明数据加密 ) 加密原理
1：配置 TDE环境：
----创建一个新目录： SYS@orcl> ho mkdir /u01/app/oracle/admin/orcl/wallet ----修改配置文件 $vi sqlnet.ora SYS@orcl> ho vi $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet/))) ~ "/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora" 1L, 114C written ---查看配置文件信息 SYS@orcl> ho cat $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet/))) ---使用sys 用户登录，创建 master key 文件，指定 wallet 密码创建后自动打开wallet 。 SYS@orcl> alter system set encryption key authenticated by "abc"; alter system set encryption key authenticated by "abc" * ERROR at line 1: ORA-28353: failed to open wallet -----从上操作得到报错信息来看:是被告知 钱包 wallet 并没有打开。需要重新启动数据库。才可以生效。 -----以下操作是：关闭数据库，然后在启动数据库 SYS@orcl> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl> startup ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 381683196 bytes Database Buffers 134217728 bytes Redo Buffers 5861376 bytes Database mounted. Database opened. ----创建 wallet 密码 SYS@orcl> alter system set encryption key authenticated by "abc"; System altered. ----查看生成的密码文件 SYS@orcl> ho ls /u01/app/oracle/admin/orcl/wallet ewallet.p12 SYS@orcl>
、
SYS@orcl> set linesize 200; SYS@orcl> col name for a30; SYS@orcl> col datafile_name for a90; SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_14 /u01/app/oracle/oradata/orcl/tbs_14.dbf NO TBS_15 /u01/app/oracle/oradata/orcl/tbs_15.dbf NO NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 33 rows selected. SYS@orcl> create tablespace tbs_16_wallet datafile '/u01/app/oracle/oradata/orcl/tbs_16_wallet.dbf' size 10m encryption default storage(encrypt); Tablespace created. SYS@orcl> create tablespace tbs_17_wallet datafile '/u01/app/oracle/oradata/orcl/tbs_17_wallet.dbf'size 10m encryption using 'AES256' default storage(encrypt); Tablespace created. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_14 /u01/app/oracle/oradata/orcl/tbs_14.dbf NO TBS_15 /u01/app/oracle/oradata/orcl/tbs_15.dbf NO NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_16_WALLET /u01/app/oracle/oradata/orcl/tbs_16_wallet.dbf NO TBS_17_WALLET /u01/app/oracle/oradata/orcl/tbs_17_wallet.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 35 rows selected. SYS@orcl>
----打开钱包 SYS@orcl> alter system set wallet open identified by "abc"; alter system set wallet open identified by "abc" * ----此时报错的原因： 是 我的钱包已经打开了 ERROR at line 1: ORA-28354: Encryption wallet, auto login wallet, or HSM is already open ---关闭钱包 SYS@orcl> alter system set wallet close identified by "abc"; System altered. --打开钱包 SYS@orcl> alter system set wallet open identified by "abc"; System altered. ----查看表空间属性 SYS@orcl> select tablespace_name ,encrypted from dba_tablespaces; TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO UNDOTBS1 NO TEMP NO USERS NO EXAMPLE NO TBS_TEST1 NO TBS_TEST_3 NO TBS_TEST4 NO TBS_TEST5 NO TBS_BIG_1 NO TABLESPACE_NAME ENC ------------------------------ --- TEMP_1 NO TBS_EXAMPLE NO TEMP_01 NO TP1 NO TP2 NO TS_1 NO TS_2 NO TBSP_1 NO COMPRESS_01 NO COMPRESS_02 NO TEST NO TABLESPACE_NAME ENC ------------------------------ --- TEST2 NO TBS_01 NO TBS_02 NO TBS_03 NO TBS_04 NO TBS_05 NO TBS_06 NO TBS_07 NO TBS_08 NO TBS_09 NO TBS_10 NO TABLESPACE_NAME ENC ------------------------------ --- TBS_11 NO TBS_12 NO TBS_13 NO TBS_14 NO TBS_15 NO TBS_16 NO TBS_17 NO TBS_16_WALLET YES TBS_17_WALLET YES 42 rows selected. SYS@orcl>
4：实验
SYS@orcl> create table encrypted_t (x int ) tablespace TBS_16_WALLET ; Table created. SYS@orcl> insert into encrypted_t values(100); 1 row created. SYS@orcl> commit; Commit complete. SYS@orcl> select * from encrypted_t ; SYS@orcl> alter system set wallet close identified by "abc"; System altered. SYS@orcl> select * from encrypted_t ; select * from encrypted_t * ERROR at line 1: ORA-28365: wallet is not open SYS@orcl> alter system set wallet open identified by "abc"; System altered. SYS@orcl> select * from encrypted_t ; X ---------- 100 SYS@orcl> alter system set wallet close identified by "abc"; System altered. SYS@orcl> create table encrypted_t_1 (x int ) tablespace TBS_16_WALLET ; create table encrypted_t_1 (x int ) tablespace TBS_16_WALLET * ERROR at line 1: ORA-28365: wallet is not open SYS@orcl> alter system set wallet open identified by "abc"; System altered. SYS@orcl> create table encrypted_t_1 (x int ) tablespace TBS_16_WALLET ; Table created. SYS@orcl> alter system set wallet close identified by "abc"; System altered. SYS@orcl> drop table encrypted_t_1 ; Table dropped. SYS@orcl> drop tablespace TBS_16_WALLET including contents and datafiles; Tablespace dropped. SYS@orcl> create table tt (x int) tablespace tbs_13 ; Table created. SYS@orcl> alter table tt move tablespace TBS_17_WALLET ; alter table tt move tablespace TBS_17_WALLET * ERROR at line 1: ORA-28365: wallet is not open SYS@orcl> alter system set wallet open identified by "abc"; System altered. SYS@orcl> alter table tt move tablespace TBS_17_WALLET ; Table altered.
20：临时表空间组
临进表空间组:
临进表空间组是ORACLE 10g引入的一个新特性，它是一个逻辑概念，不需要显示的创建和删除。只要把一个临时表空间分配到一个组中，临时表空间组就自动创建，所有的临时表空间从临时表空间组中移除就自动删除。
一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制.
A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces
如果删除一个临时表空间组的所有成员，该组也自动被删除。
临时表空间的名字不能与临时表空间组的名字相同。
It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.
可以在创建临时表空间是指定表空间组，即隐式创建。
1：隐式创建表空间组
----可以在创建临时表空间是指定表空间组，即隐式创建。 SYS@orcl> create temporary tablespace tbs_19_group tempfile '/u01/app/oracle/oradata/orcl/tbs_19_group_temp.dbf' size 10m tablespace group tbs_temp_group_1; Tablespace created. ---查看临时表空间组 SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_19_GROUP ----设置为默认的表空间 SYS@orcl> alter database default temporary tablespace tbs_temp_group_1; Database altered. ----可以在创建临时表空间是指定表空间组，即隐式创建。 SYS@orcl> create temporary tablespace tbs_20_group tempfile '/u01/app/oracle/oradata/orcl/tbs_20_group_temp.dbf' size 10m tablespace group tbs_temp_group_1; Tablespace created. SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_19_GROUP TBS_TEMP_GROUP_1 TBS_20_GROUP 6 rows selected.
2：向已经存在的表空间组增加已经创建好的临时表空间
----查看临时表空间数据信息 SYS@orcl> SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES where TABLESPACE_NAME='TBS_17' ; TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE ------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ ----------- TBS_17 /u01/app/oracle/oradata/orcl/tbs_17_01.dbf 1280 ONLINE NO .009765625 .009765625 0 .008789063 TBS_17 /u01/app/oracle/oradata/orcl/tbs_17_02.dbf 1280 ONLINE YES .009765625 31.9999847 1280 .008789063 SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_19_GROUP TBS_TEMP_GROUP_1 TBS_20_GROUP 6 rows selected. ----也可以指定已经创建好的临时表空间的临时表空间组。 SYS@orcl> alter tablespace tbs_17 tablespace group TBS_TEMP_GROUP_1; Tablespace altered. ----查看表空间组信息 SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_17 TBS_TEMP_GROUP_1 TBS_19_GROUP TBS_TEMP_GROUP_1 TBS_20_GROUP 7 rows selected. SYS@orcl>
3：向已经存在的表空间组移除已经创建好的临时表空间
SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_17 TBS_TEMP_GROUP_1 TBS_19_GROUP TBS_TEMP_GROUP_1 TBS_20_GROUP 7 rows selected. ----从表空间组中移除 SYS@orcl> alter tablespace TBS_17 tablespace group ''; Tablespace altered. SYS@orcl> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMP GROUP1 TEMP_1 GROUP1 TP1 GROUP1 TP2 TBS_TEMP_GROUP_1 TBS_19_GROUP TBS_TEMP_GROUP_1 TBS_20_GROUP 6 rows selected. SYS@orcl>
4：当为数据库指定临时表空间或为用户指定临时表空间时，可以使用临时表空间组的名称
5：删除临时表空间组(删除组成临时表空间组的所有临时表空间)
---删除临时表空间组(删除组成临时表空间组的所有临时表空间) SQL> alter tablespace tempts1 tablespace group GROUP1; SQL> drop tablespace tempts1 including contents and datafiles; SQL> select * from dba_tablespace_groups; SQL> alter user scott temporary tablespace temp; SQL> alter database orcl default temporary tablespace temp; SQL> drop tablespace tempts2 including contents and datafiles; SQL> select * from dba_tablespace_groups;
6：把临时表空间组指定给用户
----把临时表空间组指定给用户 SQL> alter user scott temporary tablespace GROUP2; SQL> select username,temporary_tablespace from dba_users where username='SCOTT';
7：将表空间从一个临时表空间组移动到另外一个临时表空间组
--将表空间从一个临时表空间组移动到另外一个临时表空间组： SQL> alter tablespace tempts1 tablespace group GROUP2 ; SQL> select * from dba_tablespace_groups;
8：查询临时表空间组
21：查看数据库的特性
SYS@orcl> col PROPERTY_NAME for a30; SYS@orcl> col PROPERTY_VALUE for a40; SYS@orcl> col DESCRIPTION for a90; SYS@orcl> select * from database_properties; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ---------------------------------------- ------------------------------------------------------------------------------------------ DICT.BASE 2 dictionary base tables version # DEFAULT_TEMP_TABLESPACE TBS_TEMP_GROUP_1 Name of default temporary tablespace DEFAULT_PERMANENT_TABLESPACE TBS_TEST1 Name of default permanent tablespace DEFAULT_EDITION ORA$BASE Name of the database default edition Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT TDE_MASTER_KEY_ID DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade DST_PRIMARY_TT_VERSION 14 Version of primary timezone data file DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file DEFAULT_TBS_TYPE SMALLFILE Default tablespace type NLS_LANGUAGE AMERICAN Language PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ---------------------------------------- ------------------------------------------------------------------------------------------ NLS_TERRITORY AMERICA Territory NLS_CURRENCY $ Local currency NLS_ISO_CURRENCY AMERICA ISO currency NLS_NUMERIC_CHARACTERS ., Numeric characters NLS_CHARACTERSET ZHS16GBK Character set NLS_CALENDAR GREGORIAN Calendar system NLS_DATE_FORMAT DD-MON-RR Date format NLS_DATE_LANGUAGE AMERICAN Date language NLS_SORT BINARY Linguistic definition NLS_TIME_FORMAT HH.MI.SSXFF AM Time format NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ---------------------------------------- ------------------------------------------------------------------------------------------ NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format NLS_DUAL_CURRENCY $ Dual currency symbol NLS_COMP BINARY NLS comparison NLS_LENGTH_SEMANTICS BYTE NLS length semantics NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters GLOBAL_DB_NAME ORCL Global database name EXPORT_VIEWS_VERSION 8 Export views revision # WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ---------------------------------------- ------------------------------------------------------------------------------------------ WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress NO_USERID_VERIFIER_SALT 6396C58AB17AE007E9A7289C0FE59274 DBTIMEZONE 00:00 DB time zone 36 rows selected. SYS@orcl>
22：undo表空间创建
SYS@orcl> col NAME for a20 SYS@orcl> col DATAFILE_NAME for a60; SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_14 /u01/app/oracle/oradata/orcl/tbs_14.dbf NO TBS_15 /u01/app/oracle/oradata/orcl/tbs_15.dbf NO NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- TBS_17_WALLET /u01/app/oracle/oradata/orcl/tbs_17_wallet.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 34 rows selected. SYS@orcl> create undo tablespace tbs_21_undo datafile '/u01/app/oracle/oradata/orcl/tbs_21_undo.dbf' size 10m; Tablespace created. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_14 /u01/app/oracle/oradata/orcl/tbs_14.dbf NO TBS_15 /u01/app/oracle/oradata/orcl/tbs_15.dbf NO NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- TBS_17_WALLET /u01/app/oracle/oradata/orcl/tbs_17_wallet.dbf NO TBS_21_UNDO /u01/app/oracle/oradata/orcl/tbs_21_undo.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO NAME DATAFILE_NAME AUT -------------------- ------------------------------------------------------------ --- UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 35 rows selected. SYS@orcl>
23：创建非标准块的表空间
---查看系统参数 db_16k_cache_size 的大小 19:57:06 SYS@orcl> show parameter db_16k_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_16k_cache_size big integer 16M ---修改系统参数 db_16k_cache_size 的大小为 32M 19:57:39 SYS@orcl> alter system set db_16k_cache_size =32M ; System altered. ---查看系统参数 db_16k_cache_size 的大小 19:57:46 SYS@orcl> show parameter db_16k_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_16k_cache_size big integer 32M ---设置 plsql 属性值 19:59:31 SYS@orcl> col name for a30; 19:59:39 SYS@orcl> col DATAFILE_NAME for a80; 19:59:51 SYS@orcl> set linesize 200; ---查询表空间名及其对应的表空间数据文件 20:00:04 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_14 /u01/app/oracle/oradata/orcl/tbs_14.dbf NO TBS_15 /u01/app/oracle/oradata/orcl/tbs_15.dbf NO NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- TBS_17_WALLET /u01/app/oracle/oradata/orcl/tbs_17_wallet.dbf NO TBS_21_UNDO /u01/app/oracle/oradata/orcl/tbs_21_undo.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 35 rows selected. ----创建非标准块表空间 20:00:06 SYS@orcl> create tablespace tbs_22_blocksize datafile '/u01/app/oracle/oradata/orcl/tbs_22_blocksize datafile.dbf' size 10m blocksize 16k; Tablespace created. ---查询表空间名及其对应的表空间数据文件 20:01:05 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- COMPRESS_01 /u01/app/oracle/oradata/orcl/compress_01.dbf NO COMPRESS_02 /u01/app/oracle/oradata/orcl/compress_02.dbf NO EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf YES SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf YES SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf YES TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES TBS_01 /u01/app/oracle/oradata/orcl/tbs_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_01.dbf NO TBS_02 /u01/app/oracle/oradata/orcl/tbs_02_02.dbf NO TBS_03 /u01/app/oracle/oradata/orcl/tbs_03.dbf YES TBS_04 /u01/app/oracle/oradata/orcl/tbs_04.dbf YES NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- TBS_05 /u01/app/oracle/oradata/orcl/tbs_05.dbf YES TBS_06 /u01/app/oracle/oradata/orcl/tbs_06.dbf NO TBS_07 /u01/app/oracle/oradata/orcl/tbs_07.dbf NO TBS_08 /u01/app/oracle/oradata/orcl/tbs_08.dbf NO TBS_09 /u01/app/oracle/oradata/orcl/tbs_09.dbf NO TBS_10 /u01/app/oracle/oradata/orcl/tbs_10.dbf NO TBS_11 /u01/app/oracle/oradata/orcl/tbs_11.dbf NO TBS_12 /u01/app/oracle/oradata/orcl/tbs_12_01.dbf YES TBS_13 /u01/app/oracle/oradata/orcl/tbs_13.dbf NO TBS_14 /u01/app/oracle/oradata/orcl/tbs_14.dbf NO TBS_15 /u01/app/oracle/oradata/orcl/tbs_15.dbf NO NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- TBS_17_WALLET /u01/app/oracle/oradata/orcl/tbs_17_wallet.dbf NO TBS_21_UNDO /u01/app/oracle/oradata/orcl/tbs_21_undo.dbf NO TBS_22_BLOCKSIZE /u01/app/oracle/oradata/orcl/tbs_22_blocksize datafile.dbf NO TBS_BIG_1 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb NO TBS_TEST1 /u01/app/oracle/oradata/orcl/datafile1.dbf NO TBS_TEST4 /u01/app/oracle/oradata/orcl/datafile4.dbf NO TBS_TEST5 /u01/app/oracle/oradata/orcl/datafile5.dbf YES TBS_TEST_3 /u01/app/oracle/oradata/orcl/datafile3.dbf YES TEST /u01/app/oracle/oradata/orcl/test_01.dbf NO TEST2 /u01/app/oracle/oradata/orcl/test02.dbf YES TS_1 /u01/app/oracle/oradata/orcl/ts1.dbf NO NAME DATAFILE_NAME AUT ------------------------------ -------------------------------------------------------------------------------- --- TS_2 /u01/app/oracle/oradata/orcl/ts2.dbf NO UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES USERS /u01/app/oracle/oradata/orcl/users01.dbf YES 36 rows selected. 20:01:07 SYS@orcl>
24：表空间的脱机和联机
语法：
示例：
SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBSP_1' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES ONLINE 7446799 7446780 SYS@orcl> alter tablespace TBSP_1 offline; Tablespace altered. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBSP_1' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf OFFLINE 7446799 7446780 SYS@orcl> alter tablespace TBSP_1 online; Tablespace altered. SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBSP_1' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES ONLINE 7446856 7446834 SYS@orcl>
25：表空间重命名
语法：
示例：
SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBSP_1' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBSP_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES ONLINE 7446856 7446834 ----重命名 SYS@orcl> alter tablespace TBSP_1 rename to TBSP_1_1; Tablespace altered. ---查询表 空间信息 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBSP_1_1' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBSP_1_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES ONLINE 7446856 7446834 SYS@orcl>
26：表空间删除
语法：
-- 删除空的表空间，不会删除表空间所对应的数据文件 drop tablespace 名称； -- 删除非空的表空间，不会删除表空间所对应的数据文件 drop tablespace 名称 including contents; -- 删除非空的表空间，同时删除表空间所对应的数据文件 drop tablespace my12 including contents and datafiles;
27：表空间扩容
语法：
-- 1）增加数据文件 alter tablespace 名称 add datafile '路径' size 大小; --- 2）更改自动扩展 alter database datafile '路径' autoextend on; CREATE TABLESPACE mytbs8 DATAFILE '/u01/app/oracle/oradata/orcl/mytbs08.dbf' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE 50M; alter database datafile '/u01/app/oracle/oradata/orcl/mytbs08.dbf' autoextend on next 1M maxsize unlimited; alter database datafile '/u01/app/oracle/oradata/orcl/mytbs08.dbf' autoextend off; --- 3）更改数据文件大小 alter database datafile '路径' resize 大小;示例：
-----查询表空间信息 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBSP_1_1' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBSP_1_1 /u01/app/oracle/oradata/orcl/tbsp_1.dbf YES ONLINE 7446856 7446834 ----创建表空间 tbs_25 初始化为2m 每次增长为1m 最大为3m; SYS@orcl> create tablespace tbs_25 datafile '/u01/app/oracle/oradata/orcl/tbs_25.dbf' size 2m autoextend on next 1m maxsize 3m; Tablespace created. ---查看表空间tbs_25 信息 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBS_25' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBS_25 /u01/app/oracle/oradata/orcl/tbs_25.dbf YES ONLINE 0 0 ----把表空间为 tbs_25 设置为自动增长 每次增长为1m 没有限制最大大小 SYS@orcl> alter database datafile '/u01/app/oracle/oradata/orcl/tbs_25.dbf' autoextend on next 1m maxsize unlimited ; Database altered. ---查看表空间tbs_25 信息 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBS_25' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBS_25 /u01/app/oracle/oradata/orcl/tbs_25.dbf YES ONLINE 0 0 ---取消 表空间 tbs_25 为自动增长方式 SYS@orcl> alter database datafile '/u01/app/oracle/oradata/orcl/tbs_25.dbf' autoextend off; Database altered. ---查询表空间tbs_25的信息 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBS_25' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBS_25 /u01/app/oracle/oradata/orcl/tbs_25.dbf NO ONLINE 0 0 ---开启表空间tbs_25 为自动增长方式 SYS@orcl> alter database datafile '/u01/app/oracle/oradata/orcl/tbs_25.dbf' autoextend on; Database altered. ---查看表空间tbs_25信息 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBS_25' order by t1.name asc; NAME DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBS_25 /u01/app/oracle/oradata/orcl/tbs_25.dbf YES ONLINE 0 0 SYS@orcl> select distinct t1.name,t2.BLOCKS,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBS_25' order by t1.name asc; NAME BLOCKS DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ ---------- -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBS_25 256 /u01/app/oracle/oradata/orcl/tbs_25.dbf YES ONLINE 0 0 ---重置表空间大小为 2m SYS@orcl> alter database datafile '/u01/app/oracle/oradata/orcl/tbs_25.dbf' resize 2m; Database altered. ---查看表空间tbs_25信息 SYS@orcl> select distinct t1.name,t2.BLOCKS,t2.name datafile_name ,t.autoextensible ,t2.STATUS,t2.ONLINE_CHANGE# ,t2.OFFLINE_CHANGE# from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name ='TBS_25' order by t1.name asc; NAME BLOCKS DATAFILE_NAME AUT STATUS ONLINE_CHANGE# OFFLINE_CHANGE# ------------------------------ ---------- -------------------------------------------------------------------------------- --- ------- -------------- --------------- TBS_25 256 /u01/app/oracle/oradata/orcl/tbs_25.dbf YES ONLINE 0 0 SYS@orcl>
28：修改数据文件的可用性
语法：
--1）数据文件的脱机（需要在归档模式） alter database datafile '/home/oracle/my.dbf' offline; --- 2）数据文件的联机 recover datafile 6; alter database datafile '/home/oracle/my.dbf' online;
29：重命名和重定位数据文件
-- 1）alter tablespace 用户创建的表空间，数据库在open状态下 -- 1、脱机表空间 -- 2、改变位置 -- 3、使用alter tablespace -- 4、联机表空间-- 2）alter database 所有的表空间(包含system)，数据库在mount状态下 -- 1、mount状态 -- 2、改变位置 -- 3、使用alter database -- 4、open
30：删除数据文件
31：UNDO表空间
1、概念 2、参数 undo_retention 1、undo表空间是自动扩展的 2、undo表空间不是自动扩展的，表空间大小固定 Guarantee
---从系统参数方式来查看undo表空间信息 22:02:48 SYS@orcl> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 -----查看 undo表空间信息 22:02:54 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name like '%UNDO%' order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES ---创建一个 初始化为10m 大小为无限制的可增长的undo表空间 22:02:56 SYS@orcl> create undo tablespace tbs_29_undo datafile '/u01/app/oracle/oradata/orcl/tbs_29_undo.dbf' size 10m autoextend on next 1m maxsize unlimited; Tablespace created. 22:04:42 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name like '%UNDO%' order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_29_UNDO /u01/app/oracle/oradata/orcl/tbs_29_undo.dbf YES UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES ---创建一个 初始化为10m 大小为无限制的可增长的undo表空间 22:04:47 SYS@orcl> create undo tablespace tbs_30_undo datafile '/u01/app/oracle/oradata/orcl/tbs_30_undo.dbf' size 10m autoextend on next 1m maxsize unlimited; Tablespace created. -----查看 undo表空间信息 22:05:12 SYS@orcl> select distinct t1.name,t2.name datafile_name ,t.autoextensible from v$tablespace t1,v$datafile t2 ,dba_data_files t where t1.name=t.tablespace_name and t1.ts# = t2.ts# and t1.name like '%UNDO%' order by t1.name asc; NAME DATAFILE_NAME AUT ------------------------------ ------------------------------------------------------------------------------------------ --- TBS_29_UNDO /u01/app/oracle/oradata/orcl/tbs_29_undo.dbf YES TBS_30_UNDO /u01/app/oracle/oradata/orcl/tbs_30_undo.dbf YES UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf YES ---从系统参数方式来查看undo表空间信息 22:05:13 SYS@orcl> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 ---总结：从上述操作得知，不管系统存在多少个undo表空间，只能有且只有1个undo空间为默认表空间 ----切换 undo 表空间，切换后，会默认提交切换前所有为没有进行事务提交的的数据 22:05:23 SYS@orcl> alter system set undo_tablespace=TBS_29_UNDO; System altered. ---从系统参数方式来查看undo表空间信息 22:07:05 SYS@orcl> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string TBS_29_UNDO 22:07:08 SYS@orcl> ---undo 表空间作用：1：实例恢复 2：闪回操作 3：回滚操作 22:08:58 SYS@orcl> ---undo 表空间存放旧的值-----实验：ssh窗口1：
---创建t表 22:09:20 SYS@orcl> create table t(id number(38)); Table created. 22:09:47 SYS@orcl> desc t Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- ID NUMBER(38) ---插入数据 22:09:51 SYS@orcl> insert into t values(1); 1 row created. 22:10:15 SYS@orcl> commit; Commit complete. 22:10:17 SYS@orcl> select * from t; ID ---------- 1 ---更改数据 此时不进行事务提交操作 22:10:27 SYS@orcl> update t set id =2; 1 row updated. 22:10:41 SYS@orcl> select * from t; ID ---------- 2-----实验：ssh窗口2：
[root@localhost ~]# su - oracle [oracle@localhost ~]$ rlwrap sqlplus sys/oracle as sysdba; SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 6 22:11:29 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> select * from t; ID ---------- 1
---在事务还没进行提交时，在不同的客户端查询来看结果是不一样的。进一步体现了 数据一致性的原则
-----实验：ssh窗口1：进行 undo表空间的切换
----查看undo信息 22:10:49 SYS@orcl> show parameter und NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_core_dump string partial background_dump_dest string /u01/app/oracle/diag/rdbms/orc l/orcl/trace undo_management string AUTO undo_retention integer 900 undo_tablespace string TBS_29_UNDO ---切换undo表空间 22:12:06 SYS@orcl> alter system set undo_tablespace=TBS_30_UNDO; System altered. ---查看undo表空间信息 22:12:36 SYS@orcl> show parameter und NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_core_dump string partial background_dump_dest string /u01/app/oracle/diag/rdbms/orc l/orcl/trace undo_management string AUTO undo_retention integer 900 undo_tablespace string TBS_30_UNDO ---查看 t 表数据信息 22:12:43 SYS@orcl> select * from t; ID ---------- 2-----实验：ssh窗口2：
----因为切换了 undo表空间，之前的 未提交事务的旧数据已经丢失了。从另外一个角度来讲已经进行了事务的提交操作了。所有的客户端用户都也可以查看到该表的数据修改后的情况。
undo_retention integer 900---这个值表示 undo表空间里的数据可以保留多少秒的时间。如果undo表空间有充足的空闲空间，则旧的数据可以保留 规定的最大值 undo数据不会被覆盖，如果undo表空间的空闲空间不足，undo数据会被覆盖。
实验 1、查看undo 2、修改undo 3、创建undo表空间（自动扩展） 4、查看undo表空间信息（dba_tablespaces,dba_data_files） 5、undo表空间重命名 6、undo表空间增加数据文件 7、数据文件修改自动扩展 8、切换undo表空间 9、删除undo表空间
---一、用ALTER SYSTEM更改初始化参数UNDO_RETENTION为1200 ---二、创建UNDO 表空间my_undo,30M CRE... UNDO TAB.... my_undo DATAFILE ...... SIZE ... autoextend on; ---三、使用dba_tablespaces查看UNDO 表空间信息 ---四、使用dba_data_files查看UNDO 表空间的数据文件 select tablespace_name,file_name,bytes/1024/1024 MB,autoextensible from dba_data_files where tablespace_name='MY_UNDO'; ---五、重命名 alter tablespace my_undo rename to wl_undo; ---六、增加数据文件 alter tablespace wl_undo add datafile '/u01/app/oracle/oradata/orcl/my_undo1.dbf' size 100M; select tablespace_name,file_name,bytes/1024/1024 MB,autoextensible from dba_data_files where tablespace_name='WL_UNDO'; ---将数据文件修改为自动扩展模式 alter database datafile '/u01/app/oracle/oradata/orcl/my_undo1.dbf' autoextend on; select tablespace_name,file_name,bytes/1024/1024 MB,autoextensible from dba_data_files where tablespace_name='WL_UNDO'; ----七、切换到新建的UNDO 表空间 show parameter undo alter system set undo_tablespace=wl_undo; show parameter undo ---八、删除UNDO 表空间 create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs2.dbf' size 10m autoextend on maxsize 100m retention guarantee; retention guarantee ---保证在给定时间内还原记录一定会存在，不会被覆盖。 ---另开会话： create table t(id number,name varchar2(20)); insert into t values (1,'wl'); ---没有提交事物 在另一会话切换undo表空间 alter system set undo_tablespace=undotbs2; show parameter undo 删除旧的表空间 drop tablespace WL_UNDO; 报错，到另一个会话提交事务 commit; 再删除，依然报错 drop tablespace WL_UNDO; 因在undo_retention参数数值范围内，oracle依旧保留旧的undo记录 ALTER SYSTEM SET UNDO_RETENTION=0 SCOPE=BOTH; drop tablespace WL_UNDO; 九、使用dba_undo_extents看UNDO记录在那个表空间 insert into t values (2,'name'); 不提交 select a.username,b.name,c.used_ublk from v$session a,v$rollname b,v$transaction c where a.saddr=c.ses_addr and b.usn=c.xidusn; USERNAME NAME USED_UBLK ------------------------------ ------------------------------ ---------- SYS _SYSSMU27_345542384$ 1 select segment_name,tablespace_name,extent_id from dba_undo_extents where segment_name='_SYSSMU27_345542384$';
32：
六：管理归档日志
预备知识
1：归档日志：
2：日志的两种模式
1：非归档模式：
2：归档模式：
3： 配置归档
4： 归档日志命名格式
归档重做日志文件
归档重做日志文件
归档重做日志文件是联机重做日志组的已填充成员的副本。此文件不是该数据库的一部分，而是由该数据库生成、并被写到用户指定位置的联机重做日志文件脱机副本。
归档重做日志文件是备份和恢复策略的关键部分。可以使用归档重做日志文件：
1)恢复数据库备份
2)更新备用数据库
3)使用 LogMiner 实用程序获取有关数据库的历史信息
归档即是生成一个归档重做日志文件操作。归档可以是自动的或手动的，只有在数据库处于归档模式下时才会归档。一个归档重做日志文件包括重做条目和联机重做日志组的相同成员的日志序列号。查看归档日志信息
-- 1、列出归档日志信息SYS@orcl> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 307 Next log sequence to archive 310 Current log sequence 310--查看 后台进程SYS@orcl> ho ps -ef |grep ora_ |grep arc oracle 3907 5559 0 21:40 pts/2 00:00:00 /bin/bash -c ps -ef |grep ora_ |grep arc oracle 5701 1 0 19:18 ? 00:00:00 ora_arc0_orcl oracle 5703 1 0 19:18 ? 00:00:00 ora_arc1_orcl oracle 5705 1 0 19:18 ? 00:00:00 ora_arc2_orcl oracle 5707 1 0 19:18 ? 00:00:00 ora_arc3_orcl SYS@orcl>
显示归档进程
SYS@orcl> select * from v$archive_processes; PROCESS STATUS LOG_SEQUENCE STAT ---------- ---------- ------------ ---- 0 ACTIVE 0 IDLE 1 ACTIVE 0 IDLE 2 ACTIVE 0 IDLE 3 ACTIVE 0 IDLE 4 STOPPED 0 IDLE 5 STOPPED 0 IDLE 6 STOPPED 0 IDLE 7 STOPPED 0 IDLE 8 STOPPED 0 IDLE 9 STOPPED 0 IDLE 10 STOPPED 0 IDLE PROCESS STATUS LOG_SEQUENCE STAT ---------- ---------- ------------ ---- 11 STOPPED 0 IDLE 12 STOPPED 0 IDLE 13 STOPPED 0 IDLE 14 STOPPED 0 IDLE 15 STOPPED 0 IDLE 16 STOPPED 0 IDLE 17 STOPPED 0 IDLE 18 STOPPED 0 IDLE 19 STOPPED 0 IDLE 20 STOPPED 0 IDLE 21 STOPPED 0 IDLE PROCESS STATUS LOG_SEQUENCE STAT ---------- ---------- ------------ ---- 22 STOPPED 0 IDLE 23 STOPPED 0 IDLE 24 STOPPED 0 IDLE 25 STOPPED 0 IDLE 26 STOPPED 0 IDLE 27 STOPPED 0 IDLE 28 STOPPED 0 IDLE 29 STOPPED 0 IDLE 30 rows selected. SYS@orcl>
配置归档进程
---将数据库转变为 archivelog模式，默认情况下会自动启动两个归档进程，通过改变初始化参数的值，动态地增加或降低归档进程的个数 SYS@orcl> alter system set log_archive_max_processes=3; System altered. SYS@orcl>
显示归档日志信息
----显示归档日志信息 SYS@orcl> col name format a130 SYS@orcl> set linesize 200 SYS@orcl> select name ,sequence# ,first_change# from v$archived_log; NAME SEQUENCE# FIRST_CHANGE# ---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------- /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_30/o1_mf_1_6_d3vpslgx_.arc 6 871312 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_01/o1_mf_1_7_d409htxg_.arc 7 882050 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_01/o1_mf_1_8_d40c7orl_.arc 8 904103 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_02/o1_mf_1_9_d40l8y1c_.arc 9 909624 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_07/o1_mf_1_10_d4j2obov_.arc 10 923656 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_08/o1_mf_1_11_d4jtlscq_.arc 11 944369 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_08/o1_mf_1_12_d4lhchob_.arc 12 967164 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_08/o1_mf_1_13_d4lo17y0_.arc 13 1000721 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_08/o1_mf_1_14_d4lw9xlx_.arc 14 1024940 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_16/o1_mf_1_15_f39k9jq0_.arc 15 1049002 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_16/o1_mf_1_16_f39oyfvs_.arc 16 1082268 NAME SEQUENCE# FIRST_CHANGE# ---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------- /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_17/o1_mf_1_17_f3c0686v_.arc 17 1091603 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_17/o1_mf_1_18_f3cmx4dq_.arc 18 1124591 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_21/o1_mf_1_19_f3o4zttn_.arc 19 1144841 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive1/1_112_929245147.dbf 112 2940292 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2_1/1_112_929245147.dbf 112 2940292 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive3_1/1_112_929245147.dbf 112 2940292 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive5/1_112_929245147.dbf 112 2940292 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2/1_112_929245147.dbf 112 2940292 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive1/1_113_929245147.dbf 113 2964077 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2_1/1_113_929245147.dbf 113 2964077 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive3_1/1_113_929245147.dbf 113 2964077 NAME SEQUENCE# FIRST_CHANGE# ---------------------------------------------------------------------------------------------------------------------------------- ---------- ------------- /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive5/1_113_929245147.dbf 113 2964077 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2/1_113_929245147.dbf 113 2964077 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive1/1_114_929245147.dbf 114 2997418 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2_1/1_114_929245147.dbf 114 2997418 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive3_1/1_114_929245147.dbf 114 2997418 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive5/1_114_929245147.dbf 114 2997418 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2/1_114_929245147.dbf 114 2997418 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive1/1_115_929245147.dbf 115 3015682 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2_1/1_115_929245147.dbf 115 3015682 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive3_1/1_115_929245147.dbf 115 3015682 /home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive5/1_115_929245147.dbf 115 3015682
显示日志操作模式
---Noarchivelog 模式只能用于保护例程失败，不能用于保护介质失败。archivelog模式不仅仅可用于保护例程失败，还可以用于保护介质失败 SYS@orcl> select name ,log_mode from v$database; NAME LOG_MODE --------- ------------ ORCL ARCHIVELOG SYS@orcl> SYS@orcl>
查看归档日志文件路径信息
----查看归档文件路径信息 SYS@orcl> show parameter recover NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 2G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SYS@orcl>
SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area orcl ORCL SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ORCL archivelog onlinelog SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ORCL/archivelog 2016_11_30 2016_12_02 2016_12_08 2017_12_17 2018_02_27 2018_04_24 2018_04_27 2018_04_29 2018_05_20 2016_12_01 2016_12_07 2017_12_16 2017_12_21 2018_04_23 2018_04_26 2018_04_28 2018_04_30
显示日志历史信息
/* THREAD# :标识重做线程号 SEQUENCE#:标识日志序列号 FIRST_CHANGE#:标识日志序列号对应的起始SCN号 FIRST_TIM :标识起始的SCN发生时间 SWITCH_CHANGE# 标识日志切换时的scn */ SYS@orcl> select * from v$loghist; THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM SWITCH_CHANGE# ---------- ---------- ------------- --------- -------------- 1 27 1359599 24-DEC-17 1374615 1 28 1374615 24-DEC-17 1394713 1 29 1394713 24-DEC-17 1418763 1 30 1418763 24-DEC-17 1450752 1 31 1450752 26-DEC-17 1460292 1 32 1460292 26-DEC-17 1492872 1 33 1492872 27-DEC-17 1520582 1 34 1520582 27-DEC-17 1542876 1 35 1542876 28-DEC-17 1575070 1 36 1575070 28-DEC-17 1592280 1 37 1592280 28-DEC-17 1619663 THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM SWITCH_CHANGE# ---------- ---------- ------------- --------- -------------- 1 38 1619663 29-DEC-17 1652615 1 39 1652615 29-DEC-17 1663717 1 40 1663717 29-DEC-17 1672636 1 41 1672636 29-DEC-17 1693714 1 42 1693714 30-DEC-17 1719181 1 43 1719181 30-DEC-17 1740013 1 44 1740013 02-JAN-18 1748813 1 45 1748813 02-JAN-18 1760422 1 46 1760422 03-JAN-18 1793491 1 47 1793491 03-JAN-18 1815590 1 48 1815590 03-JAN-18 1839858
改变归档日志文件路径信息
方法一：alter system set log_archive_dest_1 ='location=/home/oracle/oracle_system_files_back/archivelog';
----查看 归档路径位置信息： 1-10：为本地或者远程路径 ；11-31 为远程路径地址 SYS@orcl> show parameter log_archive_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_19 string log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable log_archive_dest_state_2 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable log_archive_dest_state_3 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable SYS@orcl>
SYS@orcl> log_archive_dest_state_1 string enable ----若该参数 为有效状态，则 对应的 log_archive_dest_1 设置的路劲是有效的SYS@orcl> alter system set log_archive_dest_1 ='location=/home/oracle/oracle_system_files_back/archivelog'; System altered. SYS@orcl> alter system switch logfile; System altered. SYS@orcl> ho ls /home/oracle/oracle_system_files_back/archivelog/ 1_310_929245147.dbf archivelog_20180305 SYS@orcl> show parameter log_archive_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string location=/home/oracle/oracle_s ystem_files_back/archivelog log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_3 string enable log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable SYS@orcl>
----修改 归档日志文件的后缀名称 --查看格式信息 SYS@orcl> show parameter format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf nls_date_format string nls_time_format string nls_time_tz_format string nls_timestamp_format string nls_timestamp_tz_format string permit_92_wrap_format boolean TRUE star_transformation_enabled string FALSE --修改归档日志文件的后缀名格式化格式 SYS@orcl> alter system set log_archive_format=' %t_%s_%r.arc' scope=spfile; System altered. ---查看修改结果： SYS@orcl> show parameter format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf nls_date_format string nls_time_format string nls_time_tz_format string nls_timestamp_format string nls_timestamp_tz_format string permit_92_wrap_format boolean TRUE star_transformation_enabled string FALSE ---从结果来看，并没有变化，因此需要重新启动数据库 SYS@orcl> shutdown immediate; --关闭数据库 Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl> startup ---启动数据库 ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 377488892 bytes Database Buffers 138412032 bytes Redo Buffers 5861376 bytes Database mounted. Database opened. --再次查看 归档日志文件名称格式化信息 SYS@orcl> show parameter format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.arc nls_date_format string nls_time_format string nls_time_tz_format string nls_timestamp_format string nls_timestamp_tz_format string permit_92_wrap_format boolean TRUE star_transformation_enabled string FALSE ---切换日志 SYS@orcl> alter system switch logfile; System altered. ---查看新生成的归档日志的后缀名 SYS@orcl> ho ls /home/oracle/oracle_system_files_back/archivelog/ 1_310_929245147.dbf _1_311_929245147.arc archivelog_20180305 SYS@orcl>
SYS@orcl> alter system set log_archive_dest_2 ='location=/u01/app/oracle/fast_recovery_area/ORCL/archivelog' scope=spfile; System altered. SYS@orcl> shutdown immdiate; SP2-0717: illegal SHUTDOWN option SYS@orcl> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl> startup ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 377488892 bytes Database Buffers 138412032 bytes Redo Buffers 5861376 bytes Database mounted. Database opened. SYS@orcl> show parameter log_archive_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string location=/home/oracle/oracle_s ystem_files_back/archivelog log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string location=/u01/app/oracle/fast_ recovery_area/ORCL/archivelog log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_19 string enable log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_29 string enable log_archive_dest_state_3 string enable log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable SYS@orcl> alter system switch logfile; System altered. SYS@orcl> ho ls /home/oracle/oracle_system_files_back/archivelog/ 1_310_929245147.dbf _1_311_929245147.arc _1_312_929245147.arc_1_313_929245147.arcarchivelog_20180305 SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ORCL/archivelog/_1_313_929245147.arcSYS@orcl>
方法二： alter system set log_archive_dest ='/u01/app/oracle/fast_recovery_area/ORCL/archivelog' ;
SYS@orcl> alter system set log_archive_dest ='/u01/app/oracle/fast_recovery_area/ORCL/archivelog' ; alter system set log_archive_dest ='/u01/app/oracle/fast_recovery_area/ORCL/archivelog' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST SYS@orcl> alter system reset log_archive_dest_1; System altered. SYS@orcl> alter system reset log_archive_dest_2; System altered. SYS@orcl> show parameter recover ; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 2G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SYS@orcl> alter system set db_recovery_file_dest=''; System altered. SYS@orcl> show parameter recover ; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 2G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SYS@orcl> shutdown immediate;; SP2-0717: illegal SHUTDOWN option SYS@orcl> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl> startup ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 377488892 bytes Database Buffers 138412032 bytes Redo Buffers 5861376 bytes Database mounted. Database opened. SYS@orcl> alter system set log_archive_dest ='/u01/app/oracle/fast_recovery_area/ORCL/archivelog' ; System altered. SYS@orcl> alter system switch logfile; System altered. SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ORCL/archivelog _1_313_929245147.arc 2016_11_30 2016_12_02 2016_12_08 2017_12_17 2018_02_27 2018_04_24 2018_04_27 2018_04_29 2018_05_20 _1_314_929245147.arc 2016_12_01 2016_12_07 2017_12_16 2017_12_21 2018_04_23 2018_04_26 2018_04_28 2018_04_30 SYS@orcl>SYS@orcl> SYS@orcl> alter system set log_archive_duplex_dest='/home/oracle/oracle_system_files_back/archivelog'; System altered. SYS@orcl> alter system switch logfile; System altered. SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ORCL/archivelog _1_313_929245147.arc _1_315_929245147.arc 2016_12_01 2016_12_07 2017_12_16 2017_12_21 2018_04_23 2018_04_26 2018_04_28 2018_04_30 _1_314_929245147.arc 2016_11_30 2016_12_02 2016_12_08 2017_12_17 2018_02_27 2018_04_24 2018_04_27 2018_04_29 2018_05_20 SYS@orcl> ho ls /home/oracle/oracle_system_files_back/archivelog 1_310_929245147.dbf _1_311_929245147.arc _1_312_929245147.arc _1_313_929245147.arc _1_315_929245147.arc archivelog_20180305 SYS@orcl> SYS@orcl>
查看归档目的地的相关状态信息
SYS@orcl> col dest_name format a20; SYS@orcl> col destination format a30; SYS@orcl> select dest_name,status,archiver,destination,log_sequence,reopen_secs,transmit_mode,process from v$archive_dest; DEST_NAME STATUS ARCHIVER DESTINATION LOG_SEQUENCE REOPEN_SECS TRANSMIT_MOD PROCESS -------------------- --------- ---------- ------------------------------ ------------ ----------- ------------ ---------- LOG_ARCHIVE_DEST_1 VALID ARCH USE_DB_RECOVERY_FILE_DEST 319 300 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_2 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_3 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_4 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_5 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_6 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_7 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_8 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_9 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_10 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_11 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH DEST_NAME STATUS ARCHIVER DESTINATION LOG_SEQUENCE REOPEN_SECS TRANSMIT_MOD PROCESS -------------------- --------- ---------- ------------------------------ ------------ ----------- ------------ ---------- LOG_ARCHIVE_DEST_12 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_13 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_14 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_15 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_16 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_17 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_18 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_19 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_20 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_21 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_22 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH DEST_NAME STATUS ARCHIVER DESTINATION LOG_SEQUENCE REOPEN_SECS TRANSMIT_MOD PROCESS -------------------- --------- ---------- ------------------------------ ------------ ----------- ------------ ---------- LOG_ARCHIVE_DEST_23 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_24 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_25 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_26 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_27 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_28 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_29 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_30 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH LOG_ARCHIVE_DEST_31 INACTIVE ARCH 0 0 SYNCHRONOUS ARCH 31 rows selected. SYS@orcl>
停用 log_archive_dest_state_2
---停用 SYS@orcl> alter system set log_archive_dest_state_2=defer; System altered. SYS@orcl> show parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string DEFER ---该路径显示为 defer 停用状态 log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable --启用 SYS@orcl> alter system set log_archive_dest_state_2=enable; System altered. SYS@orcl> show parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string ENABLE log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable SYS@orcl>
手动归档：
闪回恢复区的开启和关闭：
---查看闪回恢复区信息 SYS@orcl> show parameter recover; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string ---设置闪回恢复区的目录 db_recovery_file_dest_size big integer 2G ---设置闪回恢复区的大小 db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SYS@orcl>启用闪回恢复区
SYS@orcl> SYS@orcl> show parameter recover; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 2G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ orcl ORCL SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ORCL archivelog onlinelog SYS@orcl> ho mkdir /u01/app/oracle/fast_recovery_area/ORCL/db_recovery_file_dest SYS@orcl> ho ls /u01/app/oracle/fast_recovery_area/ORCL archivelog db_recovery_file_dest onlinelog SYS@orcl> alter system reset log_archive_duplex_dest; System altered. SYS@orcl> alter system reset log_archive_dest; System altered. SYS@orcl> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl> startup ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 377488892 bytes Database Buffers 138412032 bytes Redo Buffers 5861376 bytes Database mounted. Database opened. SYS@orcl> show parameter recover; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 2G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SYS@orcl> alter system set db_recovery_file_dest_size=3G; System altered. SYS@orcl> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCL/db_recovery_file_dest'; System altered. SYS@orcl> show parameter recover; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area/ORCL/db_recovery_file_des t db_recovery_file_dest_size big integer 3G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SYS@orcl>
开启归档日志模式
---开启归档日志： ----1：关闭数据库 SYS@orcl> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. ---开启数据库到 mount状态 SYS@orcl> startup mount; ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 377488892 bytes Database Buffers 138412032 bytes Redo Buffers 5861376 bytes Database mounted. ---开启 归档日志模式 SYS@orcl> alter database archivelog; Database altered.
SYS@orcl> ho ps -ef |grep ora_ |grep arc oracle 4171 1 0 22:01 ? 00:00:00 ora_arc0_orcl oracle 4173 1 0 22:01 ? 00:00:00 ora_arc1_orcl oracle 4176 1 0 22:01 ? 00:00:00 ora_arc2_orcl oracle 4178 1 0 22:01 ? 00:00:00 ora_arc3_orcl oracle 4217 5559 0 22:03 pts/2 00:00:00 /bin/bash -c ps -ef |grep ora_ |grep arc SYS@orcl>
关闭归档日志模式
----切换为非归档日志模式 --关闭数据库 SYS@orcl> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. --开启数据库到 mount状态 SYS@orcl> startup mount; ORACLE instance started. Total System Global Area 523108352 bytes Fixed Size 1346052 bytes Variable Size 377488892 bytes Database Buffers 138412032 bytes Redo Buffers 5861376 bytes Database mounted. ---切换数据库到非归档日志模式 SYS@orcl> alter database noarchivelog; Database altered. --打开数据库 SYS@orcl> alter database open; Database altered. --查看归档日志信息 SYS@orcl> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 307 Current log sequence 310 SYS@orcl>
执行手工归档
利用归档恢复过程
————————————————————————————————————————————————————————————————
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
