Oracle高级操作：启停服务，开启PDB，创建表空间、用户、导入导出用户数据等

╰+哭是因爲堅強的太久メ 2022-02-17 13:39 570阅读 0赞

sqlplus  system/orcl\_123456@orcl   //sqlplus连接

select name from v$database;    //查询当前数据库名

select instance\_name from v$instance; // 查询当前数据库实例名

//创建临时表空间

CREATE TEMPORARY TABLESPACE OX\_TEMP TEMPFILE '/opt/oracle/oradata/orcl/OX\_TEMP.DBF'  
SIZE 64M  
AUTOEXTEND ON  
NEXT 64M MAXSIZE UNLIMITED  
EXTENT MANAGEMENT LOCAL;

//创建数据表空间

CREATE TABLESPACE OX\_DATA  
LOGGING  
DATAFILE '/opt/oracle/oradata/orcl/OX\_DATA.DBF'  
SIZE 64M  
AUTOEXTEND ON  
NEXT 64M MAXSIZE UNLIMITED  
EXTENT MANAGEMENT LOCAL;

//创建用户（方案） 并指定表空间

CREATE USER OX IDENTIFIED BY ox\_123456  
ACCOUNT UNLOCK  
DEFAULT TABLESPACE OX\_DATA  
TEMPORARY TABLESPACE OX\_TEMP;

//给用户授权  
GRANT CONNECT,RESOURCE TO OX;//  把connect,resource权限授予用户  
GRANT DBA TO OX;    //把 dba权限授予给用户

\---一般用户登录---  
sqlplus  C\#\#DBADMIN/orcl\_admin@orcl;

sqlplus ox/ox\_123456@ORCLPDB;

conn ox/ox\_123456@orclpdb;

conn  C\#\#DBADMIN/orcl\_admin@orcl;  
\--打开PDB数据库--

sqlplus / as sysdba

\#查看PDB数据状态

select con\_id,name,open\_mode from v$pdbs;

alter pluggable database ORCLPDB open;

alter session set container=ORCLPDB;

commit;

\--sys dba登陆 开启服务---  
\# su - oracle  
$ lsnrctl status   检查oracle监听器运行状态  
$ lsnrctl start / lsnrctl stop

sqlplus / as sysdba

>startup  //开启服务   >shutdown immediate; //关闭服务

\--oracle 12c 开启pdb数据库--

select CDB from v$database;

show con\_name;

select con\_id, dbid, guid, name , open\_mode from v$pdbs;

alter pluggable database ORCLPDB open;   
alter pluggable database all open;  
\#alter pluggable database PDBORCL close;

alter session set container=ORCLPDB;  
\#alter session set container=CDB$ROOT;

CREATE TABLESPACE OX  
LOGGING  
DATAFILE '/u01/app/oracle/oradata/orcl/orclpdb/OX.DBF'  
SIZE 64M  
AUTOEXTEND ON  
NEXT 64M MAXSIZE UNLIMITED  
EXTENT MANAGEMENT LOCAL;

CREATE TEMPORARY TABLESPACE OX\_TEMP TEMPFILE '/u01/app/oracle/oradata/orcl/orclpdb/OX\_TEMP.DBF'  
SIZE 64M  
AUTOEXTEND ON  
NEXT 64M MAXSIZE UNLIMITED  
EXTENT MANAGEMENT LOCAL;

CREATE USER OX IDENTIFIED BY ox\_123456  
ACCOUNT UNLOCK  
DEFAULT TABLESPACE OX  
TEMPORARY TABLESPACE OX\_TEMP;

GRANT CONNECT,RESOURCE TO OX;//  把connect,resource权限授予用户  
GRANT DBA TO OX;    //把 dba权限授予给用户

jdbc连接oracle12的数据库需要具备的条件:  
 1、监听成功启用,且tnsping通过(OK (30 毫秒);  
 2、tnsnames.ora配置正确(连接pdb数据库,注意添加pdb服务项,具体如何配置查看\[准备阶段\]);  
 3、 监听连带的相应数据库成功开启;  
 4、Jdbc连接字符串迭代更新为”jdbc:oracle:thin:@192.168.75.131:1521/pdborcl”而非" jdbc:oracle:thin:@192.168.75.131:1521:pdborcl"

\--一个触发器--  
conn / as sysdba

CREATE TRIGGER open\_all\_pdbs  
AFTER STARTUP  
ON DATABASE  
BEGIN  
EXECUTE IMMEDIATE 'alter pluggable database all open';  
END  
open\_all\_pdbs;  
/

\--Oracle数据库导出--  
exp ox/ox\_pwd@ox file=/home/oracle/ox\_db.dmp owner=ox rows=n

exp ox/ox\_123456@orclpdb  file=/home/oracle/db\_bak/ox\_db.dmp owner=ox rows=n   //仅owner=ox导出结构

\---重点-----导出owner=ox所有数据对象

exp ox/ox\_pwd@ox buffer=10240 file=/home/oracle/ox\_db\_20190417.dmp log=/home/oracle/ox\_db\_20190417.log owner=ox   //导出owner=ox所有数据对象

\--Oracle数据库导入--  
sqlplus / as sysdba

select owner, table\_name from dba\_tables where owner='OX';

alter session set container=orclpdb;

imp ox/ox\_123456@orclpdb buffer=10240 file=ox\_db.dmp full=y ignore=y;

imp ox/ox\_123456@orclpdb buffer=10240 file=ox\_db\_20190417.dmp full=y ignore=y;

\----重点----将fromuser=OX用户的所有数据对象导入到touser=OX---

imp SYSTEM/oracle@orclpdb buffer=102400 file=ox\_db\_20190417.dmp fromuser=OX touser=OX ignore=y;  （不能使用full=y） //将fromuser=OX用户的所有数据对象导入到touser=OX

\--删除方案（删除OX2用户及OX2的所有数据）---