caixb/oracle repository overview

docker pull caixb/oracle:11.2.0.2.0

docker run -d --name oracle-server-11.2.0.2.0 -p 22:22 -p 1521:1521 caixb/oracle:11.2.0.2.0

hostname: localhost or docker machine ip
port: 1521
sid: xe
username: system
password: oracle
Password for SYS & SYSTEM is oracle
Password for fareuser, searchuser, bookinguser, checkinuser is rohit123

$ ssh root@<docker_machine_ip> -p 22

# 缺省命令，根据提示输入
sqlplus

# 或
sqlplus system/oracle@localhost:1521

ALTER USER system IDENTIFIED BY CjaPmyzu;
ALTER USER sys IDENTIFIED BY CjaPmyzu;

-- 删除用户及其所有对象（CASCADE选项会删除用户的所有对象）
DROP USER fareuser CASCADE;
DROP USER searchuser CASCADE;
DROP USER bookinguser CASCADE;
DROP USER checkinuser CASCADE;

-- 检查是否还有残留的表空间文件（如果需要完全清理）
SELECT file_name, tablespace_name, bytes/1024/1024 as size_mb
FROM dba_data_files
WHERE tablespace_name LIKE '%FARE%' OR tablespace_name LIKE '%SEARCH%' 
   OR tablespace_name LIKE '%BOOK%' OR tablespace_name LIKE '%CHECK%';

-- 如果需要删除相关表空间
DROP TABLESPACE TBS_CHECKINUSER INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_SEARCHUSER INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_FAREUSER INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_BOOKINGUSER INCLUDING CONTENTS AND DATAFILES;

SELECT username, account_status 
FROM dba_users 
WHERE username IN ('FAREUSER', 'SEARCHUSER', 'BOOKINGUSER', 'CHECKINUSER');

# 数据库1
别名1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 主机1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 服务名1)
    )
  )

# 数据库2  
别名2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 主机2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 服务名2)
    )
  )

dev=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.127.29)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))
test=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xe)))

TNS_ADMIN：值为 tnsnames.ora 文件所在目录

select userenv('language') from dual;

NLS_LANG: AMERICAN_AMERICA.ZHS16GBK

-- 使用 SYSTEM 或 SYS 用户连接
sqlplus system/oracle@localhost:1521/xe

-- 或者使用 SYS 用户（需要以 SYSDBA 身份连接）
sqlplus sys/oracle@localhost:1521/xe as sysdba

-- 或者缺省，本地连接（推荐，权限更高）
sqlplus sys/password as sysdba

-- 创建最简单的用户
CREATE USER dev_user IDENTIFIED BY dev_password;

-- 创建用户并指定默认表空间
CREATE USER app_user IDENTIFIED BY app_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

-- 创建用户并设置配额
CREATE USER report_user IDENTIFIED BY report_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

-- 完整用户创建示例
CREATE USER airline_app IDENTIFIED BY "Airline@2025"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 500M ON users
QUOTA 100M ON temp
PROFILE default
ACCOUNT UNLOCK
PASSWORD EXPIRE;

-- 立即解锁账户（如果创建时锁定了）
ALTER USER airline_app ACCOUNT UNLOCK;

-- 授予连接权限
GRANT CREATE SESSION TO airline_app;

-- 授予资源权限（创建表、序列等）
GRANT CONNECT, RESOURCE TO airline_app;

-- 授予特定对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON system.customers TO airline_app;

-- 授予系统权限
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO airline_app;

-- 创建自定义角色
CREATE ROLE airline_developer;
CREATE ROLE airline_readonly;

-- 为角色授予权限
GRANT SELECT ANY TABLE, CREATE SESSION TO airline_readonly;
GRANT CONNECT, RESOURCE, CREATE VIEW TO airline_developer;

-- 将角色授予用户
GRANT airline_developer TO airline_app;

-- 查看用户权限
SELECT * FROM user_role_privs WHERE username = 'AIRLINE_APP';
SELECT * FROM user_sys_privs WHERE username = 'AIRLINE_APP';

-- 创建开发用户
CREATE USER JSGBJD IDENTIFIED BY "gbjd123"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 500M ON users
PROFILE default
ACCOUNT UNLOCK
PASSWORD EXPIRE;

-- 立即解锁并设置密码（如果创建时锁定了）
ALTER USER JSGBJD ACCOUNT UNLOCK;

-- 创建开发角色
CREATE ROLE dev_role;

-- 授予基本的会话权限
GRANT CREATE SESSION TO dev_role;

-- 授予对象创建权限
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, CREATE SYNONYM TO dev_role;

-- 授予对象管理权限
GRANT ALTER ANY TABLE, DROP ANY TABLE TO dev_role;
GRANT ALTER ANY SEQUENCE, DROP ANY SEQUENCE TO dev_role;
GRANT ALTER ANY PROCEDURE, DROP ANY PROCEDURE TO dev_role;

-- 授予数据操作权限
GRANT SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO dev_role;

-- 授予调试和性能监控权限
GRANT DEBUG CONNECT SESSION, DEBUG ANY PROCEDURE TO dev_role;
GRANT SELECT ANY DICTIONARY TO dev_role;

-- 授予索引和约束权限
GRANT CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX TO dev_role;

-- 为常见开发工具授予权限
GRANT UNLIMITED TABLESPACE TO dev_role;

-- 授予执行 DBMS 包的权限（需要 sysdba 权限执行）
GRANT EXECUTE ON DBMS_LOCK TO dev_role;
GRANT EXECUTE ON DBMS_OUTPUT TO dev_role;
GRANT EXECUTE ON DBMS_SQL TO dev_role;
GRANT EXECUTE ON DBMS_UTILITY TO dev_role;
GRANT EXECUTE ON UTL_FILE TO dev_role;

-- 授予统计信息权限（用于性能调优）
GRANT ANALYZE ANY TO dev_role;

-- 授予物化视图权限（如果使用）
GRANT CREATE MATERIALIZED VIEW, ALTER ANY MATERIALIZED VIEW, 
      DROP ANY MATERIALIZED VIEW TO dev_role;

-- 将开发角色授予用户
GRANT dev_role TO JSGBJD;

-- 授予 CONNECT 角色（包含基本权限）
GRANT CONNECT TO JSGBJD;

-- 授予 RESOURCE 角色（包含资源创建权限）
GRANT RESOURCE TO JSGBJD;

-- 设置默认角色
ALTER USER JSGBJD DEFAULT ROLE dev_role, CONNECT, RESOURCE;

chmod -R 777 /opt/oracle/oradata/XE/

-- 为开发环境创建专用表空间
CREATE TABLESPACE dev_data
DATAFILE '/opt/oracle/oradata/XE/dev_data01.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

CREATE TEMPORARY TABLESPACE dev_temp
TEMPFILE '/opt/oracle/oradata/XE/dev_temp01.dbf'
SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 1G;

-- 修改用户使用开发表空间
ALTER USER JSGBJD 
DEFAULT TABLESPACE dev_data
TEMPORARY TABLESPACE dev_temp
QUOTA UNLIMITED ON dev_data;