--创建一个表空间 CREATE TABLESPACE MYSPACE DATAFILE 'D:/MYSPACE.DBF' SIZE 10M AUTOEXTEND ON --指定某个用户的默认的表空间是MYSPACE ALTER USER SYSTEM IDENTIFIED BY NIIT DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACE COMMIT --删除表空间 DROP TABLESPACE MYSPACE --删除表空间,带内容一起删除 DROP TABLESPACE MYSPACE INCLUDING CONTENTS --删除表空间及其内容和物理文件 DROP TABLESPACE MYSPACE INCLUDING CONTENTS AND DATAFILES COMMIT --创建临时表空间 CREATE TEMPORARY TABLESPACE MYTEMP TEMPFILE 'D:/MYTEMP.DBF' SIZE 30M AUTOEXTEND ON DROP TABLESPACE MYTEMP INCLUDING CONTENTS AND DATAFILES --指定默认的表空间和临时表空间 ALTER USER TOM IDENTIFIED BY TOM123 DEFAULT TABLESPACE MYSPACE TEMPORARY TABLESPACE MYTEMP QUOTA UNLIMITED ON MYSPACE --创建一个用户,指定密码,密码必须用字母开头 CREATE USER TOM IDENTIFIED BY TOM123 --删除用户 DROP USER JACK1 --删除用户及所有对象 DROP USER TOM CASCADE --改变用户的表空间 ALTER USER TOM IDENTIFIED BY TOM1234 DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACE --tom没有权限,系统管理员sys,system赋予权限 --赋予系统权限,登陆,连接权限 GRANT CREATE SESSION TO TOM WITH ADMIN OPTION --赋予创建表的权限 GRANT CREATE TABLE,CREATE USER,CREATE SEQUENCE TO TOM --收回权限 REVOKE CREATE SESSION,CREATE USER,CREATE TABLE,CREATE SEQUENCE FROM TOM --赋予TOM系统权限并将权限传递 GRANT CREATE SESSION,CREATE USER TO TOM WITH ADMIN OPTION --收回权限 REVOKE CREATE SESSION,CREATE USER FROM TOM --赋予TOM对象权限 GRANT SELECT ON SCOTT.EMP TO TOM --收回对象权限 REVOKE SELECT ON SCOTT.EMP FROM TOM --将预定义角色赋给用户 GRANT CONNECT TO TOM --收回角色 REVOKE CONNECT FROM TOM REVOKE RESOURCE FROM TOM DROP USER TOM CASCADE CREATE USER TOM IDENTIFIED BY TOM123 DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACE --RESOURCE角色给TOM GRANT RESOURCE TO TOM COMMIT ---自定义角色 --1、创建角色 CREATE ROLE ADS --2、赋予角色相应权限 GRANT CREATE SESSION,CREATE TABLE,CREATE USER TO ADS --3、将角色赋给相应用户 GRANT ADS TO TOM --表空间迁移 --1、表空间下线 ALTER TABLESPACE MYSPACE OFFLINE --2、修改更改后的物理文件地址 ALTER DATABASE RENAME FILE 'C:/MYSPACE.DBF' TO 'E:/MYSPACE.DBF' --3、表空间上线 ALTER TABLESPACE MYSPACE ONLINE --改变表空间后创建表所处的表空间 CREATE TABLE MTABLE ( TNAME VARCHAR2(20) ) COMMIT