管理表空间和数据文件

创建表空间
create tablespace tablespace_name
datafile 'path\file_name' size integer [K|M] reuse
[autoexend [off|on next integer[K|M]]
[maxsize [unlimited|integer [K|M]]]
[minimum extent integer [K|M]]
[default storage storage]
[online|offline]
[logging|nologging]
[permanent|temporary]
[extent management [dictionary|local[autoallocate|uniform. size integer[K|M]]]]

创建表空间
create tablespace userdata
datafile '/u01/oradata/userdata01.dbf' size 100M
autoextend on next 50M maxsize 1000M;

本地管理的表空间
create tablespace userdata
datafile '/u01/oradata/userdata01.dbf' size 500M
extent management local uniform. size 128k;

字典管理的表空间(X)
create tablespace userdata
datafile '/u01/oradata/userdata01.dbf'
size 500m extent management dictionary
default storage
(initial 1M next 1M pctincrease 0);

undo表空间
create undo tablespace undo1
datafile '/u01/oradata/undo01.dbf' size 40M;

切换undo表空间
alter system set undo_tablespace=undo1;

临时表空间
create temporary tablespace temp
tempfile '/u01/oradata/temp01.dbf' size 500M
extent management local uniform. size 4M;

设置系统默认临时表空间
alter database default temporary tablespace temp;

设置用户的默认的表空间：
alter user user_name default tablespace tablespace_name;
alter database default tablespace tablespace_name;

设置用户的默认的临时表空间
alter database default temporary tablespace tablespace_name;

表空间：
– dba_tablespaces
– v$tablespace
数据文件信息：
– dba_data_files
– v$datafile
临时文件信息：
– dba_temp_files
– v$tempfile

查询默认表空间
select * from database_properties
where property_name='DEFAULT_PERMANENT_TABLESPACE';

查询表空间及数据文件信息
select tablespace_name,file_name,bytes from dba_data_files;

查询临时表空间及数据文件信息
select tablespace_name,file_name,bytes from dba_temp_files;

查询USERS/SYSTEM/SYSAUX表空间内存放的对象
select owner,tablespace_name,segment_name,segment_type,bytes
from dba_segments
where tablespace_name in ('USERS','SYSTEM','SYSAUX');

将DBA权限授予用户test
grant unlimited tablespace,dba to test;

将表空间设置为读写状态
alter tablespace tablespace_name read write;

将表空间设置为只读状态
alter tablespace tablespace_name read only;

将表创建到指定的表空间
create table test(x varchar2(10),y number(10)) tablespace tablespace_name;

将临时表空间temp2设置为默认的临时表空间：
alter database default temporary tablespace temp2;

将表空间user设置为默认的表空间
alter database default tablespace users;

修改表空间大小
alter database datafile '/oracle/oradata/ora10/hjp.dbf' resize 500M;

删除表空间
drop tablespace tablespace_name including contents and datafiles;
drop tablespace tablespace_name including contents cascade constraints;

对于分区的表空间，如果表空间包含数据(不是所有数据)，要先用alter table...move partition将该表空间的数据移到另外的表空间，再删除。

增加新数据文件到表空间
alter tablespace hjp
add datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJPING02.DBF' size 30M;

删除表空间中无数据的数据文件
alter tablespace hjp
drop datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJPING02.DBF';

数据文件自动扩展设置
1、在create database语句中设置
2、在alter database语句中设置
   alter database datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP01.DBF'
   autoextend on next 10M maxsize 500M;
3、在create tablespace语句中设置
   create tablespace hjp datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP01.DBF'
   size 20M autoextend on next 5M maxsize 500M;
4、在alter tablespace语句中设置
   alter tablespace hjp datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP01.DBF'
   size 20M autoextend on next 5M maxsize 500M;
   alter database datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP01.DBF' autoextend on;
5、在追加新数据文件语句中设置
   alter tablespace hjp
   add datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJPING02.DBF' size 30M
   autoextend on next 10M maxsize 500M;
6、将已经存在的数据文件设置为非自动扩展
   alter database datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP01.DBF'
   autoextend off;

数据文件大小调整(加大到100M)
alter database datafile 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP01.DBF' resize 100M;

改变表空间名称(online)
alter tablespace hjping rename to hjp;

重命名数据文件
1、使表空间处于offline
alter tablespace hjp offline normal;
2、用操作系统命令命名数据文件
3、重命名数据文件
alter tablespace hjp rename datafile
'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJPING01.DBF',
'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJPING02.DBF'
TO
'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP01.DBF',
'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP02.DBF';

数据文件迁移
SQL> shutdown immediate;
SQL> $move D:\ORACLE\ORACLE_10G\ORADATA\HJP D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP;
SQL> startup mount;
SQL> alter database rename file 'D:\ORACLE\ORACLE_10G\ORADATA\HJP' to 'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJP';
SQL> alter database open;

移动数据文件
1、使表空间处于offline
   alter tablespace hjp offline normal;
2、用操作系统命令复制文件到新位置
3、重命名数据文件
   alter tablespace hjp rename datafile
   'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJPING01.DBF',
   'D:\ORACLE\ORACLE_10G\ORADATA\ORA10\HJPING02.DBF'
   TO
   'D:\ORACLE\ORACLE_10G\ORADATA\HJP01.DBF',
   'D:\ORACLE\ORACLE_10G\ORADATA\HJP02.DBF';

移动表至另一表空间
alter table test move tablespace users;

查询当前用户拥有的表:
select * from tab;
select * from tab where tabtype='TABLE';(TABLE要大写)

管理员用户:
select * from dba_tables where wner='SCOTT';(SCOTT要大写)

查看表空间信息
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 user hjp identified by password
default tablespace hjp
temporary tablespace temp;

grant connect to hjp;
grant resource to hjp;

drop user XXXX cascade;

drop tablespace XXXX INCLUDING CONTENTS;

SQL> alter database default tablespace system;

SQL> DROP TABLESPACE users INCLUDING CONTENTS and datafiles;

 

tablespace是表所在的表空间；
pctfree和pctused是控制块使用的存储参数
initrans表示每个块所预留的允许并发事务的入口数，默认值是1；
maxtrans表示每个块所允许的最大并发事务的入口数；

storage子句定义了如何给表分配区
initial 表示给表分配的最初的区容量；
next表示如果最初的区已经完全被表行填满了，那么该参数就是给表分配的下一个区容量；
pctincrease表示超尺寸的表的第三个(如果minextents被设置为1)和随后的区容量在前一个区容量基础上增长的百分数；
minextents表示在建立表示分配的区个数，对表来说通常是1；
maxextents表示表能分配到的最大的区个数；

logging表示表的创建以及后续对该表的操作都记入重作日志；
nologging表示对表的创建以及某些数据装载不记入重作日志。

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

转载于:http://blog.itpub.net/17012874/viewspace-693284/