创建表空间:
运行cmd命令行
录入 sqlplus /nolog 无用户名登录
conn /as sysdba 连接到数据本地数据
dba用户远程连接数据库
conn username/password@10.208.26.00:1521/orgin as sysdba
CREATE SMALLFILE TABLESPACE "cskj" LOGGING DATAFILE 'E:\oradata\test\cskj_DATA.DBF' SIZE 5M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TEMPORARY TABLESPACE "cskj_TEMP" TEMPFILE 'E:\oradata\test\cskj_TEMP.DBF' SIZE 5M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
创建用户及授权:
查询表空间:
select * from sys.dba_tablespaces
--创建用户
CREATE USER cskj
IDENTIFIED BY "cskj " DEFAULT TABLESPACE " cskj_DATA"
TEMPORARY TABLESPACE "cskj_TEMP"
ACCOUNT UNLOCK;
--用户授权
GRANT "CONNECT", "DBA", "RESOURCE" TO cskj;//正常使用resource权限即可
DBA: 拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构。
切记!!!需要commit一下
或使用默认表空间:
create user zzg identified by zzg123;
grant create session,create table,dba,create view,create sequence,unlimited tablespace to zzg;
PS:导入数据库
//查询字符集
select userenv('language') from dual;
//该数据库字符集
SIMPLIFIED CHINESE_CHINA.AL32UTF8
//导出语句
exp system/oracle@171.17.34.7:1521/portalorcl file=portalorcl.dmp log=portalorcl.log owner=R,LMDBUSR,CUSTOMIZATIONUSR,COMMUNITYUSR,FEEDBACK buffer=1024000 indexes=n STATISTICS=none
//导入语句
imp system/oracle@171.17.34.7:1521/portalorcl file=portalorcl.dmp log=portalorcl.log full=Y commit=Y buffer=20480000
select * from all_users;
//删除用户
drop user releaseusr cascade;
drop user JCR cascade;
drop user LMDBUSR cascade;
drop user CUSTOMIZATIONUSR cascade;
drop user COMMUNITYUSR cascade;
drop user FEEDBACK cascade;
//删除表空间以及文件
drop tablespace ICMLFQ32 including contents and datafiles;
drop tablespace ICMLNF32 including contents and datafiles;
drop tablespace ICMVFQ04 including contents and datafiles;
drop tablespace ICMSFQ04 including contents and datafiles;
//创建表空间
CREATE TABLESPACE "ICMLFQ32" DATAFILE
'D:\APP\ADMIN\ORADATA\ORCL\icmlfq32_01.dbf' SIZE 314572800
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "ICMLNF32" DATAFILE
'D:\APP\ADMIN\ORADATA\ORCL\icmlnf32_01.dbf' SIZE 26214400
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "ICMVFQ04" DATAFILE
'D:\APP\ADMIN\ORADATA\ORCL\icmvfq04_01.dbf' SIZE 26214400
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "ICMSFQ04" DATAFILE
'D:\APP\ADMIN\ORADATA\ORCL\icmsfq04_01.dbf' SIZE 157286400
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
//查询用户建立的用户
select * from all_users where common='NO';
//创建用户
create user releaseusr identified by passw0rd;
create user JCR identified by passw0rd ;
create user LMDBUSR identified by passw0rd;
create user CUSTOMIZATIONUSR identified by passw0rd;
create user COMMUNITYUSR identified by passw0rd;
create user FEEDBACK identified by passw0rd;
//给用户授权
grant dba,connect to releaseusr,JCR,LMDBUSR,CUSTOMIZATIONUSR,COMMUNITYUSR,FEEDBACK;
//查看磁盘路径
select name from v$datafile;
## imp system/oracle@orcl file=D:\20180518091401_347_portalorcl log=D:\1 full=Y buffer=20480000 commit=Y
##releaseusr,JCR,LMDBUSR,CUSTOMIZATIONUSR,COMMUNITYUSR,FEEDBACK