oracle创建表空间

转至：https://liujianshiwo.iteye.com/blog/1988094

不同于mysql有多个数据库， oracle新增了表空间和用户的概念，表空间是虚拟的，并且只有一个数据库，为了方便维护，一般一个表空间上只创建一个用户；

这里的用户名类似于mysql的数据库名，oracle的用户名.表名等价于mysql的数据库名.表名；因此在写兼容mysql和oracle的语句时可以用到。

oracle安装完数据库的一般流程：先建表空间（分区）–>再建用户（分配相应的表空间和用户权限）–>登陆用户，建表

1. 创建表空间
   create tablespace xin_datafile
   datafile ‘C:\kingstar\orcltb\xin_tb1.dbf’ size 500M --存储地址 初始大小500M
   autoextend on next 10M maxsize 200M --每次扩展10M，无限制扩展 unlimited
   extent management local autoallocate
   segment space management auto;

–创建临时表空间
create temporary tablespace user_temp
tempfile ‘D:\oracle\oradata\Oracle9i\user_temp.dbf’ size 50m
autoextend on next 50m maxsize 20480m
extent management local;

–删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

–表空间属性

每块的字节大小：8192

表空间自增量：increment_by8192 byte ; 换算： increment_by8192/1024/1024 M 或者 increment_by/128M

注意*：若表空间初始化大小为500M,阀值为1G，自增量为300M，自增一次后的表空间大小为800M，不会受自动扩展容量的限制，最终会扩展为与阀值相等。

–查询表空间信息

select * from dba_data_files t order by tablespace_name;

–建立UNDO表空间

CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE ‘/oracle/oradata/db/UNDOTBS02.dbf’ SIZE 50M
#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

–改变表空间状态

1.使表空间脱机
ALTER TABLESPACE game OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项
ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空间联机
ALTER TABLESPACE game ONLINE;

3.使数据文件脱机
ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机
ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读
ALTER TABLESPACE game READ ONLY;

6.使表空间可读写
ALTER TABLESPACE game READ WRITE;

–扩展表空间

首先查看表空间的名字和所属文件
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

1.增加数据文件
ALTER TABLESPACE game ADD DATAFILE ‘/oracle/oradata/db/GAME02.dbf’ SIZE 1000M;

2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf’ RESIZE 4000M;

3.设定数据文件自动扩展
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;

设定后查看表空间信息
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES100)/A.BYTES “% USED”,(C.BYTES100)/A.BYTES “% FREE”
FROM SYS.SM$T{S}_{A}VAILA,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

2.创建用户

create user test identified by test

default tablespace SIRM2

temporary tablespace TEMP
profile DEFAULT;

–创建用户并指定表空间
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;

–删除用户命令
drop user user_name cascade;

3.授权

grant dba to test;
grant connect to test;
grant resource to test;

–给用户授予权限
grant connect,resource to username;
撤权：
revoke 权限… from 用户名;

4. SYS用户在CMD下以DBA身份登陆：
   在CMD中打sqlplus /nolog
   然后再 conn / as sysdba

附加1：

oarcle数据库真正存放数据的是数据文件（data files），Oarcle表空间（tablespaces）实际上是一个逻辑的概念，他在物理上是并不存在的，那么把一组data files 捻在一起就成为一个表空间。（https://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html）

表空间属性：

一个数据库可以包含多个表空间，一个表空间只能属于一个数据库

一个表空间包含多个数据文件，一个数据文件只能属于一个表空间

表这空间可以划分成更细的逻辑存储单元

Oracle数据库的存储结构：

从逻辑的角度来看，一个数据库（database）下面可以分多个表空间（tablespace）；一个表空间下面又可以分多个段（segment）；一个数据表要占一个段（segment），一个索引也要占一个段（segment ）。 一个段（segment）由多个 区间（extent）组成，那么一个区间又由一组连续的数据块（data block）组成。这连续的数据块是在逻辑上是连续的，有可能在物理磁盘上是分散。

那么从物理的角度上看，一个表空间由多个数据文件组成，数据文件是实实在在存在的磁盘上的文件。这些文件是由oracle数据库操作系统的block 组成的。

Segment（段） ：段是指占用数据文件空间的通称，或数据库对象使用的空间的集合；段可以有表段、索引段、回滚段、临时段和高速缓存段等。

Extent （区间）：分配给对象（如表）的任何连续块叫区间；区间也叫扩展，因为当它用完已经分配的区间后，再有新的记录插入就必须在分配新的区间（即扩展一些块）；一旦区间分配给某个对象（表、索引及簇），则该区间就不能再分配给其它的对象.

附加2：oracle 表空间创建 格式及属性说明：http://blog.sina.com.cn/s/blog_3cae66560102w0vk.html

一、创建表空间完整的格式

CREATE [UNDO|TEMPORARY] TABLESPACE tablespace_name

 DATAFILE 'path/filename' [SIZE INTEGER[K| M]]  [REUSE] 

 [AUTOEXTEND [OFF | ON ] ]

 [NEXT INTEGER[K | M]]

 [MAXSIZE [UNLIMITED | INTEGER[K | M]]]

 [MINIMUM EXTENT INTEGER[K | M]]

 [BLOCKSIZE integer [k]]

 [DEFAULT STORAGE storage_clause]

 [ONLINE | OFFLINE]

 [LOGGING | NOLOGGING]

 [FORCE LOGGING]

 [EXTENT MANAGEMENT [DICTIONARY | LOCAL]]

 [AUTOALLOCATE | UNIFORM [SIZE INTEGER[K | M]]]

 [SEGMENT SPACE MANAGEMENT [AUTO | MANUAL]]

二、表空间格式说明

1、UNDO

  说明系统将创建一个回滚表空间。

   在9i中数据库管理员可以不必管理回滚段，只有建立了undo表空间，系统就会自动管理回滚段的分配，回收的工作。当然,也可以创建一般的表空间,在上         面创建回滚段.不过对于用户来说,系统管理比自己管理要好很多.如果需要自己管理,请参见回滚段管理的命令详解.

   当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。

2、tablespace_name表空间名满足oracle的命名规范即可。

3、DATAFILE ‘path/filename’ [SIZE INTEGER[K| M]] [REUSE]

  指定一个或多个数据文件路径和文件名,[SIZE INTEGER[K| M]]用来指定表空间在数据文件中所占的空间大小，K|M是说大小的单位是K还是M。

  [REUSE] 如果创建表空间指定的数据文件已经存在，则需要使用reuse注明，否则会报错。

  ERROR at line 1:

ORA-01537: cannot add data file ‘E:\oracle\oradata\10.1.0\ZRPTEST\TB_TEST1.dbf’

• file already part of database

4、[AUTOEXTEND [OFF | ON ] ]

  禁止或允许自动扩展数据文件，默认情况下为OFF

5、[NEXT INTEGER[K | M]]

  指定当需要更多盘区时分配给数据文件的磁盘空间，以K或M为单位。

6、[MAXSIZE [UNLIMITED | INTEGER[K | M]]]

  指定允许分配给数据文件的最大磁盘空间，当为UNLIMITED时表示只有当磁盘空间满时，才不允许再扩展数据文件。

7、[MINMUM EXTENT INTEGER[K | M]]

  指定盘区大小是指定大小的整数倍。即initial和next盘区大小应为minimum extent的整数倍，这个参数可以减小空间碎片，保证表空间的范围是这个值的整数倍。

  【注：minextents 用于指定在创建时分配给段的盘区总数。使用这个参数，即使可用空间是不连续的，在创建对象时也能分配很大的空间。默认                    值和最小值都是1。如果minextents>1，盘区大小以next和pctincrease为基础计算。】

8、[BLOCKSIZE integer [k]]

    这个参数可以设定一个不标准的块的大小。如果要设置这个参数，必须设置db_block_size,至少一个db_nk_block_size,

    并且声明的integer的值必须等于db_nk_block_size.

   注意：在临时表空间不能设置这个参数。

9、 [DEFAULT STORAGE storage_clause]

   表示为在该表空间创建的全部对象指定缺省的存储参数，没有指定时oracle将会为所有对象指定一些系统默认的存储参数。

   storage_clause的语法格式如下：

   STORAGE（

        INITIAL integer[K|M]       --为段分配第一个区的大小

        NEXT     integer[K|M]       --为第一个扩展分区的大小

        MINEXTENTS integer|UMLIMITED      --创建段时分配的最小总区间数

        MAXEXTENTS integer|UMLIMITED     --创建段时分配的最大总区间数

        PCTINCREASE integer                           --每次扩展增量的大小

        FREELISTS integer                                  --模式对象中每一个自由列表组中自由列表的数量，表、族或索引的每个空闲列表组中的列表数量。

        FREELISTS GROUPS integer                  --指定表、族或索引的每个空闲列表组数量。

     ）

10、[ONLINE | OFFLINE]

  ONLINE是指在创建表空间之后使该表空间立即对授权访问该表空间的用户可用。

  OFFLINE是指offline以后，未完成的事物可以提交或回滚，但不能发起新的事物，也不能进行查询。OFFLINE的四种模式

   （1）NORMAL：做检查点 

             alter tablespace users  offline;           alter tablespace users online;

   （2）TEMPORARY：可以在数据文件损坏的情况下offline tablespace

             alter tablespace users offline temporary;   alter tablespace users online;

   （3）IMMEDIATE：不做检查点，只有在归档模式下才可以offline tablespace，online时需要recover

             alter tablespace users offline immediate;    recover tablespace users;       alter tablespace users online;

    （4）FOR RECOVER：在归档模式下才可以offonline，在基

11、[LOGGING | NOLOGGING]

  指定日志属性，它表示将来的表、索引等是否需要进行日志处理。默认值为LOGGING

12、[FORCE LOGGING]

   使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。

   注意：设置这个参数指数据库不可以open并且进入读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。

13、[PERMANENT | TEMPORARY]

   指出表空间的属性，是永久表空间还是临时表空间。永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。

   这个参数生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。

   如果要创建本地管理表空间，必须使用create temporary tablespace

   注意，声明了这个参数后，不能声明block size

14、[EXTENT MANAGEMENT [DICTIONARY | LOCAL]]

    [AUTOALLOCATE | UNIFORM [SIZE INTEGER[K | M]]]

    这是最重要的子句，说明了表空间如何管理范围。一旦你声明了这个子句，只能通过移植的方式改变这些参数。

    如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。autoallocate说明表空间自动分配范围，用户不能指定范围的大小。

    只有9.0以上的版本具有这个功能。uniform说明表空间的范围的固定大小，缺省是1m。

     不能将本地管理的数据库的system表空间设置成字典管理。oracle公司推荐使用本地管理表空间。

     如果没有设置extent_management_clause，oracle会给他设置一个默认值。如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。

      如果大于9.0.0,那么按照如下设置：如果没有指定default storage_clause,oracle创建一个自动分配的本地管理表空间。

     否则，如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果满足以上的条件，oracle创建一个本地管理

      表空间，extent size是initial.如果不满足以上条件，那么oracle将创建一个自动分配的本地管理表空间。

      如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。表示满足oracle创建一个本地管理表空间并指定uniform。

       否则oracle将创建一个自动分配的本地管理表空间。

      注意：本地管理表空间只能存储永久对象。如果你声明了local,将不能声明default storage_clause,mininum extent、temporary.

15、[SEGMENT SPACE MANAGEMENT [AUTO | MANUAL]]

   指定 表空间段的管理方式，AUTO为系统自动管理，MANUAL为手工管理。默认为AUTO。

附加3：Oracle中分区表中表空间属性：https://blog.51cto.com/hbxztc/1929585