1-- 清空用户数据
 2DO $$
 3DECLARE
 4    v_user_id TEXT := '78914762532372551';
 5BEGIN
 6    -- 可选：记录操作日志或检查是否存在
 7    RAISE NOTICE 'Deleting user data for user_id: %', v_user_id;
 8
 9    DELETE FROM health_management_ta404.et_user WHERE user_id = v_user_id;
10    DELETE FROM health_management_biz.health_checkin_user WHERE user_id = v_user_id;
11    DELETE FROM health_management_biz.reg_infor WHERE user_id = v_user_id;
12    DELETE FROM health_management_biz.ship_address WHERE user_id = v_user_id;
13    DELETE FROM health_management_biz.user_info_ext WHERE user_id = v_user_id;
14    DELETE FROM health_management_biz.user_points_details WHERE user_id = v_user_id;
15    DELETE FROM health_management_biz.user_points_summary WHERE user_id = v_user_id;
16
17    RAISE NOTICE 'User data deleted successfully.';
18EXCEPTION
19    WHEN OTHERS THEN
20        RAISE WARNING 'Error deleting user %: %', v_user_id, SQLERRM;
21        -- 可选择是否回滚（在 DO 块中自动回滚）
22END $$;

 1-- 锁表
 2SELECT * FROM COSTINFO c FOR UPDATE;
 3
 4-- 查询锁表信息
 5SELECT t2.username, t2.sid,t2.serial#,t3.object_name,t2.OSUSER,t2.MACHINE,t2.PROGRAM,t2.LOGON_TIME,t2.COMMAND,t2.LOCKWAIT,t2.SADDR,t2.PADDR,t2.TADDR,t2.SQL_ADDRESS,t1.LOCKED_MODE
 6FROM v$locked_object t1, v$session t2, dba_objects t3
 7WHERE t1.session_id = t2.sid AND t1.object_id = t3.object_id
 8ORDER BY t2.logon_time;
 9
10-- 查询session
11SELECT a.OS_USER_NAME,c.owner,c.object_name,b.sid,b.serial#,logon_time
12FROM v$locked_object a, v$session b, dba_objects c
13WHERE a.session_id = b.sid AND a.object_id = c.object_id
14ORDER BY b.logon_time;
15
16-- 杀掉session 'sid,serial#'
17ALTER system kill SESSION '2844,28686' IMMEDIATE;

1-- 其中username是你要创建的用户名，password是该用户的密码
2CREATE USER username IDENTIFIED BY password;
3
4-- 分配默认表空间和临时表空间
5ALTER USER username DEFAULT TABLESPACE tablespace_name
6    TEMPORARY TABLESPACE temp_tablespace;

 1-- 授权创建session权限
 2grant create session,resource TO username;
 3
 4-- 设置为管理员用户（拥有大多权限）
 5GRANT DBA TO username;
 6
 7-- 查看用户拥有权限
 8SELECT * FROM USER_SYS_PRIVS;
 9-- 查看用户拥有的角色
10SELECT * FROM USER_ROLE_PRIVS;
11-- 查看用户拥有的对象权限
12SELECT * FROM USER_TAB_PRIVS;
13-- 查看用户拥有的系统权限和角色（查询结合了系统权限和角色，提供了一个更全面的视图）
14SELECT * FROM SESSION_PRIVS;

1exp 用户名/密码[@数据库实例] 参数1=值1 参数2=值2 ... file=导出文件路径

 1-- 导出完整数据库：用户名/密码@数据库名，file=d:/daochu1.dmp 是导出的文件路径，full=y 表示导出整个数据库。
 2exp username/passwd@ip:port/db file=d:/daochu1.dmp full=y
 3
 4-- 导出某个用户表空间： 在这里，owner=user 指定了要导出的数据库用户
 5exp username/passwd@ip:port/db file=d:/daochu1.dmp owner=username
 6
 7-- 导出某个用户的某个表： tables=table1 是要导出的表名，如果需要导出多个表，则使用(table1,table2)的形式。
 8exp username/passwd@ip:port/db file=d:/daochu1.dmp tables=(table1,...)
 9
10-- 示例
11exp MOHRSS_AIO_TA404/MOHRSS_AIO_TA404@10.161.18.39:1521/orclpdb file=e:/MOHRSS_AIO_TA404.dmp owner=MOHRSS_AIO_TA404 buffer=8192

1imp 用户名/密码[@数据库实例] 参数1=值1 参数2=值2 ... file=导入文件路径

1-- 查看版本号
2select * from v$version;
3
4-- cmd 命令行执行导入
5imp username/passwd@ip:port/db file=d:/daochu1.dmp full=y ignore=y
6
7-- 示例
8imp MOHRSS_AIO_TA404_2025/MOHRSS_AIO_TA404_2025@10.161.18.39:1521/orclpdb file=e:/MOHRSS_AIO_TA404.dmp buffer=8192 ignore=y full=y

 1-- 查询库的字符集 ；SIMPLIFIED CHINESE_CHINA.AL32UTF8;
 2select userenv('language') from dual;
 3
 4# 设置临时环境变量
 5set NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8
 6
 7# 切换当前cmd使用 utf-8 编码
 8chcp 65001
 9
10# 执行导出导入命令

1-- 查看所有表（'table1','table2',...）
2SELECT LISTAGG(''''||table_name||'''' ,',') WITHIN GROUP (ORDER BY table_name) AS names
3FROM all_tables
4WHERE owner = 'MOHRSS_AIO_TA404' AND table_name!='ET_ATTACHMENT';

 1-- 创建一个名为 remote_db_link 的数据库链接，连接到远程数据库
 2CREATE DATABASE LINK remote_db_link
 3CONNECT TO remote_username IDENTIFIED BY remote_password
 4USING '(DESCRIPTION =
 5  (ADDRESS = (PROTOCOL = TCP)(HOST = remote_host)(PORT = remote_port))
 6  (CONNECT_DATA =
 7    (SERVICE_NAME = remote_service_name)
 8  )
 9)';
10-- remote_db_link：数据库链接的名称，可以自定义。
11-- remote_username：远程数据库的用户名。
12-- remote_password：远程数据库的密码。
13-- remote_host：远程数据库服务器的主机名或 IP 地址。
14-- remote_port：远程数据库监听的端口号，默认是 1521。
15-- remote_service_name：远程数据库的服务名。
16
17-- 或下面写法
18CREATE DATABASE LINK myrsjhk_db_link
19    CONNECT TO SCCARD IDENTIFIED BY SCCARD
20    USING 'ip:port/service';
21
22-- 查询远程数据库中的表数据
23SELECT * FROM employees@remote_db_link;
24
25-- 修改远程数据库表数据
26UPDATE JXJYLOG@yhwsyw_db_link SET fieldName = 'value';
27
28--  删除 DBLINK
29DROP DATABASE LINK remote_db_link;
30
31-- 查询某个用户所有的DBLINK
32SELECT * FROM DBA_DB_LINKS WHERE OWNER = 'MY_EXAM_MANAGE';
33
34-- 查询某个DBLINK
35SELECT * FROM dba_db_links WHERE db_link = 'MYRSWT_DB_LINK'

 1-- 查询某个模式（Schema）占用的物理空间大小
 2SELECT
 3    owner AS "模式名称",
 4    ROUND(SUM(bytes) / 1024 / 1024, 2) AS "占用空间（MB）"
 5FROM dba_segments
 6GROUP BY owner;
 7
 8-- 查询某个模式（Schema）占用的物理空间大小
 9SELECT 
10    u.username AS "模式名称",
11    SUM(s.bytes / 1024 / 1024) AS "占用空间（MB）"
12FROM dba_segments s JOIN dba_users u ON s.owner = u.username
13GROUP BY 
14    u.username
15ORDER BY u.username;
16
17-- 查询某个模式（Schema）各个对象占用的物理空间大小
18SELECT owner,
19       segment_name,
20       segment_type,
21       ROUND(bytes / 1024 / 1024, 2) AS SIZE_MB
22FROM dba_segments
23WHERE owner = 'MOHRSS_AIO_TA404'
24ORDER BY SIZE_MB DESC;
25
26-- 查询某个模式（Schema）表占用的物理空间大小
27SELECT
28    t.owner,
29    t.table_name,
30    ROUND(s.bytes / 1024 / 1024, 2) AS "表占用空间（MB）"
31FROM dba_tables t
32JOIN dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
33WHERE t.owner = 'YOUR_SCHEMA_NAME'
34ORDER BY "表占用空间（MB）" DESC;

1# 该语句会返回数据库中所有用户的用户名、账户状态以及密码过期日期。如果expiry_date列的值为NULL，表示该用户的密码没有设置过期时间；如果有具体的日期值，则表示该用户的密码将在该日期过期。
2SELECT username, account_status, expiry_date FROM dba_users;
3
4# 同样以管理员身份连接到数据库，先查询用户对应的概要文件，一般用户的概要文件为DEFAULT
5SELECT username, PROFILE FROM dba_users;
6# 然后查询对应概要文件的密码有效期设置,如果返回的LIMIT值为UNLIMITED，表示密码永不过期；如果是一个数字，则表示密码的有效期为该数字指定的天数。
7SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

 1-- ======================================
 2-- 核心操作：新建数据库、用户、专属模式（超级管理员 postgres 执行）
 3-- 适用场景：为业务用户分配专属数据库/模式，保障数据隔离与权限可控
 4-- ======================================
 5
 6-- 1. 新建数据库 test_db，指定 UTF-8 编码（避免中文乱码）
 7CREATE DATABASE test_db
 8  WITH ENCODING 'UTF8'
 9       CONNECTION LIMIT -1;      -- 连接数限制（-1 表示无限制，可根据业务调整为具体数值，如 100）
10
11-- 2. 新建数据库用户 test_user，设置强密码、指定连接有效期（可选）
12CREATE USER test_user
13  WITH PASSWORD 'LongerPassword123.'  -- 登录密码
14       VALID UNTIL '2027-12-31 23:59:59'  -- 密码有效期（可选，不写则永久有效）
15       CONNECTION LIMIT 50;  -- 该用户单实例连接数限制（可选，配合数据库连接限制使用）
16
17-- 3. 新建专属模式 schema_a，直接指定拥有者为 test_user（简化后续授权，test_user 默认拥有该模式全部权限）
18-- 备注：模式（Schema）用于数据库内对象隔离，避免多业务表名冲突，相当于「数据库内的子目录」
19CREATE SCHEMA schema_a AUTHORIZATION test_user;
20
21-- ======================================
22-- 后续操作：test_user 登录使用（无需超级管理员权限）
23-- ======================================
24-- 登录命令（终端执行）：psql -U test_user -d test_db -h localhost -p 5432
25-- 登录后默认操作 schema_a 步骤（可选，避免每次操作都写 schema_a.表名）：
26--   1. 设置当前会话默认模式：SET search_path TO schema_a, public;
27--   2. （可选）修改用户永久默认模式：ALTER USER test_user SET search_path TO schema_a, public;
28--
29-- 登录后可直接在 schema_a 下创建表/视图/索引等对象，所有对象所有者均为 test_user
30-- 示例：CREATE TABLE schema_a.user_info (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL);
31-- （若已设置 search_path，可简化为：CREATE TABLE user_info (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL);）
32
33-- ======================================
34-- 补充操作：额外授权命令（根据业务场景选择，超级管理员 postgres 执行）
35-- 适用场景：多人共享 test_db 数据库、需要给 test_user 分配 public 模式权限等
36-- ======================================
37
38-- 场景1：将 test_db 数据库所有权转移给 test_user（权限最高，适合 test_user 专属数据库）
39-- 备注：转移后 test_user 成为数据库所有者，拥有该数据库内所有操作权限（包括删除数据库）
40ALTER DATABASE test_db OWNER TO test_user;
41
42-- 场景2：授予 test_user 对 test_db 数据库的全部操作权限（不转移所有权，适合多人共享数据库）
43-- 备注：仅数据库层面权限（连接、创建模式等），不包含模式内对象的操作权限
44GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user;
45
46-- 场景3：授予 test_user 对 public 模式的全部操作权限（创建、修改、删除模式内对象）
47-- 备注：public 是 PostgreSQL 默认模式，未指定模式时对象会创建在该模式下
48GRANT ALL PRIVILEGES ON SCHEMA public TO test_user;
49
50-- 场景4：授予 test_user 对 public 模式下现有对象的全部操作权限
51-- 备注：仅对「已存在」的表、序列、函数生效，后续新建对象需依赖默认权限设置
52GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test_user;
53GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO test_user;  -- 自增字段依赖序列，需单独授权
54GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO test_user;
55
56-- 场景5：设置 public 模式下「未来新建」对象的默认权限（避免后续新建对象无权限）
57-- 备注：仅对「超级管理员或拥有模式权限的用户」后续新建的对象生效，保障权限继承
58ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO test_user;
59ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO test_user;
60ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO test_user;
61
62-- 场景6：删除用户
63-- 将 test_user 拥有的所有数据库的 ownership 转移给 postgres（或其他管理员）
64REASSIGN OWNED BY test_user TO postgres;
65-- 然后删除该用户拥有的所有权限和依赖对象（安全清理）
66DROP OWNED BY test_user;
67-- 再删除用户
68DROP USER test_user;
69
70-- 场景7：查询所有用户（角色）信息
71-- 备注：在 PostgreSQL 中，用户（User）本质上是具有 LOGIN 权限的角色（Role）
72SELECT rolname AS username,
73       rolsuper AS is_superuser,
74       rolcreatedb AS can_create_db,
75       rolcreaterole AS can_create_role,
76       rolcanlogin AS can_login,
77       rolreplication AS can_replicate,
78       rolconnlimit AS connection_limit,
79       rolvaliduntil AS password_valid_until
80FROM pg_roles
81ORDER BY rolname;

 1-- 查询face_db数据库的活跃 / 非内部连接详情
 2SELECT 
 3  pid,  -- 连接的唯一进程ID（PostgreSQL内部标识每个连接的进程号）
 4  datname,  -- 连接的数据库名（这里过滤后只显示face_db）
 5  usename,  -- 发起连接的数据库用户名（比如fastapi_user）
 6  client_addr,  -- 客户端IP（能定位到是哪个FastAPI服务实例连过来的）
 7  application_name,  -- 应用名（如果FastAPI连接时配置了这个参数，能直接识别业务模块）
 8  state,  -- 连接状态（关键！比如：
 9          -- active：正在执行SQL；
10          -- idle：空闲（没执行SQL）；
11          -- idle in transaction：事务中空闲（最危险，占连接且未提交/回滚）；
12          -- idle in transaction (aborted)：事务出错但未终止
13  state_change,  -- 连接状态最后一次变更的时间（比如idle状态持续了多久）
14  backend_start,  -- 连接建立的时间（能看连接是否长期不释放）
15  query,  -- 该连接最后/正在执行的SQL（能定位到慢SQL/异常SQL，比如未提交的DELETE）
16  now() - backend_start AS connection_age,  -- 连接存活总时长（排查长连接泄漏）
17  now() - state_change AS state_age  -- 当前状态持续时长（比如idle状态卡了几小时）
18FROM pg_stat_activity 
19WHERE datname = 'face_db'  -- 只查face_db数据库的连接
20  AND client_addr is not null;  -- 排除数据库内部连接（比如后台进程、本机localhost无IP的连接）
21  
22SELECT pg_terminate_backend(4750);  -- 终止指定 PID 的数据库连接
23
24SELECT * FROM pg_stat_activity WHERE pid = 4723;  -- 查询指定 PID 连接的完整详情

 1# PGPASSWORD 是 PostgreSQL 客户端（如 psql、pg_dump 等）用来自动提供密码的环境变量
 2# cmd:  set PGPASSWORD=your_password
 3# powershell:  $env:PGPASSWORD = "your_password"
 4
 5# 导出
 6pg_dump.exe --verbose \    
 7  --host=192.168.116.131 --port=5432 \
 8  --username=postgres \
 9  --format=t --encoding=UTF-8 \
10  --file E:/dump-404 \
11  --no-owner --no-privileges \
12  -n "health_management_ta404" postgres
13  
14  
15# 导入
16pg_restore.exe --verbose \
17  --host=20.1.1.5 --port=5433 \
18  --username=postgres \
19  --dbname=health_db \
20  --no-owner --no-privileges \
21  E:/dump-404

1CREATE USER "KXCWT_TA404" IDENTIFIED BY "Dameng123" DEFAULT TABLESPACE MAIN;
2GRANT CREATE SESSION TO "KXCWT_TA404";  -- 授予连接权限
3GRANT RESOURCE TO "KXCWT_TA404";   -- 授予建表等基本资源权限
4# GRANT DBA TO "KXCWT_TA404";   -- 或者一次性授予 DBA 权限（谨慎使用）

1# 导出 KXCWT_TA404 模式表到 KXCWT_TA404.dmp
2dexp USERID=KXCWT_TA404/Dameng123@127.0.0.1:5236 FILE=KXCWT_TA404.dmp SCHEMAS=KXCWT_TA404
3
4# 导入到 KXCHX_TA404 模式
5dimp USERID=KXCHX_TA404/Dameng123@127.0.0.1:5236 FILE=KXCWT_TA404.dmp REMAP_SCHEMA=KXCWT_TA404:KXCHX_TA404