【无标题】

前言

创建Oracle数据库表空间

---

一、在Windows系统下， 请先点击“开始”，然后点“运行”，输入cmd并点击“确定”，打开命令行窗口

• 以管理员身份运行 sqlplus /nolog 回车后，将出现提示符 SQL>然后输入 conn / as sysdba
  　　一般即可登录，如果失败的话，可以试一下用conn sys/sys用户的密码 as sysdba来重试一下

原文链接：https://blog.csdn.net/qq_38726370/java/article/details/85682745

create tablespace TBS_CBP_DATA
logging
datafile ‘D:\oracle\oradata\orcl\tbs_CBP_data.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;

create tablespace TBS_CBP_IDX
logging
datafile ‘D:\oracle\oradata\orcl\tbs_CBP_idx.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;
–创建用户管理表空间
create user CBP identified by CBP
default tablespace TBS_CBP_DATA;
–赋予用户所以权限
grant connect,resource to CBP;

建表空间时datafile 'D:\oracle\product\10.2.0\oradata\orcl\tbs_das_idx.dbf’改成你的目录下

‘FDREPORT_DATA’
create tablespace FDREPORT_DATA
logging
datafile ‘D:\oracle\oradata\orcl\FDREPORT_DATA_data.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;

grant sysdba to CBP; 授予超级权限

C:\Users\superqc>imp CBP/CBP@127.0.0.1:1521/ORCL file=D:\fgszjmh\database\cbp.dmp full=y

C:\Users\superqc>imp CBP/CBP@127.0.0.1:1521/ORCL file=D:\fgszjmh\database\aqreport.dmp full=y

imp CBP/CBP@127.0.0.1:1521/ORCL file=D:\fgszjmh\database\szreport.dmp full=y

---

create tablespace TBS_AQC_DATA
logging
datafile ‘D:\oracle\oradata\orcl\tbs_AQC_data.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;

create tablespace TBS_AQC_IDX
logging
datafile ‘D:\oracle\oradata\orcl\tbs_AQC_idx.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;

create user AQC identified by AQC
default tablespace TBS_AQC_DATA;

grant sysdba to AQC;

imp AQC/AQC@127.0.0.1:1521/ORCL file=D:\fgszjmh\database\szreport.dmp full=y

本地测试ABC库
create tablespace TBS_ABC_DATA
logging
datafile ‘D:\oracle\oradata\orcl\tbs_ABC_data.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;

create tablespace TBS_ABC_IDX
logging
datafile ‘D:\oracle\oradata\orcl\tbs_ABC_idx.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;

create user ABC identified by ABC
default tablespace TBS_ABC_DATA;

grant connect,resource to ABC;
grant sysdba to ABC;

C:\Users\superqc>imp ABC/ABC@127.0.0.1:1521/ORCL file=D:\fgszjmh\database\szreport.dmp full=y

问题：

在用PL/SQL进行登录时，出现：”ora-01045 :user system lacks create session privilege; logon denied”。

原因：该用户没有创建session会话的权限

在Google找到这段的说明：

What does “ORA-01045: user USERNAME lacks CREATE SESSION privilege;logon denied” mean?

It means that the username and password with which you tried to login are known and accepted by the Oracle server, but that the username doesn’t have permission to create a session. If you think this username should have permission to create sessions send mail to acisdba.

解决办法：

使用系统用户登录后，使用如下sql语句给出错用户赋权限

grant create session to UserName;(UserName是登录出错的用户名)

如想给scott赋予权限：使用 grant create session to scott;

create tablespace TBS_NOTICE_IDX
logging
datafile ‘\tbs_notice_idx.dbf’
size 2048M
autoextend on next 50M maxsize 5120M --每次扩展50M,最大只能为5G
extent management local;

create user notice identified by notice
default tablespace TBS_NOTICE_DATA;

grant connect,resource to notice;
grant sysdba to notice;