加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：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
  2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my01.dbf' size 20M
  3  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
  2      datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my02.dbf' size 20M
  3      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
  2  tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\temp001.dbf' size 10m
  3  extent management local
  4  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
  2     tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\temp002.dbf' size 10m
  3      extent management local
  4     autoallocate
  5  ;
   autoallocate
   *
第 4 行出现错误:
ORA-25139: CREATE TEMPORARY TABLESPACE 的选项无效

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

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
  2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\myundo02.dbf' size 5m
  3  uniform size 10k
  4  ;
uniform size 10k
*
第 3 行出现错误:
ORA-30024: CREATE UNDO TABLESPACE 的说明无效

2.5创建大文件
SQL>  create bigfile tablespace big01
  2   datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\myundo02.dbf' size 5m
  3   extent management local
  4   segment space management auto
  5  ;
表空间已创建。
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
  2   datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\big04.dbf' size 7m
  3   extent management local
  4    manual
  5  ;
  manual
  *
第 4 行出现错误:
ORA-02180: 无效的 CREATE TABLESPACE 选项

 -------------------------------------------------------------------------------------
默认为本地自动段，不支持数据字典和段手动，但下面3 and 4之间没说明就应该是默认manual 啊，但为什么还创建成功了呢？
SQL>  create bigfile tablespace big03
  2   datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\big03.dbf' size 6m
  3   extent management local
  4  ;
表空间已创建。
 -------------------------------------------------------------------------------------
  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  ;
数据库已更改。

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 | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有