Linux创建Oracle用户

SQL>conn 用户名/密码
conn / as sysdba(管理用户)
sqlplus / as sysdba
sqlplus /nolog （无用户连接Oracle）

获取表：

select table_name from user_tables; //当前用户的表

select table_name from all_tables; //所有用户的表

select table_name from dba_tables; //包括系统表

select table_name from dba_tables where owner='用户名'

清除现有的用户： drop user userName cascade;

清除现有表空间： drop tablespace nsName including contents and datafiles cascade constraints ;



1、查看我们常规将用户表空间放置的位置：执行如下sql：

select name from v$datafile;

2、创建用户表空间：

CREATE TABLESPACE uap datafile '+FDATADG/bzyddb/datafile/uap_tbs.dbf ' size 1000M autoextend on next 50m maxsize unlimited;

CREATE TABLESPACE isc datafile '+FDATADG/bzyddb/datafile/isc_tbs.dbf ' size 1000M autoextend on next 50m maxsize unlimited;

CREATE TABLESPACE bpm datafile '+FDATADG/bzyddb/datafile/bpm_tbs.dbf ' size 1000M autoextend on next 50m maxsize unlimited;


3、创建用户，指定密码和上边创建的用户表空间：

CREATE USER uap IDENTIFIED BY uap2016 DEFAULT TABLESPACE uap;

CREATE USER isc IDENTIFIED BY isc2016 DEFAULT TABLESPACE isc;

CREATE USER bpm IDENTIFIED BY bpm2016 DEFAULT TABLESPACE bpm;



imp uap/uap2016@bzyddb file=./bak/uap.dmp log=./bak/uap.dmp.log owner=(uap) buffer=64000000;

imp isc/isc2016@bzyddb file=./bak/isc.dmp log=./bak/isc.dmp.log owner=(isc) buffer=64000000;







4、添加权限：

grant ALTER ANY CLUSTER to uap with admin option;
grant ALTER ANY DIMENSION to uap with admin option;
grant ALTER ANY INDEX to uap with admin option;
grant ALTER ANY INDEXTYPE to uap with admin option;
grant ALTER ANY LIBRARY to uap with admin option;
grant ALTER ANY OUTLINE to uap with admin option;
grant ALTER ANY PROCEDURE to uap with admin option;
grant ALTER ANY ROLE to uap with admin option;
grant ALTER ANY SEQUENCE to uap with admin option;
grant ALTER ANY SNAPSHOT to uap with admin option;
grant ALTER ANY TABLE to uap with admin option;
grant ALTER ANY TRIGGER to uap with admin option;
grant ALTER ANY TYPE to uap with admin option;
grant ALTER DATABASE to uap with admin option;
grant ALTER PROFILE to uap with admin option;
grant ALTER RESOURCE COST to uap with admin option;
grant ALTER ROLLBACK SEGMENT to uap with admin option;
grant ALTER SESSION to uap with admin option;
grant ALTER SYSTEM to uap with admin option;
grant ALTER TABLESPACE to uap with admin option;
grant ALTER USER to uap with admin option;
grant ANALYZE ANY to uap with admin option;
grant AUDIT ANY to uap with admin option;
grant AUDIT SYSTEM to uap with admin option;
grant BACKUP ANY TABLE to uap with admin option;
grant BECOME USER to uap with admin option;
grant COMMENT ANY TABLE to uap with admin option;
grant CREATE ANY CLUSTER to uap with

admin option;
grant CREATE ANY CONTEXT to uap with admin option;
grant CREATE ANY DIMENSION to uap with admin option;
grant CREATE ANY DIRECTORY to uap with admin option;
grant CREATE ANY INDEX to uap with admin option;
grant CREATE ANY INDEXTYPE to uap with admin option;
grant CREATE ANY LIBRARY to uap with admin option;
grant CREATE ANY OUTLINE to uap with admin option;
grant CREATE ANY PROCEDURE to uap with admin option;
grant CREATE ANY SEQUENCE to uap with admin option;
grant CREATE ANY SNAPSHOT to uap with admin option;
grant CREATE ANY SYNONYM to uap with admin option;
grant CREATE ANY TABLE to uap with admin option;
grant CREATE ANY TRIGGER to uap with admin option;
grant CREATE ANY TYPE to uap with admin option;
grant CREATE ANY VIEW to uap with admin option;
grant CREATE CLUSTER to uap with admin option;
grant CREATE DATABASE LINK to uap with admin option;
grant CREATE DIMENSION to uap with admin option;
grant CREATE INDEXTYPE to uap with admin option;
grant CREATE LIBRARY to uap with admin option;
grant CREATE PROCEDURE to uap with admin option;
grant CREATE PROFILE to uap with admin option;
grant CREATE PUBLIC DATABASE LINK to uap with admin option;
grant CREATE PUBLIC SYNONYM to uap with admin option;
grant CREATE ROLE to uap with admin option;
grant CREATE ROLLBACK SEGMENT to uap with admin option;
grant CREATE SEQUENCE to uap with admin option;
grant CREATE SESSION to uap with admin option;
grant CREATE SNAPSHOT to uap with admin option;
grant CREATE SYNONYM to uap with admin option;
grant CREATE TABLE to uap with admin option;
grant CREATE TABLESPACE to uap with admin option;
grant CREATE TRIGGER to uap with admin option;
grant CREATE TYPE to uap with admin option;
grant CREATE USER to uap with admin option;
grant CREATE VIEW to uap with admin option;
grant DEBUG ANY PROCEDURE to uap with admin option;
grant DEBUG CONNECT SESSION to uap with admin option;
grant DELETE ANY TABLE to uap with admin option;
grant DROP ANY CLUSTER to uap with admin option;
grant DROP ANY CONTEXT to uap with admin option;
grant DROP ANY DIMENSION to uap with admin option;
grant DROP ANY DIRECTORY to uap with admin option;
grant DROP ANY INDEXTYPE to uap with admin option;
grant DROP ANY LIBRARY to uap with admin option;
grant DROP ANY OUTLINE to uap with admin option;
grant DROP ANY PROCEDURE to uap with admin option;
grant DROP ANY ROLE to uap with admin option;
grant DROP ANY SEQUENCE to uap with admin option;
grant DROP ANY SNAPSHOT to uap with admin option;
grant DROP ANY SYNONYM to uap with admin option;
grant DROP ANY TABLE to uap with admin option;
grant DROP ANY TRIGGER to uap with admin option;
grant DROP ANY TYPE to uap with admin option;
grant DROP ANY VIEW to uap with admin option;
grant DROP PROFILE to uap with admin option;
grant DROP PUBLIC DATABASE LINK to uap with admin option;
grant DROP PUBLIC SYNONYM to uap with admin option;
grant DROP ROLLBACK SEGMENT to uap with admin opt

ion;
grant DROP TABLESPACE to uap with admin option;
grant DROP USER to uap with admin option;
grant EXECUTE ANY INDEXTYPE to uap with admin option;
grant EXECUTE ANY LIBRARY to uap with admin option;
grant EXECUTE ANY PROCEDURE to uap with admin option;
grant EXECUTE ANY TYPE to uap with admin option;
grant FORCE ANY TRANSACTION to uap with admin option;
grant FORCE TRANSACTION to uap with admin option;
grant GLOBAL QUERY REWRITE to uap with admin option;
grant grant ANY OBJECT PRIVILEGE to uap with admin option;
grant grant ANY PRIVILEGE to uap with admin option;
grant grant ANY ROLE to uap with admin option;
grant INSERT ANY TABLE to uap with admin option;
grant LOCK ANY TABLE to uap with admin option;
grant MANAGE TABLESPACE to uap with admin option;
grant ON COMMIT REFRESH to uap with admin option;
grant QUERY REWRITE to uap with admin option;
grant RESTRICTED SESSION to uap with admin option;
grant SELECT ANY DICTIONARY to uap with admin option;
grant SELECT ANY SEQUENCE to uap with admin option;
grant SELECT ANY TABLE to uap with admin option;
grant UNDER ANY TABLE to uap with admin option;
grant UNDER ANY TYPE to uap with admin option;
grant UNDER ANY VIEW to uap with admin option;
grant UNLIMITED TABLESPACE to uap with admin option;
grant UPDATE ANY TABLE to uap with admin option;