Oracle 常用初始化命令
--创建一个表空间CREATE TABLESPACE MYSPACE DATAFILE 'D:/MYSPACE.DBF' SIZE 10M AUTOEXTEND ON--指定某个用户的默认的表空间是MYSPACEALTER USER SYSTEM IDENTIFIED BY NIIT DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACECOMMIT
--删除表空间DROP TABLESPACE MYSPACE
--删除表空间,带内容一起删除DROP TABLESPACE MYSPACE INCLUDING CONTENTS
--删除表空间及其内容和物理文件DROP TABLESPACE MYSPACE INCLUDING CONTENTS AND DATAFILESCOMMIT
--创建临时表空间CREATE TEMPORARY TABLESPACE MYTEMP TEMPFILE 'D:/MYTEMP.DBF' SIZE 30M AUTOEXTEND ONDROP 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 TOMREVOKE RESOURCE FROM TOM
DROP USER TOM CASCADE
CREATE USER TOM IDENTIFIED BY TOM123 DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACE
--RESOURCE角色给TOMGRANT RESOURCE TO TOMCOMMIT
---自定义角色--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
来源: http://www.linuxidc.com/Linux/2017-05/144310.htm