Oracle11g温习-第九章：表空间和数据文件管理
2013年4月27日 星期六
10:37
|
1、tablespace 功能:从逻辑上简化数据库的管理 |
|||
|
2、tablespace 概述 |
|||
system表空间不能 offline 和drop。
查看与表空间有关的视图
V$TABLESPACE V$DBA_DATA_FILES V$DATABASE_PROPERTIES |
|||
|
表空间的管理 |
|||
|
1、创建表空间 |
|||
|
|
|||
|
2、表空间对free extents（空闲区）的管理 |
|||
|
1）9i 以前通过dictionary管理，性能不好，容易产生碎片，通过uet$ 和 fet$ 管理 2）9i以后采用local 管理，在表空间上建立bitmap ，表空间自行管理 用0,1来表示free extents，oracle 10g 默认本地管理
|
|||
|
3、数据字典管理通过fet$(记录空闲extent）和uet$(记录已使用extent） |
|||
|
SQL> select a.ts#,a.name,b.file#,b.name "File_name" ,c.block#,d.extent_management from v$tablespace a,v$datafile b,fet$ c,dba_tablespaces d where a.ts#=b.ts# and a.ts#=c.ts# and a.name=d.tablespace_name;
TS# NAME FILE# File_name BLOCK# EXTENT_MAN ---------- ------------------------------ ---------- -------------------------------------------------- ---------- ---------- 0 SYSTEM 1 /u01/app/oracle/oradata/cuug/system01.dbf 21509 DICTIONARY 9 CUUG 9 /u01/app/oracle/oradata/cuug/cuug01.dbf 2 DICTIONARY
SQL> select ts#,file#,block# from uet$； |
|||
|
4、local manager 对extent size 分配有两种管理方式：autoallocate （自动分配，默认），uniform size 统一大小 |
|||
|
采用uniform 建立表空间
SQL> create tablespace lx01 datafile '/u01/app/oracle/oradata/cuug/lx01.dbf' size 10m extent management local uniform size 1m;
Tablespace created.
SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MANAGEMENT -------------------------------------------------- ------------------- ---------------- ------------------------------ SYSTEM 64 LOCAL（自动分配，默认） RTBS 64 LOCAL SYSAUX 64 LOCAL TEMP 1024 1024 LOCAL USERS 64 LOCAL TEXT 64 LOCAL LX01 1024 1024 LOCAL（统一大小）
11 rows selected.
————inittial_extent 第一个extent 的大小 ————next_extent 第二个extent的大小 |
|||
|
5、数据字典管理表空间 |
|||
|
SQL> create tablespace cuug datafile '/u01/app/oracle/oradata/cuug/cuug.dbf' size 10m extent management dictionary default storage (initial 1m next 1m pctincrease 50);
Tablespace created.
SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENT,pct_increase from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MAN PCT_INCREASE ------------------------------ ------------------- ---------------- ---------- ------------ SYSTEM 16 16 DICTIONARY 50 RTBS 64 LOCAL SYSAUX 64 LOCAL TEMP 1024 1024 LOCAL 0 USERS 4096 4096 LOCAL 0 EXAMPLE 1024 1024 LOCAL 0 INDX 64 LOCAL OLTP 2048 2048 LOCAL 0 TEST 64 LOCAL CUUG 40 40 DICTIONARY 50 LX01 1024 1024 LOCAL 0 CUUG 1024 1024 DICTIONARY 50 -----------initial 初始化区（第一个extent）， next 第二个extent ， pctincrease 从第三个extent 开始，比上一个extent增加的百分比。 |
|||
|
UNDO 表空间 |
|||
|
undo tablespace 存放undo数据块，可以建立多个undo 表空间，但处于active状态只有一个，active 状态的undo tablespace是不能offline 和drop。 如果未建立undo tablespace 。 oracle 使用system undo segment。（如果没有建立undo表空间，就默认用system表空间作为undo表空间，这是很不好的）
SQL> show parameter undo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string rtbs
SQL> select tablespace_name,status,contents,logging from dba_tablespaces;
TABLESPACE STATUS CONTENTS ---------- --------------------------- --------------------------- SYSTEM ONLINE PERMANENT RTBS ONLINE UNDO SYSAUX ONLINE PERMANENT TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT TEXT ONLINE PERMANENT LX01 ONLINE PERMANENT
7 rows selected.
SQL> select tablespace_name,status,contents,logging from dba_tablespaces;
TABLESPACE STATUS CONTENTS ---------- --------------------------- --------------------------- SYSTEM ONLINE PERMANENT RTBS ONLINE UNDO SYSAUX ONLINE PERMANENT TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT TEXT ONLINE PERMANENT LX01 ONLINE PERMANENT UNDOTBS ONLINE UNDO
SQL> show parameter undo;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string rtbs
SQL> select TABLESPACE_NAME,STATUS,CONTENTS,logging from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS ------------------------------ --------- --------- SYSTEM ONLINE PERMANENT RTBS ONLINE UNDO SYSAUX ONLINE PERMANENT TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT EXAMPLE ONLINE PERMANENT INDX ONLINE PERMANENT OLTP ONLINE PERMANENT TEST ONLINE PERMANENT CUUG ONLINE PERMANENT LX01 ONLINE PERMANENT CUUG ONLINE PERMANENT UNDO_TBS01 ONLINE UNDO
|
|||
|
临时表空间 |
|||
|
temporary tablespace 用于排序，可以建立多个临时表空间，但默认的临时表空间也只能有一个，default temporary tablespace不能offline和drop。如果未指定默认的临时表空间oracle 将会使用system作为临时表空间。 不可以存放永久对象 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/cuug/temp01.dbf' size 100m reuse; Tablespace altered.
SQL> select file_id,file_name,tablespace_name from dba_temp_files;
TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 1 /u01/app/oracle/oradata/cuug/temp01.dbf TEMP
SQL> select file#,name ,bytes/1024/1024 from v$tempfile;
FILE# NAME BYTES/1024/1024 ---------- -------------------------------------------------- --------------- 1 /u01/app/oracle/oradata/cuug/temp01.dbf 100
SQL> select file#,name from v$tempfile;
FILE# NAME ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/cuug/temp01.dbf 2 /u01/app/oracle/oradata/cuug/tmp01.dbf
------------查看默认的临时表空间 SQL> select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ------------------------------ -------------------------------------------------- DICT.BASE 2 dictionary base tables version # DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace DBTIMEZONE -04:00 DB time zone DEFAULT_TBS_TYPE SMALLFILE Default tablespace type NLS_LANGUAGE AMERICAN Language 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 10.2.0.1.0 RDBMS version for NLS parameters GLOBAL_DB_NAME CUUG Global database name EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
-----------用户指定临时表空间 SQL> alter user scott temporary tablespace tmp01;
User altered. -----切换默认的临时表空间 SQL> alter database default temporary tablespace tmp01;
Database altered.
SQL> select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ------------------------------ -------------------------------------------------- DICT.BASE 2 dictionary base tables version # DEFAULT_TEMP_TABLESPACE TMP01 Name of default temporary tablespace DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace DBTIMEZONE -04:00 DB time zone DEFAULT_TBS_TYPE SMALLFILE Default tablespace type NLS_LANGUAGE AMERICAN Language 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 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 10.2.0.1.0 RDBMS version for NLS parameters GLOBAL_DB_NAME CUUG Global database name EXPORT_VIEWS_VERSION 8 Export views revision # --------------建立临时表空间组 SQL> alter tablespace temp tablespace group tmpgrp;
Tablespace altered.
SQL> alter tablespace tmp01 tablespace group tmpgrp;
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TMPGRP TEMP TMPGRP TMP01
------------将临时表空间组设置为默认临时表空间，可以实现负载均衡 SQL> alter database default temporary tablespace tmpgrp;
Database altered.
SQL> select * from database_properties;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ------------------------------ -------------------------------------------------- DICT.BASE 2 dictionary base tables version # DEFAULT_TEMP_TABLESPACE TMPGRP Name of default temporary tablespace DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace DBTIMEZONE -04:00 DB time zone DEFAULT_TBS_TYPE SMALLFILE Default tablespace type NLS_LANGUAGE AMERICAN Language 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 10.2.0.1.0 RDBMS version for NLS parameters GLOBAL_DB_NAME CUUG Global database name EXPORT_VIEWS_VERSION 8 Export views revision #
------------查看临时表空间信息 SQL> select file#,name,bytes from v$tempfile
FILE# NAME BYTES ---------- -------------------------------------------------- ---------- 1 /u01/app/oracle/oradata/prod/temp01.dbf 20971520 2 /u01/app/oracle/oradata/prod/tmp01.dbf 10485760 3 /u01/app/oracle/oradata/prod/tmp02.dbf 10485760
SQL> desc dba_temp_files;
Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME NOT NULL VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS CHAR(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER
|
|||
|
表空间处于read only 状态，不可以做dml 操作，但可以删除对象。 |
|||
|
SQL> create table t01 (id int) tablespace cuug;
Table created.
|
|||
|
表空间脱机offline（在表空间被破坏，需要recover） |
|||
|
1）system表空间不能offline ，正在使用的undo 不能offline，默认temporary tablespace不能offline 2）表空间脱机时会在tablespace上生成检查点，下次online 不需要做恢复操作。
SQL> alter tablespace system offline; alter tablespace system offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary 【 system表空间不可以offline】
SQL> alter tablespace undo_tbs01 offline;
alter tablespace undo_tbs01 offline * ERROR at line 1: ORA-30042: Cannot offline the undo tablespace
——offline immediate 立刻offline 不生成检查点，下次online需要recover SYS @ orcl >alter tablespace users offline;
Tablespace altered.
SYS @ orcl >select file#,name ,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ------------------ 1 /u01/app/oracle/oradata/orcl/system01.dbf 175912 2 /u01/app/oracle/oradata/orcl/undotbs01.dbf 175912 3 /u01/app/oracle/oradata/orcl/sysaux01.dbf 175912
SYS @ orcl >alter tablespace users online;
Tablespace altered.
表空间 不需要
SQL> alter tablespace tests online;
alter tablespace tests online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/test01.dbf'
SQL> recover tablespace tests;
Media recovery complete.
SQL> alter tablespace tests online;
Tablespace altered.
SQL> archive log list;
Database log mode Archive Mode Automatic archival Enabled Archive destination /disk1/arch/prod Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8
-------对于非归档模式脱机：alter tablespace xxx offline drop； 调整表空间的尺寸（表空间的大小和它的数据文件对应）
1）自动 autoextend 扩展 2）resize datafile 大小 3）增加表空间数据文件
1）自动扩展配置 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT ---------- -------------------------------------------------- ------------------------------ --- 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 9 /u01/app/oracle/oradata/cuug/cuug01.dbf CUUG NO 11 /u01/app/oracle/oradata/cuug/cuug.dbf CUUG NO 7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP NO 6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX NO 5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE YES 4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS NO 8 /u01/app/oracle/oradata/cuug/test01.dbf TEST NO 10 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 12 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDO_TBS01 NO
12 rows selected.
数据文件 改变大小
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size ---------- -------------------------------------------------- ------------------------------ --- ---------- 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 300 9 /u01/app/oracle/oradata/cuug/cuug01.dbf CUUG NO 10 11 /u01/app/oracle/oradata/cuug/cuug.dbf CUUG NO 10 7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP NO 48 6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX NO 40 5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE YES 400 4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 48 3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 100 2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS NO 100 8 /u01/app/oracle/oradata/cuug/test01.dbf TEST YES 50 10 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10 12 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDO_TBS01 NO 10 13 /u01/app/oracle/oradata/cuug/test02.dbf TEST NO 10
13 rows selected. |
|||
|
迁移表空间数据文件： |
|||
|
1） open 状态（将表空间脱机——alter tablespace 表空间名 rename datafile ‘’to ‘’——将表空间online）
SQL> select file_id,file_name,tablespace_name,autoextensible,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUTOEXTEN size ---------- -------------------------------------------------- --------------- --------- ---------- 5 /u01/app/oracle/oradata/cuug/text01.dbf TEXT NO 100 4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 200 3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 325 2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS YES 200 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 325 6 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10
6 rows selected.
SQL> alter tablespace text offline;
Tablespace altered.
SQL> ! [oracle@solaris10 ~]$cp /u01/app/oracle/oradata/cuug/text01.dbf /disk1/oradata/cuug
SQL> alter tablespace text rename datafile '/u01/app/oracle/oradata/cuug/text01.dbf' to '/disk1/oradata/cuug/text01.dbf';
Tablespace altered.
SQL> alter tablespace text online;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUTOEXTEN size ---------- -------------------------------------------------- --------------- --------- ---------- 5 /disk1/oradata/cuug/text01.dbf TEXT NO 100 4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 200 3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 325 2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS YES 200 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 325 6 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10
2）mount 状态（正常关库——启动到mount状态——执行改名语句——起库到open状态）
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> startup mount ORACLE instance started.
SQL> ! [oracle@solaris10 ~]$cp /disk1/oradata/cuug/text01.dbf /u01/app/oracle/oradata/cuug/text01.dbf [oracle@solaris10 ~]$exit exit
SQL> alter database rename file '/disk1/oradata/cuug/text01.dbf' to '/u01/app/oracle/oradata/cuug/text01.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUTOEXTEN size ---------- -------------------------------------------------- --------------- --------- ---------- 5 /u01/app/oracle/oradata/cuug/text01.dbf TEXT NO 100 4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 200 3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 325 2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS YES 200 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 325 6 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10 |
|||
|
删除表空间 |
|||
|
SQL> DROP TABLESPACE SYSTEM;
DROP TABLESPACE SYSTEM * ERROR at line 1: ORA-01550: cannot drop system tablespace
SQL> drop tablespace cuug including contents and datafiles; 删除表空间 级联 表空间下的对象 和 数据文件
Tablespace dropped.
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size ---------- -------------------------------------------------- ------------------------------ --- ---------- 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 300 11 /u01/app/oracle/oradata/cuug/cuug.dbf CUUG NO 10 7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP NO 48 6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX NO 40 5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE YES 400 4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 48 3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 100 2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS NO 100 8 /u01/app/oracle/oradata/cuug/test01.dbf TEST YES 50 10 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10 12 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDO_TBS01 NO 10 13 /u01/app/oracle/oradata/cuug/test02.dbf TEST NO 10
|
|||
|
查看表空间空闲大小 |
|||
|
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024 ------------------------------ -------------------- SYSAUX 51.0625 LX01 9 UNDO_TBS01 8.6875 CUUG 9.9921875 RTBS 18.6875 USERS 16 OLTP 46 TEST 59.875 SYSTEM 131.890625 EXAMPLE 399 INDX 39.9375 |
|||
|
建立非标准块表空间 |
|||
|
SQL> alter system set db_16k_cache_size=12m; 给非标准块添加缓存
System altered.
SQL> create tablespace tbs_16k datafile '/u01/app/oracle/oradata/tbs16k01.dbf' size 10m blocksize 16k;
Tablespace created.
SQL> select TABLESPACE_NAME,block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE ------------------------------ ---------- SYSTEM 8192 RTBS 8192 SYSAUX 8192 TEMP 8192 USERS 8192 EXAMPLE 8192 INDX 8192 OLTP 8192 TEST 8192 LX01 8192 CUUG 8192 UNDO_TBS01 8192 TMP01 8192 TBS_16K 16384 |
|||
|
大文件（bigfile）表空间（默认small file） |
|||
|
1）small file，在一个表空间可以建立多个数据文件（8k的block，最多到32G） 2）bigfile：在一个表空间只能建立一个数据文件（缺点）（8k的block，datafile maxsize 可以32T），可以简化对数据文件管理
SQL> create bigfile tablespace big_tbs datafile '/u01/app/oracle/oradata/bigtbs01.dbf' size 100m;
Tablespace created.
给大文件表空间增加多一个数据文件，会被拒绝，因为大文件表空间只能建立一个数据文件
SQL> alter tablespace big_tbs add datafile '/u01/app/oracle/oradata/bigtbs02.dbf' size 100m;
alter tablespace big_tbs * ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
SQL> select name,bigfile from v$tablespace;
NAME BIG ----------------------------------------------------- SYSTEM NO RTBS NO SYSAUX NO USERS NO EXAMPLE NO INDX NO OLTP NO TEMP NO TEST NO TBS_16K NO LX01 NO CUUG NO UNDO_TBS01 NO TMP01 NO BIG_TBS YES
|
