Oracle用户和表空间管理

创建用户授予权限

create user material identified by material default tablespace material1;
grant connect,resource to material;
grant dba to material;

删除用户

drop material cascade;

创建表空间

Oracle 10g

Windows

CREATE TABLESPACE MATERIAL1
DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;

Linux

CREATE TABLESPACE MATERIAL1
DATAFILE '/u01/app/oracle/oradata/orcl/MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;

Oracle RAC环境

CREATE TABLESPACE MATERIAL1
DATAFILE '+ORADATA/ORCL/MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;

删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

Oracle 11g

CREATE TABLESPACE MATERIAL1
DATAFILE 'D:\app\Administrator\oradata\orcl\MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;
ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\orcl\MATERIAL1.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;

设定数据文件自动扩展

ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\orcl\MATERIAL1.dbf' 
AUTOEXTEND ON NEXT 100M 
MAXSIZE 10000M; 
ALTER DATABASE DATAFILE 'D:\app\jackycheng\oradata\orcl\MATERIAL1.dbf' 
AUTOEXTEND ON NEXT 100M 
MAXSIZE 10000M;