加载中…
个人资料
  • 博客等级:
  • 博客积分:
  • 博客访问:
  • 关注人气:
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

oracle表空间操作(1)表空间创建

(2012-05-25 23:22:48)
标签:

oracle

表空间

杂谈

分类: oracle

1.数据字典筛选
SQL>
 select tablespace_name,extent_management,allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
已选择7行。
-------------------------------------------
TABLESPACE_NAME                EXTENT_MAN STATUS    ALLOCATIO
------------------------------ ---------- --------- ---------
SYSTEM                         LOCAL      ONLINE    SYSTEM
UNDOTBS1                       LOCAL      ONLINE    SYSTEM
SYSAUX                         LOCAL      ONLINE    SYSTEM
TEMP                           LOCAL      ONLINE    UNIFORM
USERS                          LOCAL      ONLINE    SYSTEM
TABLESPCE_TEST                 LOCAL      ONLINE    SYSTEM
TABLESPCE_TEMP                 LOCAL      ONLINE    UNIFORM
已选择7行。

SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TABLESPACE_TEST.DBF

2.创建表空间
2.1创建永久表空间
SQL> create tablespace my01
  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my01.dbf' size 20M
  uniform size 128k;
表空间已创建。

当不指定uniform,默认使用autoallocate。
 SQL> create tablespace my01_1
datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my01_1.dbf' size 20M

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY01_1                         LOCAL      SYSTEM

2.2
SQL> create tablespace my02
      datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my02.dbf' size 20M
      uniform size 128k;
表空间已创建。
TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY01_1                         LOCAL      SYSTEM
MY02                           LOCAL      UNIFORM
已选择10行。

2.3 创建临时表空间
SQL> create temporary tablespace temp001
  tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\temp001.dbf' size 10m
  extent management local
  uniform size 1k;
表空间已创建。

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY01_1                         LOCAL      SYSTEM
MY02                           LOCAL      UNIFORM
TEMP001                        LOCAL      UNIFORM
已选择11行。
不用使用 autoallocate指定,如下:
SQL>  create temporary tablespace temp002
     tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\temp002.dbf' size 10m
      extent management local
     autoallocate
  ;
   autoallocate
   *
第 4 行出现错误:
ORA-25139: CREATE TEMPORARY TABLESPACE 的选项无效

2.4创建UNDO表空间
SQL> create smallfile undo tablespace myundo01
  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\myundo01.dbf' size 5m
  ;
表空间已创建。

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY01_1                         LOCAL      SYSTEM
MY02                           LOCAL      UNIFORM
TEMP001                        LOCAL      UNIFORM

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
MYUNDO01                       LOCAL      SYSTEM
已选择12行。

只能指定datafile和  extent management local。
SQL>  create smallfile undo tablespace myundo02
  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\myundo02.dbf' size 5m
  uniform size 10k
  ;
uniform size 10k
*
第 3 行出现错误:
ORA-30024: CREATE UNDO TABLESPACE 的说明无效

2.5创建大文件
SQL>  create bigfile tablespace big01
   datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\myundo02.dbf' size 5m
   extent management local
   segment space management auto
  ;
表空间已创建。
TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY01_1                         LOCAL      SYSTEM
MY02                           LOCAL      UNIFORM
TEMP001                        LOCAL      UNIFORM

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
MYUNDO01                       LOCAL      SYSTEM
BIG01                          LOCAL      SYSTEM
已选择13行。

默认为本地自动段,不支持数据字典和段手动
SQL>  create bigfile tablespace big04
   datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\big04.dbf' size 7m
   extent management local
    manual
  ;
  manual
  *
第 4 行出现错误:
ORA-02180: 无效的 CREATE TABLESPACE 选项

 -------------------------------------------------------------------------------------
默认为本地自动段,不支持数据字典和段手动,但下面3 and 4之间没说明就应该是默认manual 啊,但为什么还创建成功了呢?
SQL>  create bigfile tablespace big03
   datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\big03.dbf' size 6m
   extent management local
  ;
表空间已创建。
 -------------------------------------------------------------------------------------
  TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY01_1                         LOCAL      SYSTEM
MY02                           LOCAL      UNIFORM
TEMP001                        LOCAL      UNIFORM

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
MYUNDO01                       LOCAL      SYSTEM
BIG01                          LOCAL      SYSTEM
BIG03                          LOCAL      SYSTEM
已选择14行。

2.5.1  大文件可直接更改大小
SQL>  alter tablespace big03 resize 9m;

表空间已更改。

alter database 表空间名 AutoExtend on next 20G;
2.5.2修改默认表空间文件类型
SQL> alter database set default bigfile tablespace
  ;
数据库已更改。

2.5.3查看系统默认表空间文件类型
 SQL>select property_name  ,property_value from database_properties
where property_name='DEFAULT_TBS_TYPE';

PROPERTY_NAME     PROPERTY_VALUE
---------------------------------------------
DEFAULT_TBS_TYPE  SMALLFILE

0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有