- 表空间结构
SQL> 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)
- 查看表空间基本信息
SQL> select tablespace_name,contents from dba_tablespaces; SYSTEM PERMANENT SYSAUX PERMANENT UNDOTBS1 UNDO TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT
- 动态性能视图v$tablespace结构
SQL> desc v$tablespace; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ TS# NUMBER NAME VARCHAR2(30) INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3) BIGFILE VARCHAR2(3) FLASHBACK_ON VARCHAR2(3) ENCRYPT_IN_BACKUP VARCHAR2(3)
- 从动态性能视图中查询表空间信息
SQL> select ts#,name from v$tablespace; TS# NAME ---------- -------------------- 0 SYSTEM 1 SYSAUX 2 UNDOTBS1 4 USERS 3 TEMP 6 EXAMPLE 8 APP1 7 rows selected.
- 创建一个表空间
CREATE TABLESPACE EXPIMP DATAFILE '/u01/oradata/expimp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
- 如何将一个数据库的表空间快速的转移到另外的一个数据库
- 使用Transportable TS Modes
- Datafile与Tablespace之间的关系类似于Backup sets 与 Backup piece之间的关系.
- 删除一个表空间，同时删除数据文件
SQL> drop tablespace mytbs including contents and datafiles; Tablespace dropped.
- 查看用户与表空间对应信息
SQL> select username,default_tablespace from dba_users order by 1; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ ANONYMOUS SYSAUX APEX_030200 SYSAUX APEX_PUBLIC_USER USERS APPQOSSYS SYSAUX ARCER USERS BI USERS CTXSYS SYSAUX DBSNMP SYSAUX DIP USERS EXFSYS SYSAUX FBDA_ADMIN FBDA_TBS USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ FLOWS_FILES SYSAUX HR USERS IX USERS MDDATA USERS MDSYS SYSAUX OE USERS OLAPSYS SYSAUX ORACLE_OCM USERS ORDDATA SYSAUX ORDPLUGINS SYSAUX ORDSYS SYSAUX USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ OUTLN SYSTEM OWBSYS SYSAUX OWBSYS_AUDIT SYSAUX PM USERS SCOTT USERS SH USERS SI_INFORMTN_SCHEMA SYSAUX SPATIAL_CSW_ADMIN_USR USERS SPATIAL_WFS_ADMIN_USR USERS SYS SYSTEM SYSTEM SYSTEM USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ U2 TAB_U2 U3 TAB_U3 WMSYS SYSAUX XDB SYSAUX XS$NULL USERS 38 rows selected.
- 查看数据文件信息
SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/oradata/poli/system01.dbf SYSTEM /u01/oradata/poli/sysaux01.dbf ONLINE /u01/oradata/poli/undotbs01.dbf ONLINE /u01/oradata/poli/users01.dbf ONLINE /u01/oradata/poli/example01.dbf ONLINE
- 查看数据文件与表空间对应信息（方式一）
SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME ----------------------------------------------------------------- ------------------------------ +DATA/racdb/datafile/system.262.818420641 SYSTEM +DATA/racdb/datafile/sysaux.263.818420665 SYSAUX +DATA/racdb/datafile/undotbs1.264.818420681 UNDOTBS1 +DATA/racdb/datafile/undotbs2.266.818420707 UNDOTBS2 +DATA/racdb/datafile/users.267.818420715 USERS
- 查看表与表空间对应关系
SQL> select table_name,tablespace_name,cluster_name from user_tables; TABLE_NAME TABLESPACE_NAME CLUSTER_NAME ------------------------------ ------------------------------ ------------------------------ REGIONS EXAMPLE LOCATIONS EXAMPLE EMPLOYEES EXAMPLE COUNTRIES JOBS EXAMPLE DEPARTMENTS EXAMPLE JOB_HISTORY EXAMPLE 7 rows selected.
- 查看数据库文件与表空间数据文件对应关系（方式二）
SQL> SELECT t.name tablespace,f.name datafile FROM v$tablespace t,v$datafile f WHERE t.ts# = f.ts# ORDER BY t.name; TABLESPACE DATAFILE ------------------------------ ------------------------------------ EXAMPLE /u01/oradata/poli/example01.dbf SYSAUX /u01/oradata/poli/sysaux01.dbf SYSTEM /u01/oradata/poli/system01.dbf UNDOTBS1 /u01/oradata/poli/undotbs01.dbf USERS /u01/oradata/poli/users01.dbf
- 如何缩小数据文件尺寸大小
alter database datafile 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF' resize 18M;
- RAC环境中，创建表空间（方法一）
SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME ----------------------------------------------------------------- ------------------------------ +DATA/racdb/datafile/system.262.818420641 SYSTEM +DATA/racdb/datafile/sysaux.263.818420665 SYSAUX +DATA/racdb/datafile/undotbs1.264.818420681 UNDOTBS1 +DATA/racdb/datafile/undotbs2.266.818420707 UNDOTBS2 +DATA/racdb/datafile/users.267.818420715 USERS SQL> create tablespace dg_tbs datafile size 5m; Tablespace created. SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME ----------------------------------------------------------------- ------------------------------ +DATA/racdb/datafile/system.262.818420641 SYSTEM +DATA/racdb/datafile/sysaux.263.818420665 SYSAUX +DATA/racdb/datafile/undotbs1.264.818420681 UNDOTBS1 +DATA/racdb/datafile/undotbs2.266.818420707 UNDOTBS2 +DATA/racdb/datafile/users.267.818420715 USERS +DATA/racdb/datafile/dg_tbs.284.818763765 DG_TBS 6 rows selected.
- RAC环境中，创建表空间（方法二）
SQL> create tablespace mis datafile '+DATA' size 1024M; Tablespace created.
- RAC环境中，删除表空间
drop tablespace MIS INCLUDING CONTENTS AND DATAFILES;
- 删除表空间，官方文档
--Dropping a Tablespace: Example The following statement drops the tbs_01 tablespace and drops all referential integrity constraints that refer to primary and unique keys inside tbs_01: DROP TABLESPACE tbs_01 INCLUDING CONTENTS CASCADE CONSTRAINTS; --Deleting Operating System Files: Example The following example drops the tbs_02 tablespace and deletes all associated operating system data files: DROP TABLESPACE tbs_02 INCLUDING CONTENTS AND DATAFILES;
- 创建bigfile tablespace（在大文件 数据表空间下永远只能拥有一个数据文件，所以其数据文件号为0）
SQL> create bigfile tablespace bigfile_tbs datafile '/u01/oradata/DB234/bigfile1.dbf' size 10m autoextend on; Tablespace created.
- 为用户HR分配在表空间bigfile_tbs上的配额.
SQL> alter user HR quota 2m on bigfile_tbs; User altered.
alter database datafile 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF' autoextend on next 200m maxsize 500m;
- 查看表空间自动扩展情况
SQL> col file_name format a60 SQL> select file_name,bytes,autoextensible,maxbytes from dba_data_files where tablespace_name='APP_DATACENTER'; FILE_NAME BYTES AUT MAXBYTES ------------------------------------------------------------ ---------- --- ---------- D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATACENTER_01.DBF 209715200 YES 524288000 SQL>
- 查询指定表空间下，用户与表的对应信息
SQL> select owner,table_name from all_tables where tablespace_name='USERS'; OWNER TABLE_NAME ------------------------------ ------------------------------ SCOTT DEPT SCOTT EMP SCOTT BONUS SCOTT SALGRADE SCOTT T OE PRODUCT_REF_LIST_NESTEDTAB OE SUBCATEGORY_REF_LIST_NESTEDTAB 7 rows selected. SQL>
- 设置tablespace -> online -> offline
SQL> select ts#,name from v$tablespace; TS# NAME ---------- ------------------------------ 0 SYSTEM 1 SYSAUX 2 UNDOTBS1 4 USERS 3 TEMP 6 EXAMPLE 7 APP_FGPS 8 RMAN_TS 9 MYUNDOTBS 11 TESTTBS01 12 TESTTBS02 TS# NAME ---------- ------------------------------ 13 TS_PERF 12 rows selected. SQL> alter tablespace testtbs01 offline; Tablespace altered. SQL> alter tablespace testtbs01 online; Tablespace altered. SQL>