oracle数据库创建用户的sql语句旗舰版

1、用system用户和你设定的密码通过sqlplus登陆数据库

2、创建表空间(DATAFILE 'E:\APP\USER\ORADATA\ORCL\HTBASE1')
CREATE SMALLFILE TABLESPACE "HTBASE" DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\HTBASE\HTBASE1' SIZE 2024M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED , 'D:\APP\ADMINISTRATOR\ORADATA\HTBASE\HTBASE2' SIZE 2024M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS

增加表空间文件：
ALTER TABLESPACE "HTBASE" ADD DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\HTBASE\HTBASE4.DBF' SIZE 1024M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED

3、创建用户并制定默认表空间
create user ADT identified by htbase default tablespace htbase;
create user AIB identified by htbase default tablespace htbase;
create user CDR identified by htbase default tablespace htbase;
create user CHART identified by htbase default tablespace htbase;
create user COMM identified by htbase default tablespace htbase;
create user DIAG identified by htbase default tablespace htbase;
create user DRUG identified by htbase default tablespace htbase;
create user EXAM identified by htbase default tablespace htbase;
create user EXP identified by htbase default tablespace htbase;
create user FLOW identified by htbase default tablespace htbase;
create user INFECT identified by htbase default tablespace htbase;
create user INPARCHIVE identified by htbase default tablespace htbase;
create user INPCASE identified by htbase default tablespace htbase;
create user INPCHILD identified by htbase default tablespace htbase;
create user INPEXP identified by htbase default tablespace htbase;
create user INPFILE identified by htbase default tablespace htbase;
create user INPINV identified by htbase default tablespace htbase;
create user INPORD identified by htbase default tablespace htbase;
create user INPTEMPER identified by htbase default tablespace htbase;
create user INTER identified by htbase default tablespace htbase;
create user IPLOG identified by htbase default tablespace htbase;
create user LAB identified by htbase default tablespace htbase;
create user LEAVEORD identified by htbase default tablespace htbase;
create user MEDTERM identified by htbase default tablespace htbase;
create user METADATA identified by htbase default tablespace htbase;
create user MPI identified by htbase default tablespace htbase;
create user MSG identified by htbase default tablespace htbase;
create user NURCONFIG identified by htbase default tablespace htbase;
create user NURSING identified by htbase default tablespace htbase;
create user ORDERDICT identified by htbase default tablespace htbase;
create user OUTP identified by htbase default tablespace htbase;
create user OUTPADM identified by htbase default tablespace htbase;
create user OUTPORD identified by htbase default tablespace htbase;
create user PA identified by htbase default tablespace htbase;
create user PH identified

by htbase default tablespace htbase;
create user PRINT identified by htbase default tablespace htbase;
create user PRIVILEGE identified by htbase default tablespace htbase;
create user QA identified by htbase default tablespace htbase;
create user SETUP identified by htbase default tablespace htbase;
create user SEARCH identified by htbase default tablespace htbase;
create user STOCK identified by htbase default tablespace htbase;
create user TASK identified by htbase default tablespace htbase;
create user TEMPLATE identified by htbase default tablespace htbase;
create user TEXTTEMPLATE identified by htbase default tablespace htbase;
create user USERMGMT identified by htbase default tablespace htbase;
create user WORKFLOW identified by htbase default tablespace htbase;
4、给用户分配权限
grant resource,connect,dba to ADT;
grant resource,connect,dba to AIB;
grant resource,connect,dba to CDR;
grant resource,connect,dba to CHART;
grant resource,connect,dba to COMM;
grant resource,connect,dba to DIAG;
grant resource,connect,dba to DRUG;
grant resource,connect,dba to EXAM;
grant resource,connect,dba to EXP;
grant resource,connect,dba to FLOW;
grant resource,connect,dba to INFECT;
grant resource,connect,dba to INPARCHIVE;
grant resource,connect,dba to INPCASE;
grant resource,connect,dba to INPCHILD;
grant resource,connect,dba to INPEXP;
grant resource,connect,dba to INPFILE;
grant resource,connect,dba to INPINV;
grant resource,connect,dba to INPORD;
grant resource,connect,dba to INPTEMPER;
grant resource,connect,dba to INTER;
grant resource,connect,dba to IPLOG;
grant resource,connect,dba to LAB;
grant resource,connect,dba to LEAVEORD;
grant resource,connect,dba to MEDTERM;
grant resource,connect,dba to METADATA;
grant resource,connect,dba to MPI;
grant resource,connect,dba to MSG;
grant resource,connect,dba to NURCONFIG;
grant resource,connect,dba to NURSING;
grant resource,connect,dba to ORDERDICT;
grant resource,connect,dba to OUTP;
grant resource,connect,dba to OUTPADM;
grant resource,connect,dba to OUTPORD;
grant resource,connect,dba to PA;
grant resource,connect,dba to PH;
grant resource,connect,dba to PRINT;
grant resource,connect,dba to PRIVILEGE;
grant resource,connect,dba to QA;
grant resource,connect,dba to SETUP;
grant resource,connect,dba to SEARCH;
grant resource,connect,dba to STOCK;
grant resource,connect,dba to TASK;
grant resource,connect,dba to TEMPLATE;
grant resource,connect,dba to TEXTTEMPLATE;
grant resource,connect,dba to USERMGMT;
grant resource,connect,dba to WORKFLOW;
5、导入数据库
imp system/htinc@orcl full=y fiLe=D:/htbase.dmp log=e:/20120608.log ignore=y buffer=64000

6、修改密码
alter user system identified by password

修改账户密码有效期限
alter profile default limit password_life_time unlimited;

查看数据库服务端字符集
select userenv('language') from dual

修改数据库配置，解决空表无法导出问题

：
alter system set deferred_segment_creation=false;
查看：
show parameter deferred_segment_creation;

数据库文件导入命令：
imp adt/htbase@orcl

数据库导出前解决空表无法导出问题：
select distinct 'alter table '||owner||'.'||table_name||' allocate extent;' from dba_col_comments where owner in (select username from dba_user where default_tablespace='HTBASE')