Oracle 19c 常用运维 SQL

大部分以 `sysdba` 身份操作（部分操作需要普通业务用户）。

一、实例 / CDB / PDB / 监听状态与启停

1.1 查看实例状态

-- 查看实例状态（OPEN/MOUNTED 等）
SELECT status FROM v$instance;

STATUS 为 OPEN 表示数据库已正常打开。v$instance 是动态性能视图，记录当前实例状态。

1.2 查看是否为 CDB 及 CDB 状态

-- 查看是否为 CDB（YES/NO）
SELECT name, cdb FROM v$database;
-- 查看当前连接的容器
SELECT sys_context('userenv', 'con_name') AS cur_container FROM dual;

CDB 为 YES 表示当前是 CDB 环境。

1.3 查看 PDB 状态

-- 在 CDB 根容器中执行
COLUMN pdb_name FORMAT A20
SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;
-- 或使用动态视图
SELECT name, open_mode FROM v$pdbs;

DBA_PDBS 记录 PDB 的元数据状态；V$PDBS 记录运行时状态（OPEN_MODE 等）。

1.4 启动 / 关闭 CDB 实例（在 Linux 下 sqlplus / as sysdba）

-- 正常关闭
SHUTDOWN IMMEDIATE;
-- 正常启动（默认到 OPEN）
STARTUP;
-- 或分步启动
STARTUP NOMOUNT;   -- 只启动实例
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

STARTUP 语法见官方 SQL*Plus 文档。

1.5 打开 / 关闭 PDB

-- 打开指定 PDB
ALTER PLUGGABLE DATABASE pdb_name OPEN;
-- 关闭指定 PDB
ALTER PLUGGABLE DATABASE pdb_name CLOSE;
-- 打开所有 PDB
ALTER PLUGGABLE DATABASE ALL OPEN;

PDB 在 CDB 启动时默认为 MOUNTED 状态，需要手动 OPEN 或保存状态。

1.6 保存 PDB 打开状态（实现自动打开）

-- 打开 PDB 并保存状态（以后 CDB 启动时自动恢复）
ALTER PLUGGABLE DATABASE pdb_name OPEN;
ALTER PLUGGABLE DATABASE pdb_name SAVE STATE;
-- 查看保存的状态
COLUMN con_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

SAVE STATE 是 12.2 及以上版本推荐做法。

1.7 监听状态与启动（Linux）

# 查看监听状态
lsnrctl status
# 启动监听
lsnrctl start
# 关闭监听
lsnrctl stop

在 CDB/PDB 环境中，默认监听 LISTENER 一般由 dbstart 随实例一起启动。

二、表空间管理

2.1 查询表空间路径

-- 当前容器中的数据文件
COLUMN file_name FORMAT A50
COLUMN tablespace_name FORMAT A20
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb
FROM   dba_data_files
ORDER BY tablespace_name;

DBA_DATA_FILES 记录数据文件路径与表空间对应关系。
在 CDB 根容器中，可以用 CDB_DATA_FILES 查看 CDB 与 PDB 的数据文件。

2.2 创建表空间

-- 创建小文件表空间（示例）
CREATE TABLESPACE tbs_demo
DATAFILE '/opt/oracle/oradata/ORCLCDB/tbs_demo01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE 语法见 SQL Language Reference。

2.3 修改表空间（增加数据文件、调整大小等）

-- 增加数据文件
ALTER TABLESPACE tbs_demo
ADD DATAFILE '/opt/oracle/oradata/ORCLCDB/tbs_demo02.dbf' SIZE 50M;
-- 改变数据文件大小（需操作系统支持）
ALTER DATABASE DATAFILE '/opt/oracle/oradata/ORCLCDB/tbs_demo01.dbf' RESIZE 200M;

2.4 删除表空间

-- 删除表空间及内容（谨慎）
DROP TABLESPACE tbs_demo INCLUDING CONTENTS AND DATAFILES;

三、用户、角色、权限管理

3.1 创建用户并指定默认表空间

-- 创建用户
CREATE USER app_user IDENTIFIED BY "YourPassword123"
DEFAULT TABLESPACE tbs_demo
TEMPORARY TABLESPACE TEMP
QUOTA 100M ON tbs_demo;

CREATE USER 语法见官方文档。

3.2 查询用户信息、默认表空间

-- 查看用户信息
COLUMN username FORMAT A20
COLUMN default_tablespace FORMAT A20
SELECT username, default_tablespace, temporary_tablespace
FROM   dba_users
WHERE  username = 'APP_USER';

3.3 查询用户拥有的角色 / 权限

-- 查用户拥有的角色
SELECT granted_role FROM dba_role_privs WHERE grantee = 'APP_USER';
-- 查用户拥有的系统权限
SELECT privilege FROM dba_sys_privs WHERE grantee = 'APP_USER';
-- 查用户在某个表空间上的配额
SELECT tablespace_name, bytes/1024/1024 AS quota_mb
FROM   dba_ts_quotas
WHERE  username = 'APP_USER';

3.4 创建角色并授权

-- 创建角色
CREATE ROLE app_role;
-- 给角色授权（系统权限）
GRANT CREATE SESSION, CREATE TABLE TO app_role;
-- 给角色授权（对象权限）
GRANT SELECT, INSERT ON scott.emp TO app_role;

CREATE ROLE / GRANT 语法见 SQL Language Reference。

3.5 把角色 / 权限授予用户

-- 把角色授予用户
GRANT app_role TO app_user;
-- 直接给用户系统权限
GRANT CREATE SESSION, CREATE TABLE TO app_user;
-- 给用户表空间配额（另一种方式）
GRANT UNLIMITED TABLESPACE TO app_user;  -- 或精确配额
ALTER USER app_user QUOTA 200M ON tbs_demo;

四、数据库链接（DB LINK）

4.1 创建 DB Link（在 PDB 中）

-- 创建私有数据库链接
CREATE DATABASE LINK link_remote
CONNECT TO remote_user IDENTIFIED BY "RemotePassword"
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=remote_service)))';

简化语法见官方示例。
在 CDB/PDB 环境中，DB Link 是在某个 PDB 中创建，属于该 PDB 的模式对象。

4.2 查看 DB Link

-- 查看当前容器中的数据库链接
COLUMN db_link FORMAT A30
SELECT db_link, username, host FROM dba_db_links;

4.3 使用 DB Link 访问远程表

SELECT * FROM scott.emp@link_remote;

4.4 删除 DB Link

DROP DATABASE LINK link_remote;

五、表 / 视图 / 索引等对象操作

5.1 创建表

-- 在业务用户下创建表
CREATE TABLE app_user.orders (
  order_id   NUMBER PRIMARY KEY,
  order_date DATE DEFAULT SYSDATE,
  customer_id NUMBER,
  amount     NUMBER(10,2)
) TABLESPACE tbs_demo;

CREATE TABLE 语法见 SQL Language Reference。

5.2 修改表

-- 增加列
ALTER TABLE app_user.orders ADD (status VARCHAR2(20));
-- 修改列
ALTER TABLE app_user.orders MODIFY (status VARCHAR2(50));
-- 删除列
ALTER TABLE app_user.orders DROP COLUMN status;

5.3 删除表

DROP TABLE app_user.orders PURGE;

PURGE 表示不放入回收站，直接删除。

5.4 创建视图

CREATE OR REPLACE VIEW app_user.v_order_summary AS
SELECT customer_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount
FROM   app_user.orders
GROUP BY customer_id;

5.5 创建索引

-- B-Tree 索引
CREATE INDEX app_user.idx_orders_customer_id
ON app_user.orders(customer_id)
TABLESPACE tbs_demo;
-- 唯一索引
CREATE UNIQUE INDEX app_user.uk_orders_order_id
ON app_user.orders(order_id);

CREATE INDEX 语法见 SQL Language Reference。

5.6 删除索引 / 视图

DROP INDEX app_user.idx_orders_customer_id;
DROP VIEW app_user.v_order_summary;

六、DML：增删改查

6.1 插入数据（INSERT）

INSERT INTO app_user.orders(order_id, customer_id, amount)
VALUES (1, 1001, 1234.56);
-- 提交事务
COMMIT;

6.2 查询数据（SELECT）

SELECT order_id, order_date, customer_id, amount
FROM   app_user.orders
WHERE  customer_id = 1001
ORDER BY order_date DESC;

6.3 更新数据（UPDATE）

UPDATE app_user.orders
SET    amount = 1300
WHERE  order_id = 1;
COMMIT;

6.4 删除数据（DELETE）

DELETE FROM app_user.orders
WHERE  order_id = 1;
COMMIT;

七、在 CDB/PDB 中切换容器

-- 切换到指定 PDB
ALTER SESSION SET CONTAINER = pdb_name;
-- 查看当前容器
SELECT sys_context('userenv', 'con_name') FROM dual;

很多视图（如 DBA_USERS、DBA_DATA_FILES）在 PDB 中查询时只显示本 PDB 的数据。

八、常用运维查询汇总

-- 1. 实例状态
SELECT status FROM v$instance;
-- 2. 是否为 CDB
SELECT name, cdb FROM v$database;
-- 3. 当前容器
SELECT sys_context('userenv', 'con_name') FROM dual;
-- 4. PDB 列表与状态
SELECT name, open_mode FROM v$pdbs;
-- 5. 表空间与数据文件
COLUMN file_name FORMAT A50
COLUMN tablespace_name FORMAT A20
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb
FROM   dba_data_files;
-- 6. 用户信息
SELECT username, default_tablespace FROM dba_users WHERE username = 'APP_USER';
-- 7. 用户角色/权限
SELECT granted_role FROM dba_role_privs WHERE grantee = 'APP_USER';
SELECT privilege FROM dba_sys_privs WHERE grantee = 'APP_USER';
-- 8. DB Link
SELECT db_link, username, host FROM dba_db_links;

posted @ 2026-03-02 15:06 dirgo 阅读(0) 评论(0) 收藏举报

刷新页面返回顶部

Chobits

Oracle 19c 常用运维 SQL

大部分以 sysdba 身份操作（部分操作需要普通业务用户）。

一、实例 / CDB / PDB / 监听 状态与启停

1.1 查看实例状态

1.2 查看是否为 CDB 及 CDB 状态

1.3 查看 PDB 状态

1.4 启动 / 关闭 CDB 实例（在 Linux 下 sqlplus / as sysdba）

1.5 打开 / 关闭 PDB

1.6 保存 PDB 打开状态（实现自动打开）

1.7 监听状态与启动（Linux）

二、表空间管理

2.1 查询表空间路径

2.2 创建表空间

2.3 修改表空间（增加数据文件、调整大小等）

2.4 删除表空间

三、用户、角色、权限管理

3.1 创建用户并指定默认表空间

3.2 查询用户信息、默认表空间

3.3 查询用户拥有的角色 / 权限

3.4 创建角色并授权

3.5 把角色 / 权限授予用户

四、数据库链接（DB LINK）

4.1 创建 DB Link（在 PDB 中）

4.2 查看 DB Link

4.3 使用 DB Link 访问远程表

4.4 删除 DB Link

五、表 / 视图 / 索引 等对象操作

5.1 创建表

5.2 修改表

5.3 删除表

5.4 创建视图

5.5 创建索引

5.6 删除索引 / 视图

六、DML：增删改查

6.1 插入数据（INSERT）

6.2 查询数据（SELECT）

6.3 更新数据（UPDATE）

6.4 删除数据（DELETE）

七、在 CDB/PDB 中切换容器

八、常用运维查询汇总

公告

大部分以 `sysdba` 身份操作（部分操作需要普通业务用户）。

一、实例 / CDB / PDB / 监听状态与启停

五、表 / 视图 / 索引等对象操作