Oracle表空间操作

ustbwang

　　转载至红黑联盟--http://www.iyunv.com/database/201402/278907.html
　　(1) 数据文件查询
　　Select FILE_NAME from dba_data_files;
　　SQL> select file_name fromdba_data_files;
　　FILE_NAME
　　--------------------------------------------------------------------------------
　　/data/EDWPRD/users01.dbf
　　/data/EDWPRD/sysaux01.dbf
　　/data/EDWPRD/undotbs01.dbf
　　/data/EDWPRD/system01.dbf
　　/data/EDWPRD/user_data.dbf
　　#查看tempfile
　　SQL> select status,enabled, name,bytes/1024/1024 file_size from v_$tempfile;
　　#查看临时文件使用情况
　　SQL> select * from GV_$TEMP_SPACE_HEADER
　　(2)查看现有表空间
　　SQL> select tablespace_namename,block_size,contents from dba_tablespaces;
　　NAME BLOCK_SIZE CONTENTS
　　------------------------------ -------------------
　　SYSTEM 8192 PERMANENT
　　UNDOTBS1 8192 UNDO
　　SYSAUX 8192 PERMANENT
　　TEMP 8192 TEMPORARY
　　USERS 8192 PERMANENT
　　USER_DATA 8192 PERMANENT
　　USER_TEMP 8192 TEMPORARY
　　#查看表空间剩余容量
　　select distinct(tablespace_name) fromdba_free_space;
　　TABLESPACE_NAME
　　------------------------------
　　UNDOTBS1
　　SYSAUX
　　USERS
　　SYSTEM
　　USER_DATA
　　SQL> select * from dba_free_space;
　　SQL> select t.tablespace_name,round(sum(bytes/(1024*1024)),0) ts_size
　　2 from dba_tablespaces t,dba_data_files d where t.tablespace_name = d.tablespace_name
　　3 group by t.tablespace_name;
　　TABLESPACE_NAME TS_SIZE
　　------------------------------ ----------
　　SYSAUX 250
　　UNDOTBS1 25
　　USERS 5
　　SYSTEM 480
　　USER_DATA 50
　　SQL> selecttablespace_name,bytes/1024/1024 cur_size,user_bytes/1024/1024 asuser_bytes,status,online_status from dba_data_files;
　　TABLESPACE_NAME CUR_SIZE USER_BYTESSTATUS ONLINE_
　　------------------------------ -------------------- --------- -------
　　USERS 5 4.9375 AVAILABLE ONLINE
　　SYSAUX 250 249.9375 AVAILABLE ONLINE
　　UNDOTBS1 25 24.9375 AVAILABLE ONLINE
　　SYSTEM 480 479.9375 AVAILABLE SYSTEM
　　USER_DATA 50 49.9375 AVAILABLE ONLINE
　　(3)和数据有关的字典
　　数据库：
　　V$DATABASE 同义词 V_$DATABASE,记录系统的运行情况；
　　表空间：
　　DBA_TABLESPACES 记录系统表空间的基本信息；
　　DBA_DATA_FILES 记录系统数据文件及表空间的基本信息；
　　DBA_FREE_SPACE 记录系统表空间的剩余空间的信息；
　　控制文件：
　　V$CONTROLFILE 记录系统控制文件的路径信息；
　　V$PARAMETER 记录系统各参数的基本信息；
　　v$CONTROLFILE_RECORD_SECTION 记录系统控制运行的基本信息；
　　数据文件：
　　DBA_DATA_FILES 记录系统数据文件及表空间的基本信息；
　　v$DATAFILE 记录来自控制文件的数据文件信息；
　　v$FILESTAT 记录数据文件读写的基本信息；
　　(4)查看字段
　　#查看字段
　　SQL> select distinct segment_type fromdba_segments;
　　SEGMENT_TYPE
　　------------------
　　LOBINDEX
　　INDEX PARTITION
　　TABLE PARTITION
　　NESTED TABLE
　　ROLLBACK
　　LOB PARTITION
　　LOBSEGMENT
　　INDEX
　　TABLE
　　CLUSTER
　　TYPE2 UNDO
　　(5)添加临时文件

　　alter tablespace temp add tempfile'/data/EDWPRD/temp02.dbf'>　　(6)用户及表空间
　　#查看当前用户所在表空间
　　select username,default_tablespace fromuser_users;
　　#查看当前用户的表
　　select table_name,tablespace_name fromuser_tables;
　　TABLE_NAME TABLESPACE_NAME
　　------------------------------------------------------------
　　DEPT USERS
　　EMP USERS
　　BONUS USERS
　　SALGRADE USERS
　　#查看某个表的字段
　　select * from user_tab_columns whereTable_Name='用户表';
　　(7)添加表空间
　　/*分为四步 */
　　/*第1步：创建临时表空间 */
　　create temporary tablespace user_temp
　　tempfile'D:\oracle\oradata\Oracle9i\user_temp.dbf'
　　size 50m
　　autoextend on
　　next 50m maxsize 20480m
　　extent management local;
　　/*第2步：创建数据表空间 */
　　create tablespace user_data
　　logging
　　datafile'D:\oracle\oradata\Oracle9i\user_data.dbf'
　　size 50m
　　autoextend on
　　next 50m maxsize 20480m
　　extent management local;
　　/*第3步：创建用户并指定表空间 */

　　create user username>　　default tablespace user_data
　　temporary tablespace user_temp;
　　/*第4步：给用户授予权限 */
　　grant connect,resource,dba to username;
　　#删除表空间
　　DROP TABLESPACE tablespace_name INCLUDINGCONTENTS AND DATAFILES;
　　(8)区间
　　extent是“区间”的意思
　　在oracle数据库中：extent management 有两种方式 extent management local（本地管理）; extentmanagement dictionary（数据字典管理）
　　默认的是local
　　每种也有两种大小增长方式：
　　uniform：默认为1M大小，在temp表空间里为默认的，但是不能被应用在undo表空间
　　本地管理表空间与字典管理表空间相比大大提高了管理效率和数据库性能，其优点如下：
　　1.减少了递归空间管理
　　本地管理表空间是自己管理分配，而不是象字典管理表空间需要系统来管理空间分配，本地表空间是通过在表空间的每个数据文件中维持一个位图来跟踪在此文件中块的剩余空间及使用情况。并及时做更新。这种更新只对表空间的额度情况做修改而不对其他数据字典表做任何update操作，所以不会产生任何回退信息，从而大大减少了空间管理，提高了管理效率。同时由于本地管理表空间可以采用统一大小分配方式(UNIFORM),因此也大大减小了空间管理，提高了数据库性能。
　　2.系统自动管理extents大小或采用统一extents大小
　　本地管理表空间有自动分配(AUTOALLOCATE)和统一大小分配(UNIFORM)两种空间分配方式，自动分配方式(AUTOALLOCATE)是由系统来自动决定extents大小，而统一大小分配(UNIFORM)则是由用户指定extents大小。这两种分配方式都提高了空间管理效率。
　　3.减少了数据字典之间的竞争
　　因为本地管理表空间通过维持每个数据文件的一个位图来跟踪在此文件中块的空间情况并做更新，这种更新只修改表空间的额度情况，而不涉及到其他数据字典表，从而大大减少了数据字典表之间的竞争，提高了数据库性能。
　　4.不产生回退信息
　　因为本地管理表空间的空间管理除对表空间的额度情况做更新之外不修改其它任何数据字典表，因此不产生回退信息，从而大大提高了数据库的运行速度。
　　5.不需合并相邻的剩余空间
　　因为本地管理表空间的extents空间管理会自动跟踪相邻的剩余空间并由系统自动管理，因而不需要去合并相邻的剩余空间。同时，本地管理表空间的所有extents还可以具有相同的大小，从而也减少了空间碎片。
　　6.减少了空间碎片
　　7.对临时表空间提供了更好的管理