oracle 表空间

============  查询 ===================

1.查询oracle用户的默认表空间和临时表空间
select default_tablespace, temporary_tablespace, d.username
  from dba_users d
 where d.username like '%YGJ%'
 group by default_tablespace, temporary_tablespace, d.username

 

2、查看表空间的名称及大小  
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size  
from dba_tablespaces t, dba_data_files d  
where t.tablespace_name = d.tablespace_name  
group by t.tablespace_name;  

 

3. 查询当前数据库中的所有的临时表空间

select distinct tablespace_name from dba_temp_files;

4、查看表空间物理文件的名称及大小  
select tablespace_name, file_id, file_name,  
round(bytes/(1024*1024),0) total_space  
from dba_data_files  
order by tablespace_name; 

 

5、查看表空间的使用情况  
select sum(bytes)/(1024*1024) as free_space,tablespace_name  
from dba_free_space  
group by tablespace_name;  
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,  
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"  
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C  
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;  

 

 

============  创建 =================

//创建临时表空间
create temporary tablespace ygj_temp
tempfile '/opt/oracle10g/oradata/orcl/ygj_temp.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

/创建数据表空间
create tablespace ygj_data
logging
datafile '/opt/oracle10g/oradata/orcl/ygj_data1.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建用户并指定表空间
create user atf_ygj  identified by password
default tablespace  ygj_data
temporary tablespace ygj_temp;

//给用户授予权限
grant connect,resource to username;

//以后以该用户登录，创建的任何数据库对象都属于test_temp 和test_data表空间，这就不用在每创建一个对象给其指定表空间了。

=============  移动数据到表空间  ================= 

查询需要移动的表所在的表空间

  select tt.table_name,tt.tablespace_name  from user_all_tables tt where tt.tablespace_name like '%YGJ%'

 移动表到指定表空间

 alter table employees move tablespace ygj_data;

查询要移动的索引所在的表空间

select ii.index_name,ii.table_name,ii.tablespace_name,ii.temporary from user_indexes ii where index_name like  '%EMP_PK%'
 移动（重建）索引到指定表空间

 alter index EMP_PK rebuild tablespace ygj_data;

 

============  修改 =================

重命名表空间

alter tablespace atf_ygj_data rename to ygj_data;

修改系统默认的表空间

alter database default tablespace ygj_data;

 

 

修改用户临时表空间

ALTER USER atf_ygj2 TEMPORARY TABLESPACE  ygj_temp

 

修改用户临时表空间

ALTER USER atf_ygj2 TEMPORARY TABLESPACE  ygj_temp

 

============  删除 =================

 删除表空间（只有表空间中没有任何的数据时才能删除）

drop tablespace ygj_data;

 

删除临时表空间

drop tablespace ygj_temp;

删除用户(CASCADE会把该用户的全部表等关联信息一并删除)

drop user atf_ygj2 CASCADE;

 

 

 

 

 

 ==============================

在oracle对象的重命名始终都是个麻烦的事情，这些对象主要是指表名，索引名，列名，表空间名。

 

在8i的时候提供了对表名和索引名的重命名功能：
SQL> alter table sunwg rename to sunwg01;

表已更改。

SQL> alter index ind_sunwg rename to ind_sunwg01;

索引已更改。

在9i的时候提供对表中的列的重命名功能：

SQL> alter table sunwg rename column owner to owner_1;

表已更改。

在10g的时候提供对表空间的重命名功能：

SQL> alter tablespace test rename to test01;

============================================

 

 

 

 

 

 ORACLE中，表空间是数据管理的基本方法，所有用户的对象要存放在表空间中，也就是用户有空间的使用权，才能创建用户对象．否则是不充许创建对象，因为就是想创建对象,如表,索引等，也没有地方存放,Oracle会提示:没有存储配额．

　　因此，在创建对象之前，首先要分配存储空间．
　　分配存储，就要创建表空间：
　　创建表空间示例如下：
CREATE TABLESPACE  "SAMPLE"
    LOGGING
    DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M
EXTENT    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO 
上面的语句分以下几部分：
第一:  CREATE TABLESPACE  "SAMPLE" 　创建一个名为 "SAMPLE"  的表空间.
    对表空间的命名,遵守Oracle 的命名规范就可了.
   ORACLE可以创建的表空间有三种类型:
  (1)TEMPORARY: 临时表空间,用于临时数据的存放;
创建临时表空间的语法如下:
CREATE TEMPORARY TABLESPACE "SAMPLE"......
  (2)UNDO : 还原表空间.  用于存入重做日志文件.
创建还原表空间的语法如下:
CREATE UNDO  TABLESPACE "SAMPLE"......
(3)用户表空间: 最重要,也是用于存放用户数据表空间
    可以直接写成: CREATE TABLESPACE  "SAMPLE"
TEMPORARY 和 UNDO 表空间是ORACLE 管理的特殊的表空间.只用于存放系统相关数据.
第二:   LOGGING
  有 NOLOGGING  和 LOGGING  两个选项,
      NOLOGGING:  创建表空间时,不创建重做日志.
     LOGGING 和NOLOGGING正好相反, 就是在创建表空间时生成重做日志.
用NOLOGGING时,好处在于创建时不用生成日志,这样表空间的创建较快,但是没能日志,数据丢失后,不能恢复,但是一般我们在创建表空间时,是没有数据的,按通常的做法,是建完表空间,并导入数据后,是要对数据做备份的,所以通常不需要表空间的创建日志,因此,在创建表空间时,选择  NOLOGGING,以加快表空间的创建速度.
第三: DATAFILE 用于指定数据文件的具体位置和大小.
 如: DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M
说明文件的存放位置是  'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' , 文件的大小为5M.
如果有多个文件,可以用逗号隔开:
 DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M,
    'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M
但是每个文件都需要指明大小.单位以指定的单位为准如 5M 或 500K.
对具体的文件,可以根据不同的需要,存放大不同的介质上,如磁盘阵列,以减少IO竟争.
指定文件名时,必须为绝对地址,不能使用相对地址.
第四: EXTENT  MANAGEMENT LOCAL  存储区管理方法
在Oracle 8i以前,可以有两种选择,一种是在字典中管理(DICTIONARY),另一种是本地管理(LOCAL ),从9I开始,只能是本地管理方式.因为LOCAL 管理方式有很多优点.
在字典中管理(DICTIONARY): 将数据文件中的每一个存储单元做为一条记录,所以在做DM操作时,就会产生大量的对这个管理表的Delete和Update操作.做大量数据管理时,将会产生很多的DM操作,严得的影响性能,同时,长时间对表数据的操作,会产生很多的磁盘碎片,这就是为什么要做磁盘整理的原因.
本地管理(LOCAL): 用二进制的方式管理磁盘,有很高的效率,同进能最大限度的使用磁盘. 同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。
第五:  SEGMENT SPACE MANAGEMENT  
磁盘扩展管理方法:
SEGMENT SPACE MANAGEMENT: 使用该选项时区大小由系统自动确定。由于 Oracle 可确定各区的最佳大小，所以区大小是可变的。
UNIFORM SEGMENT SPACE MANAGEMENT:指定区大小，也可使用默认值 (1 MB)。
第六: 段空间的管理方式:
AUTO: 只能使用在本地管理的表空间中. 使用LOCAL管理表空间时,数据块中的空闲空间增加或减少后，其新状态都会在位图中反映出来。位图使 Oracle 管理空闲空间的行为更加自动化，并为管理空闲空间提供了更好的性,但对含有LOB字段的表不能自动管理.
MANUAL: 目前已不用,主要是为向后兼容.
 
第七: 指定块大小. 可以具体指定表空间数据块的大小.
 
 
创建例子如下:
 1 CREATE TABLESPACE "SAMPLE"
 2      LOGGING
 3      DATAFILE 'D:\ORACLE\ORADATA\ORA92\SAMPLE.ora' SIZE 5M,
 4      'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M
 5      EXTENT MANAGEMENT LOCAL
 6      UNIFORM SEGMENT SPACE MANAGEMENT
 7*     AUTO
SQL> /
 
表空间已创建。
 
要删除表空间进,可以
 
SQL> DROP TABLESPACE SAMPLE;
 
表空间已丢弃。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/whqcfp/archive/2006/11/13/1381665.aspx

 

评论

1 楼 dolphin_ygj 2009-08-07  

ORACLE修改表空间方法 收藏
一、使用imp/exp。先导出源库，再创建新库把表空间创建好，然后再导入。（据说这样可以，前提是新的库里面不能有与源库相同名字的表空间。有待验证！）

二、使用脚本进行修改。据目前所了解，正长情况下需要修改表的空间和表的索引的空间，如果涉及到BOLB字段的表，修改的方式又不一样了！
正常情况下的修改脚本：
1.修改表的空间
alter table TABLE_NAME move tablespace TABLESPACENAME

查询当前用户下的所有表
select 'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_all_tables;

2.修改表的索引的空间
alter index INDEX_NAME rebuild tablespace TABLESPACENAME

查询当前用户下的所有索引
select 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes;

可以使用脚本执行查询的结果，这样就可以批量处理！

不正常情况即含有BLOB字段的表:
参考下面的文档

本人已经执行过，是可行的，但是不知道有没有漏掉数据库中其它对象。是否还存在有对象的表空间需要修改，需要进一步发现或有数据库帮忙讲解则不胜感激！目前的修改在项目中还没有出现问题！

在移植看注意研究了下ORACLE ALTER TABLE MOVE 的语法:

       ALTER TABLE table_name MOVE [ONLINE] tablespace_name;
通过上面的语句可以移植表到新表空间,

如果要移植LOB字典需要参考以下语法:
ALTER TABLE table_name LOB (lob_item) STORE AS [lob_segment]
      (
        TABLESPACE tablespace_name
                   (STORAGE.....)
           ENABLE|DISABLE STORAGE IN ROW
           CHUNK integer
           PCTVERSION integer
            RETENTION
            FREEPOOLS integer
            CACHE|NOCACHE|CACHE READS
           INDEX lobindexname

(TABLESPACE tablesapce_name

((STORAGE.....))
)

....

注解：

LOB (lob_item):表中的lob字段
STORE AS [lob_segment]:每个lob字段在表创建后系统都会自动单独创建一个段,可以通过这个参数手动指定一个段名
   tablespace_name:LOB字段新的存储表空间
(STORAGE.....):指定tablespace_name的存储属性
    ENABLE STORAGE IN ROW:如果设置了enable storage in row 那么oracle会自动将小于4000bytes的数据存储在行内, 这是ORACLE的默认值，对于大于4000字节的lob字段保存在lob段（同disable storage in row），在表段将保留36-84字节的控制信息。对于disable storage in row，Oracle将lob字段分开保存在lob段中，而仅仅在行位置保留20字节的指针。对于相当于disable storage in row的这部分（也就是单独保存在LOB段的这部分数据），UNDO仅仅是记录指针与相关lob索引改变，如果发生更新操作等DML操作，原始数据将保留在LOB段。

    DISABLE STORAGE IN ROW:如果DISABLE这个属性,那么lob数据会在行外存储,行内只存储该lob值得指针,而且这个属性在表
创建后只能在MOVE表时才可以被改变
    CHUNK:是一个很特别的属性,对一次LOB数据的操作(插入或更新),因该分配多少存储空间,指定的值最好是数据库块的倍数,而且指定的值不能大于表空间区间中NEXT的值, 要不然ORACLE会return一个错误,如果以前已经设置这个值了,那么在后期指定的值是不能被改变的。

storage as ( CHUNK bytes )表示对于disable storage in row的这部分，最小的LOB块的大小，必须是数据库块(DB_BLOCK_SIZE)的整数倍。一个chunk最多只保留一行LOB数据，也就是说，如果你设置了32K的CHUNK，但是如果LOB字段大小只有4K，也将占用32K的空间

storage as(cache|nocahce)表示是否允许lob段经过buffer cache并缓存。默认是nocache，表示直接读与直接写，不经过数据库的data buffer。所以，默认情况下，对于单独保存在LOB段的这部分数据，在发生物理读的时候，是直接读，如direct path read (lob)

storage as(nocache logging |nocache nologging)，logging/nologging属性只对nocache方式生效，默认是logging，如果是nologging方式，对于 保存在行外的log部分，在update等DML操作时将不记录redo日志。

    PCTVERSION integer、RETENTION:都是ORACLE用来管理LOB字段镜像数据的。在LOB 数据的更新过程中,
ORACLE没有用UNDO TABLESPACE空间，而是从LOB字段所在的表空间里划分一段空间来做镜像空间的,
这个空间的大小由PCTVERSION参数控制,默认值为10,代表划分表空间的10%作为镜像空间,
每个镜像空间的单元大小由CHUNK参数指定,pctversion可以使用在manual undo mode和automatic undo mode 环境中.
retention应用了automatic undo mode中的undo_retention通过时间来管理lob镜像空间.
pctversion和retention不能同时被指定.建议数据库在automatic undo mode下使用retention参数。
FREEPOOLS integer:给LOG segment指定free list.RAC环境下integer为实例的个数.单实例环境下为1.在automatic undo mode下oracle默认采用
FREEPOOLS来管理空闲块列表。除非我们在表的storage配置中指定了freelist groups参数.
CACHE|NOCACHE|CACHE READS:指定lob块是否在database buffer中缓存.
INDEX lobindexname (TABLESPACE tablesapce_name ((STORAGE.....):给lob列指定索引存储参数
举例:
SQL> show parameter db_create_file_dest

SQL> create tablespace test datafile size 100M autoextend off;
SQL> create table test(a varchar2(100), b clob, d blob) pctfree 10 tablespace test;

SQL> desc test
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name='TEST';

我们发现每个LOB字段单独有一个LOGSEGMENT和LOBINDEX;

SQL> set linesize 200
col table_name format a5
col column_name format a5
SELECT b.table_name,
a.segment_name,
b.index_name,
a.segment_type,
b.column_name,
a.tablespace_name,
b.chunk,
b.cache,
b.freepools,
b.pctversion,
b.retention
FROM dba_segments a,dba_lobs b
WHERE a.segment_name = b.segment_name
AND a.tablespace_name = 'TEST'
/

SQL>

从上面的结果我们可以观察到LOB字段的各个属性.
下面我们对LOB字段move到另一个表空间

SQL> create tablespace lob_test datafile size 100M autoextend off;

SQL> ALTER TABLE TEST MOVE LOB(B) STORE AS TEST_B (
TABLESPACE lob_test
DISABLE STORAGE IN ROW
CHUNK 16384
RETENTION
FREEPOOLS 1
NOCACHE);
SQL> ALTER TABLE TEST MOVE LOB(D) STORE AS TEST_D (
TABLESPACE lob_test
DISABLE STORAGE IN ROW
CHUNK 16384
RETENTION
FREEPOOLS 1
NOCACHE);
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name='TEST';

SQL> set linesize 200
col table_name format a5
col column_name format a5
SELECT b.table_name,
a.segment_name,
b.index_name,
a.segment_type,
b.column_name,
a.tablespace_name,
b.chunk,
b.cache,
b.freepools,
b.pctversion,
b.retention
FROM dba_segments a,dba_lobs b
WHERE a.segment_name = b.segment_name
AND a.tablespace_name = 'LOB_TEST'
/SQL>

在一些复杂情况下可能需要连表一起移植
alter table table_name move [tablespace_name] lob (lob_item) store as [lobsegmentname] (tablespace tablespace_name.....);
移植分区中lob
alter table table_name move partition [partition_name] lob (lob_item) store as [logsegmentname] (tablespace_name.....);
移植分区表
alter table table_name move partition [partition_name] tablespace_name lob (lob_item) store as [logsegmentname] (tablespace_name.....);
如果不需要修改lobsegmentname,可以同时移植多个列
alter table table_name move lob (lob_item1,lob_item2,lob_item3...) store as [lobsegmentname] (tablespace tablespace_name.....);



    LOB段也可以利用move来重整数据，以下的语句会将表与lob字段move到指定的表空间：

   alter table table_name move [tablespace tbs_name]

lob(lob_field1,lob_field2) store as (tablespace new_tbs_name);

如果LOB字段在分区表中，则增加partition关键字，如

   alter table table_name move [partition partname] [tablespace tbs_name]

lob(field) store as (tablespace new_tbs_name);



在数据库中合理的存储LOB列,不仅可以提升性能,而且还可以有效的管理存储空间.


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/yczz/archive/2009/05/27/4220972.aspx