Docker安装Oracle 1903

mkdir -p /opt/oracle/oradata
chmod -R 777 /opt/oracle

docker run -d  \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_SID=ORCLCDB \
-e ORACLE_PDB=ORCLPDB1 \
-e ORACLE_PWD=123456 \
-e ORACLE_EDITION=standard \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-v /opt/oracle/oradata:/opt/oracle/oradata \
--name oracle_1903 \
baoyibo/oracle:19.3.0-ee

Docker安装Oracle 2103

mkdir -p /opt/oracle/oradata
chmod -R 777 /opt/oracle

docker run -d  \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_SID=ORCLCDB \
-e ORACLE_PDB=ORCLPDB1 \
-e ORACLE_PWD=123456 \
-e ORACLE_EDITION=standard \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-v /opt/oracle/oradata:/opt/oracle/oradata \
--name oracle_2103 \
baoyibo/oracle:21.3.0-ee

创建表空间和用户并授权

select CDB from v$database;

alter pluggable database ORCLPDB1 open;

alter session set container=ORCLPDB1;

select tablespace_name,status,contents from user_tablespaces;

# 创建表空间
create tablespace test datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/test.dbf' size 2048M autoextend on;

# 创建用户
create user test identified by oracle default tablespace test;

# 用户授权
grant connect,resource,dba to test;

# 删除表空间及数据文件
drop tablespace test including contents and datafiles;

常用命令

# 查看数据库是否为CDB
select CDB from v$database;

select tablespace_name,status,contents from user_tablespaces;

select * from user_all_tables;

# 查看所有pdb
show pdbs

# 查看当前用户的默认表空间
select username,default_tablespace from user_users;

# 查看各个表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files  group by tablespace_name;

# 查看当前用户的角色
select * from user_role_privs;
select * from user_tab_privs;

# 查看用户下所有的表
select * from user_tables;

# 查看某表的创建时间
select object_name,created from user_objects where object_name=upper('&table_name');

# 查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');

# 查看表空间剩余大小
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

# 查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,'Y')>0;

# 查看索引个数和类别
select index_name,index_type,table_name from user_indexes order by table_name;

# 查看索引被索引的字段
select * from user_ind_columns where index_name=upper('&table_name');

# 使用sys_context查看属于哪个容器
select sys_context('USERENV','CON_NAME') from dual;

# 使用show查看当前属于哪个容器
show con_name