Oracle-19C 相关操作命令整理

----运行oracle19c 镜像
docker run --name oracle19c -p 1521:1521 -p 5500:5500 \
-e ORACLE_SID=orcl \
-e ORACLE_PDB=orclpdb1 \
-e ORACLE_PWD=sysadmin01 \
-e ORACLE_CHARACTERSET=zhs16gbk \
-e ORACLE_BASE=/opt/oracle \
-e ORACLE_HOME=/opt/oracle/product/19c/dbhome_1 \
-e PATH=/opt/oracle/product/19c/dbhome_1/bin:/opt/oracle/product/19c/dbhome_1/OPatch/:/usr/sbin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin \
-v /home/nicemoe/oradata:/opt/oracle/oradata \
7b5eb4597688

----进入oracle 19c
docker exec -it oracle19c /bin/bash

----管理员连接sqlplus
sqlplus / as sysdba

--进入oracle用户
su - oracle

--杀死oracle进程
kill oracle 进程 或者关掉oracle
ps -ef|grep ora_dbw0_$ORACLE_SID
kill -9 pidXXX

-----查看实例名称(sid)：
select instance_name from V$instance;

----关闭oracle
SQL>shutdown immediate;
----启动oracle
SQL>startup;

----查看数据库pdbs
SQL>show con_name pdbs;

----切换PDB数据库
SQL> alter session set container=ORCLPDB1

-----开启关闭PDB数据库
SQL> alter pluggable database ORCLPDB1 open;
SQL> alter pluggable database ORCLPDB1 close;

-----删除数据库及数据文件
SQL> drop pluggable database ORCLPDB1 including datafiles;

          drop pluggable database ORCLPDB2 including datafiles;
Pluggable database dropped.

-----从pdbseed复制快速创建PDB库，并指定默认表空间users
SQL>create pluggable database ORCLPDB1 admin user maobao identified by maobao
file_name_convert=('/opt/oracle/oradata/ORCL/pdbseed','/opt/oracle/oradata/ORCL/ORCLPDB1')
default tablespace users
datafile '/opt/oracle/oradata/ORCL/ORCLPDB1/user01.dbf' size 10m autoextend on;

---本地克隆pdb
SQL>create pluggable database ORCLPDB2 from ORCLPDB1 file_name_convert=('/opt/oracle/oradata/ORCL/ORCLPDB1','/opt/oracle/oradata/ORCL/ORCLPDB2');

----启动PDB数据库
SQL>alter pluggable database ORCLPDB1 open;

-- 创建临时空间dev_temp
SQL>create temporary tablespace DEV_TEMP
tempfile '/opt/oracle/oradata/ORCL/ORCLPDB1/dev_temp.dbf'
size 32m
autoextend on
next 32m MAXSIZE unlimited
extent management local;

-- 创建数据表空间dev
SQL>create tablespace DEV
logging
datafile '/opt/oracle/oradata/ORCL/ORCLPDB1/dev.dbf'
size 1024m
autoextend on
next 100m MAXSIZE unlimited
extent management local;

----删除表空间dev
drop tablespace DEV including contents and datafiles cascade constraints ;
--出现错误处理：-ORA-23515: 实体化视图和/或它们的索引存在于表空间中错误处理
--1、select table_name, tablespace_name from dba_tables where tablespace_name='DEV' and table_name in (select mview_name from dba_mviews);
--2、DROP MATERIALIZED VIEW DEV.XXXXX_MVIEW;
--3、删除表空间-drop tablespace DEV including contents and datafiles cascade constraints ;

--Oracle创建用户权限
--创建用户
create user {#用户名} identified by {#密码} default tablespace {#表空间} temporary tablespace {#临时表空间};

---删除用户
drop user {#用户名} cascade;

-- 更改用户默认表空间
alter user {#用户名} default tablespace  {#表空间} temporary tablespace  {#临时表空间};

---赋予session权限
SQL>grant create session to {#用户名};
---赋予读写权限
SQL>grant connect,resource,dba to {#用户名};