ORACLE 数据存储管理

1、创建表空间

语法：CREATE [UNDO]  TABLESPACE tablespace_name           

[DATAFILE datefile_spec1 [,datefile_spec2] ......    

[{MININUM EXTENT integer [k|m]    

|BLOCKSIZE integer [k]    

|logging clause | FORCE LOGGING    

|DEFAULT {data_segment_compression} storage_clause    

|[online|offline]    

|[PERMANENT|TEMPORARY]    

|extent_manager_clause    

|segment_manager_clause}]  

 

引用 http://**/viewthread.php?tid=55627

1）、CREATE TABLESPACE USERS DATAFILE '...path/user01.dbf'
   SIZE 200M REUSE AUTOEXTEND ON
   NEXT  1280K MAXSIZE  2000M
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

2）、CREATE TABLESPACE USERS DATAFILE '...path/user01.dbf'
   SIZE 200M REUSE AUTOEXTEND ON
   NEXT  1280K MAXSIZE  2000M
   EXTENT MANAGEMENT LOCAL uniform. size 1280K
   SEGMENT SPACE MANAGEMENT  AUTO ;

通过查看dba_tablespaces，uniform. size体现在ALLOCATION_TYPE列，
采用第一种方法创建表表空间时，ALLOCATION_TYPE列的值为SYSTEM，也
就是默认由系统进行extent的扩展分配，大家通常采用哪种方法创建表空间呢？

要创建本地管理表空间，在CREATE TABLESPACE命令中将EXTENT MANAGEMENT 子句中设定LOCAL关键字。然后可以有两个选择：
        设定让Oracle自动管理区——使用AUTOALLOCATE选项（缺省）；
        设定使用统一区大小管理表空间（UNIFORM. SIZE）。
如果希望表空间中包含不同大小的区的对象和许多区，则AUTOALLOCATE是最佳的选择。对于用户来说，如果空间的分配与释放的控制不是非常重要，则AUTOALLOCATE可以提供一种简化的表空间管理方式。这种方式可能会浪费一些空间，但是Oracle管理空间带来的优点可以抵消这个缺点。
另外一方面，如果希望更好地控制没有使用的空间，并且能够准确地预言为对象分配的空间以及区的大小和数量，则UNIFORM是一个好的选择。这个选项保证了数据库中不会有未使用的空间。
注意：当不明确设定区管理的类型，并且缺省将创建本地管理表空间，Oracle将按照如下规则决定区管理。
在CREATE TABLESPACE命令中如果没有包含DEFAULT存储子句，Oracle将创建一个本地管理自动分配（autoallocated）表空间。
在CREATE TABLESAPCE命令中如果包含了DEFAULT存储子句，Oracle将遵循以下规则：
如果设定了MINIMUM EXTENT子句，Oracle会推断MINIMUM EXTENT、INITIAL、NEXT的值是否相等以及PCTINCREASE的值是否为0，如果相等并且PCTINCREASE值为0，Oracle将创建本地管理的uniform表空间并且区大小等于INITIAL。如果MINIMUM EXTENT、INITIAL、NEXT的值不相等，或者PCTINCREASE的值不等于0，Oracle将忽略可以设定的任何区存储参数，并创建一个本地管理的autoallocated表空间。
如果没有设定MINIMUM EXTENT子句，Oracle仅推断INITAIL和NEXT的值是否相等以及PCTINCREASE的值是否为0，如果相等并PCTINCREASE的值为0，表空间是本地管理和uniform。否则，表空间将是本地管理和autoallocated。

以下的示例命令将创建名为lmtbsb01的表空间并设定AUTOALLOCATED：
CREATE TABLESPACE lmtbs ‘/u02/oracle/data/ltbsb01.dbf’ SIZE 50M EXTENT MANAGEMNET LOCAL AUTOALLOCATE ;
AUTOALLOCATE将导致由系统管理表空间，并使用最小的区大小——64K。在自动分配的表空间中，对象空间的增长按照initial大小自动增长。由于在字典管理表空间中最小的区大小为2个blocks，但是，在自动分配的本地管理表空间中，最小的对象大小为64K。
作为可以选择的，表空间也可以通过设定UNIFORM创建。如果UNIFORM. SIZE被设定，表空间将由uniform. size管理，缺省的SIZE为1M。

2、数据表的创建

         如果是流水表建议根据日期做成分区表，分区后在做检索是可以oracle会自动进行分区裁剪，可以缩小查询量，提高效率。

      9i以后建议使用本地管理的表空间，这样一般不再显式的指定initial,next,pctincrease这些参数，由oracle自动管理即可。如果这张表经常有大量的更新操作建议将pctfree设大一些，这个值大了可以保证一个block中有足够的剩余空间用于后续的dml操作，尽量避免产生行迁移。pctused一般不需要太关心，使用默认值即可，这个参数的含义是:假设设的值为20，那就是说当一个block的使用率将低到20%一下以后，这个块oracle就认为该块是空闲的了，下面再有新的插入动作就会使用这个block的剩余的存储空间进行数据存储。

pctfree和pctused的使用:
较高的pctfree的值大约在20到25之间，较低的值为4或5，可用于静态表或只读表。
除非要严格地管理可用空间，否则不要设置pctused超过40或50。
一般这两个值地和不能达到90，否则会使Oarcle将更多地时间花费在处理空间利用上。下面是几个参考：
pctfree 5，pctused 40 适合于静态表或只读表。
pctfree 10，pctused 50 适合插入行后，更新活动不会增加已有行地长度地综合性OLTP系统。
pctfree 20，pctused 40 适合于插入行后，更新活动会增加已有行地长度地OLTP系统。

 

表空间讨论  http://space.itpub.net/6517/viewspace-548762

 

3、分区表的使用

1).  分区表的用途和优势：

    a 、增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用；
    b 、减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少；
    c 、维护轻松：如果需要重建表，独立管理每个分区比管理单个大表要轻松得多；
    d 、均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
    e 、改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快；
    f 、分区对用户透明，最终用户感觉不到分区的存在。

2).  使用过程中存在的问题：
     删除分区和TRUNCATE分区表中数据，会造成主键和全局索引的失效。如果数据表非常大，索引的重建也是需要花很长时间的。

3).  解决办法：
   
    首先根据应用尽量将索引修改为分区索引（个别索引可能不能修改），删除主键也修改为唯一分区索引。
    经过测试表明，如果分区字段选择合理，使用分区索引的效率比全局索引要高一些，而主键和唯一分区索引的效率大体相当，因此这样的优化还是非常值得的，如果由于优化导致个别应用效率下降，也可以通过应用的调整进行优化。
    需要特别注意的几个方面：
    a. 如果个别索引不适合使用分区索引，在执行删除分区的操作时最好增加update global indexes子句，示例如下：alter table xxx drop partition yyy update global indexes ，以保证全局索引同步更新，避免对应用造成影响。
    b. 分区的创建和删除等维护操作最好采用手工的方式在数据库相对比较空闲的时段进行，特别是分区的删除操作，由于需要释放磁盘空间并同步更新索引，容易产生一些意外。
    c. 在分区表上创建的唯一索引必须包含分区字段，否则会提示错误(ORA-14039)，这一点也需要特别注意。
    d. 在分区表上增加或者拆分分区时分区索引会同步进行更新，不需要进行索引重建和分析操作，如有必要可以动态创建分区，以满足应用的需要。
   
4).  创建分区表的实例：
CREATE TABLE EDU.TJ_RESULT_PARTITION
(
    ID            NUMBER(8)    NOT NULL,
    MSG_ID        NUMBER(8)    NOT NULL,
    AINSERVICEID  VARCHAR2(10) NOT NULL,
    STATE         NUMBER(1)    DEFAULT 0 NOT NULL,
    MSGMODE       NUMBER(1)        NULL,
    SERVICEID     VARCHAR2(10) NOT NULL,
    SRCTERMID     VARCHAR2(22) NOT NULL,
    DESCTERMID    VARCHAR2(22) NOT NULL,
    FEETERMINALID VARCHAR2(22) NOT NULL,
    SRC_MOBILE    VARCHAR2(11) DEFAULT '0' NOT NULL,
    SRC_ACCOUNTID NUMBER(8)    DEFAULT 0 NOT NULL,
    SRC_PERSONID  NUMBER(8)    DEFAULT 0 NOT NULL,
    SRC_ORGID     NUMBER(6)    DEFAULT 0 NOT NULL,
    VALIDTIME     DATE             NULL,
    ATTIME        DATE             NULL,
    FINISHDATE    DATE         DEFAULT sysdate NOT NULL
)
TABLESPACE EDUCATION
NOLOGGING
PCTFREE 10
PCTUSED 40
INITRANS 10
MAXTRANS 255
STORAGE(PCTINCREASE 0
        FREELISTS 5
        FREELIST GROUPS 2
        BUFFER_POOL KEEP)
NOPARALLEL
NOCACHE
PARTITION BY RANGE(FINISHDATE)
(
PARTITION PARTITION_200605 VALUES LESS THAN (TO_DATE(' 2006-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE EDUSPACE,
PARTITION PARTITION_200606 VALUES LESS THAN (TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE EDUSPACE,
PARTITION PARTITION_200607 VALUES LESS THAN (TO_DATE(' 2006-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE EDUSPACE,
PARTITION PARTITION_200608 VALUES LESS THAN (TO_DATE(' 2006-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE EDUSPACE,
PARTITION PARTITION_200609 VALUES LESS THAN (TO_DATE(' 2006-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE EDUSPACE,
PARTITION PARTITION_200610 VALUES LESS THAN (TO_DATE(' 2006-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE EDUSPACE,
PARTITION PARTITION_200611 VALUES LESS THAN (TO_DATE(' 2006-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE EDUSPACE,
PARTITION PARTITION_200612 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING)
/
5). 创建唯一分区索引的实例：
CREATE UNIQUE INDEX EDU.PARTITION_PRIMARY
ON EDU.TJ_RESULT_PARTITION
(ID, FINISHDATE)
  LOGGING
LOCAL ( 
  PARTITION PARTITION_200605
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200606
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200607
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200608
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200609
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200610
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200611
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200612
    LOGGING
    NOCOMPRESS
)
6). 创建普通分区索引的实例：
CREATE INDEX EDU.PARTITION_FINISHDATE
ON EDU.TJ_RESULT_PARTITION
(FINISHDATE)
  LOGGING
LOCAL ( 
  PARTITION PARTITION_200605
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200606
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200607
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200608
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200609
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200610
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200611
    LOGGING
    NOCOMPRESS, 
  PARTITION PARTITION_200612
    LOGGING
    NOCOMPRESS
)
7). 分区维护实例：
  a.  删除分区：
      ALTER TABLE EDU.TJ_RESULT_PARTITION DROP PARTITION PARTITION_200610;
  b.  增加分区：
      ALTER TABLE EDU.TJ_RESULT_PARTITION ADD  PARTITION PARTITION_200701 VALUES LESS THAN
      (TO_DATE(' 2007-02-01 00:00:00',  'SYYYY-MM-DD HH24:MI:SS',  'NLS_CALENDAR=GREGORIAN'))      
      LOGGING  NOCOMPRESS;
  c.  拆分分区：
         ALTER TABLE EDU.TJ_RESULT_PARTITION
      SPLIT PARTITiON PARTITION_200608 AT (TO_DATE('2006-08-15','YYYY-MM-DD'))
      INTO (partition PARTITION_20060801 , partition PARTITION_20060802)

 

 

8）

local索引的建法：就是针对每个分区分别建立索引
create index idx_m_send_id on sms_confirm_month(send_id)
local
(
partition idx_m_send_id_1 tablespace SMS_INDEX1,
partition idx_m_send_id_2 tablespace SMS_INDEX1,
partition idx_m_send_id_3 tablespace SMS_INDEX1,
partition idx_m_send_id_4 tablespace SMS_INDEX1,
partition idx_m_send_id_5 tablespace SMS_INDEX1,
partition idx_m_send_id_6 tablespace SMS_INDEX1,
partition idx_m_send_id_7 tablespace SMS_INDEX1,
partition idx_m_send_id_8 tablespace SMS_INDEX1,
partition idx_m_send_id_9 tablespace SMS_INDEX1,
partition idx_m_send_id_10 tablespace SMS_INDEX1,
partition idx_m_send_id_11 tablespace SMS_INDEX1,
partition idx_m_send_id_12 tablespace SMS_INDEX1
);
9）

分区索引和全局索引：
　　分区索引就是在所有每个区上单独创建索引，它能自动维护，在drop或truncate某个分区时不影响该索引的其他分区索引的使用，也就是索引不会失效，维护起来比较方便，但是在查询性能稍微有点影响。
oracle会对主键自动创建全局索引
　　如果想在主键的列上创建分区索引，除非主键包括分区键，还有就是主键建在两个或以上列上。
　　在频繁删除表的分区且数据更新比较频繁时为了维护方便避免使用全局索引。

 

       若分区表跨不同表空间，做导出、导入时目标数据库必须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间！这些表空间不一定是用户的默认表空间，只要存在即可。如果有一个不存在，就会报错！

 

相关参考地址

http://www.codesky.net/article/200805/119413.html 

http://www.codesky.net/article/200805/119413.html      组合分区

 

4、表空间的管理

--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
      D.TOT_GROOTTE_MB "表空间大小(M)",
      D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
      TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
      F.TOTAL_BYTES "空闲空间(M)",
      F.MAX_BYTES "最大块(M)"
      FROM (SELECT TABLESPACE_NAME,
      ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
      ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
   (SELECT DD.TABLESPACE_NAME,
    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
   FROM SYS.DBA_DATA_FILES DD
   GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1

--查询表空间的free space
select tablespace_name,
     count(*) as extends,
     round(sum(bytes) / 1024 / 1024, 2) as MB,
     sum(blocks) as blocks
from dba_free_space
group by tablespace_name;

--查询表空间的总容量
select
   tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;

--查询表空间使用率
select total.tablespace_name,
     round(total.MB, 2) as Total_MB,考试大论坛
     round(total.MB - free.MB, 2) as Used_MB,
     round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
     from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
  (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
   from dba_data_files
   group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;

 

4、Oracle LINUX 文件大小限制问题

      LINUX下创建一个表空间,数据文件AUTOEXTEND ON NOLIMITED,但到32GB时,就不能再增长了,需要手动的再增加一个文件。

     oracle数据文件最大值：因为oracle 的dba(data block address)是32 bits的,其中block id占了22bits,file id占了10bits,所以datafile能达到的最大size就是2^22*db_block_size也就是4194304*db_block_size,
4k的blocksize那么它的datafile max size就是16G
8k的blocksize那么它的datafile max size就是32G

    写个脚本每天查询一次表空间和数据文件的情况，自动增加表空间。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11949963/viewspace-730481/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11949963/viewspace-730481/