通用银行业务表结构说明
所有关系型数据库统一创建以下5张表：
| 表名 | 说明 | 数据量策略 |
|---|---|---|
csdu_customer |
客户信息表 | 10万条，约100MB |
csdu_account |
账户信息表 | 20万条，约150MB |
csdu_transaction |
交易流水表（主力大表） | 3000万条+大字段，约5-10GB |
csdu_loan |
贷款信息表 | 5万条，约50MB |
csdu_deposit_product |
存款产品表 | 100条，约1MB |
csdu_transaction 大字段造数策略: trans_detail 字段填充 REPEAT('CSDUN_LARGE_DATA_', 500) + 序号，单条约10KB，3000万条约300GB原始文本，经压缩后实际占用5-10GB。
1. Oracle
1.1 技术原理
Oracle采用表空间(Tablespace)管理存储，数据以数据块(Block)为单位存储在数据文件中。核心组件包括：
-
实例(Instance): 内存结构(SGA) + 后台进程，是数据库的运行时环境
-
数据库(Database): 物理数据文件集合，与实例可以一对多（RAC）
-
归档模式(ARCHIVELOG): 开启后，redo日志在覆盖前会被归档保存，支持时间点恢复(PITR)
-
备份恢复: RMAN(Recovery Manager)是专用工具，支持全备、增量备、镜像备份
1.2 创建数据库/用户
-- 创建表空间
CREATE TABLESPACE csdu_ts DATAFILE '/u01/app/oracle/oradata/csdu_ts01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 20G;
-- 创建用户（Oracle使用Schema即用户）
CREATE USER csdu IDENTIFIED BY csdu_password DEFAULT TABLESPACE csdu_ts QUOTA UNLIMITED ON csdu_ts;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE, DBA TO csdu;
-- 切换用户
CONN csdu/csdu_password;
1.3 创建业务表
-- 客户信息表
CREATE TABLE csdu.csdu_customer (
cust_id VARCHAR2(32) PRIMARY KEY,
cust_name VARCHAR2(100),
id_type VARCHAR2(20),
id_no VARCHAR2(50),
phone VARCHAR2(30),
address VARCHAR2(500),
cust_type CHAR(1),
risk_level CHAR(1),
open_date DATE,
status CHAR(1),
branch_no VARCHAR2(20),
remark CLOB,
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 账户信息表
CREATE TABLE csdu.csdu_account (
acct_no VARCHAR2(32) PRIMARY KEY,
cust_id VARCHAR2(32),
acct_type CHAR(1),
currency CHAR(3),
balance NUMBER(18,2),
available_amt NUMBER(18,2),
open_date DATE,
close_date DATE,
status CHAR(1),
branch_no VARCHAR2(20),
passwd VARCHAR2(100),
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 交易流水表（主力大表）
CREATE TABLE csdu.csdu_transaction (
trans_id VARCHAR2(64) PRIMARY KEY,
acct_no VARCHAR2(32),
trans_type VARCHAR2(10),
trans_date DATE,
trans_time TIMESTAMP,
dr_cr_flag CHAR(1),
trans_amt NUMBER(18,2),
balance NUMBER(18,2),
opp_acct_no VARCHAR2(32),
opp_name VARCHAR2(100),
opp_bank_no VARCHAR2(20),
trans_channel VARCHAR2(20),
trans_summary VARCHAR2(200),
trans_detail CLOB,
teller_no VARCHAR2(20),
branch_no VARCHAR2(20),
check_flag CHAR(1),
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 贷款信息表
CREATE TABLE csdu.csdu_loan (
loan_no VARCHAR2(32) PRIMARY KEY,
cust_id VARCHAR2(32),
acct_no VARCHAR2(32),
loan_type VARCHAR2(20),
loan_amt NUMBER(18,2),
loan_balance NUMBER(18,2),
rate NUMBER(8,4),
start_date DATE,
end_date DATE,
term_months NUMBER(10),
repay_type CHAR(1),
status CHAR(1),
guarantee_type VARCHAR2(20),
purpose VARCHAR2(500),
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 存款产品表
CREATE TABLE csdu.csdu_deposit_product (
prod_code VARCHAR2(20) PRIMARY KEY,
prod_name VARCHAR2(100),
prod_type CHAR(1),
min_amt NUMBER(18,2),
term_days NUMBER(10),
rate NUMBER(8,4),
auto_renew CHAR(1),
status CHAR(1),
create_date DATE,
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
1.4 插入测试数据（5-10GB）
-- 插入客户数据
INSERT INTO csdu.csdu_customer
SELECT
'C' || LPAD(ROWNUM, 10, '0'),
'CSDUN客户_' || ROWNUM,
DECODE(MOD(ROWNUM,3),0,'身份证',1,'护照','军官证'),
'csdun_' || LPAD(ROWNUM, 18, '0'),
'138' || LPAD(MOD(ROWNUM,100000000), 8, '0'),
'CSDUN测试地址_' || ROWNUM || REPEAT('_地址补充', 20),
DECODE(MOD(ROWNUM,2),0,'1','2'),
DECODE(MOD(ROWNUM,5),0,'A',1,'B',2,'C',3,'D','E'),
SYSDATE - MOD(ROWNUM, 3650),
'1',
'BR' || LPAD(MOD(ROWNUM,999), 3, '0'),
REPEAT('CSDUN_REMARK_', 100),
'csdun_' || ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 100000;
-- 插入账户数据
INSERT INTO csdu.csdu_account
SELECT
'6222' || LPAD(ROWNUM, 10, '0'),
'C' || LPAD(MOD(ROWNUM,100000), 10, '0'),
DECODE(MOD(ROWNUM,3),0,'1',1,'2','3'),
'CNY',
ROUND(DBMS_RANDOM.VALUE(0, 1000000), 2),
ROUND(DBMS_RANDOM.VALUE(0, 1000000), 2),
SYSDATE - MOD(ROWNUM, 3650),
NULL,
'1',
'BR' || LPAD(MOD(ROWNUM,999), 3, '0'),
'csdun_passwd_' || ROWNUM,
'csdun_' || ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 200000;
-- 插入交易流水（主力大表，PL/SQL存储过程批量插入3000万条）
CREATE OR REPLACE PROCEDURE insert_csdu_trans AS
BEGIN
FOR i IN 1..30000000 LOOP
INSERT INTO csdu.csdu_transaction VALUES (
'TXN' || LPAD(i, 15, '0'),
'6222' || LPAD(MOD(i,200000), 10, '0'),
DECODE(MOD(i,5),0,'DEPOSIT',1,'WITHDRAW',2,'TRANSFER',3,'PAYMENT','FEE'),
SYSDATE - MOD(i,365),
SYSDATE - i/86400,
DECODE(MOD(i,2),0,'1','2'),
ROUND(DBMS_RANDOM.VALUE(1,100000),2),
ROUND(DBMS_RANDOM.VALUE(1,500000),2),
'6222' || LPAD(MOD(i+1,200000), 10, '0'),
'CSDUN测试对方户名_' || i,
'104' || LPAD(MOD(i,10000), 8, '0'),
DECODE(MOD(i,4),0,'COUNTER',1,'ATM',2,'MOBILE','WEB'),
'CSDUN银行业务测试交易摘要-' || i,
REPEAT('CSDUN_LARGE_DATA_', 500) || i,
'T' || LPAD(MOD(i,9999), 4, '0'),
'BR' || LPAD(MOD(i,999), 3, '0'),
'0',
'csdun_' || i
);
IF MOD(i, 10000) = 0 THEN COMMIT; END IF;
END LOOP;
COMMIT;
END;
/
EXEC insert_csdu_trans;
-- 插入贷款数据
INSERT INTO csdu.csdu_loan
SELECT
'L' || LPAD(ROWNUM, 10, '0'),
'C' || LPAD(MOD(ROWNUM,100000), 10, '0'),
'6222' || LPAD(MOD(ROWNUM,200000), 10, '0'),
DECODE(MOD(ROWNUM,4),0,'个人消费贷',1,'住房按揭',2,'企业经营贷','小微企业贷'),
ROUND(DBMS_RANDOM.VALUE(10000, 1000000), 2),
ROUND(DBMS_RANDOM.VALUE(1000, 500000), 2),
ROUND(DBMS_RANDOM.VALUE(0.03, 0.06), 4),
SYSDATE - MOD(ROWNUM, 3650),
SYSDATE + MOD(ROWNUM, 3650),
MOD(ROWNUM, 360),
DECODE(MOD(ROWNUM,3),0,'1',1,'2','3'),
DECODE(MOD(ROWNUM,3),0,'1',1,'2','3'),
DECODE(MOD(ROWNUM,4),0,'信用',1,'抵押',2,'质押','保证'),
'CSDUN测试贷款用途_' || ROWNUM,
'csdun_' || ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 50000;
-- 插入存款产品
INSERT INTO csdu.csdu_deposit_product VALUES
('P001', 'CSDUN活期存款', '1', 0.00, 0, 0.0030, '1', '1', SYSDATE, 'csdun_001'),
('P002', 'CSDUN三个月定期', '2', 50000.00, 90, 0.0145, '1', '1', SYSDATE, 'csdun_002'),
('P003', 'CSDUN一年定期', '2', 50000.00, 365, 0.0175, '1', '1', SYSDATE, 'csdun_003'),
('P004', 'CSDUN三年大额存单', '3', 200000.00, 1095, 0.0285, '1', '1', SYSDATE, 'csdun_004'),
('P005', 'CSDUN结构性存款', '4', 100000.00, 180, 0.0350, '0', '1', SYSDATE, 'csdun_005');
1.5 数据库状态查询
-- 实例状态
SELECT instance_name, status, database_status FROM v$instance;
-- 数据库打开模式、归档模式
SELECT name, open_mode, log_mode FROM v$database;
-- 会话状态
SELECT sid, serial#, username, status, machine, program FROM v$session WHERE username='CSDU';
-- 表空间使用率
SELECT tablespace_name, ROUND(used_space*8192/1024/1024/1024,2) used_gb,
ROUND(tablespace_size*8192/1024/1024/1024,2) total_gb,
ROUND((used_space/tablespace_size)*100,2) pct_used
FROM dba_tablespace_usage_metrics WHERE tablespace_name='CSDU_TS';
1.6 归档查询
-- 是否开启归档
SELECT log_mode FROM v$database;
-- 归档日志列表
ARCHIVE LOG LIST;
-- 归档目的地
SELECT destination, status, space_limit/1024/1024/1024 space_limit_gb,
space_used/1024/1024/1024 space_used_gb
FROM v$archive_dest WHERE status != 'INACTIVE';
1.7 数据量统计
-- CSDU用户下所有表大小
SELECT table_name, ROUND(SUM(bytes)/1024/1024/1024,2) gb
FROM dba_segments WHERE owner='CSDU' GROUP BY table_name ORDER BY gb DESC;
-- 交易流水表记录数
SELECT COUNT(*) FROM csdu.csdu_transaction;
-- 按天统计交易量
SELECT trans_date, COUNT(*) FROM csdu.csdu_transaction GROUP BY trans_date ORDER BY trans_date;
1.8 性能查询
-- Top 10慢SQL
SELECT sql_id, elapsed_time/1000000 sec, sql_text
FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
-- 交易流水表全表扫描性能
SET TIMING ON;
SELECT COUNT(*) FROM csdu.csdu_transaction WHERE trans_detail LIKE '%CSDUN_LARGE_DATA_1000000%';
SET TIMING OFF;
1.9 删除/清理语句
-- 方式1：删除测试数据（保留表结构，可重复测试）
DELETE FROM csdu.csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_loan WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_deposit_product WHERE csdun_tag LIKE 'csdun_%';
COMMIT;
-- 方式2：截断表（快速清空，不可回滚）
TRUNCATE TABLE csdu.csdu_transaction;
TRUNCATE TABLE csdu.csdu_account;
TRUNCATE TABLE csdu.csdu_customer;
TRUNCATE TABLE csdu.csdu_loan;
TRUNCATE TABLE csdu.csdu_deposit_product;
-- 方式3：彻底删除（删除用户和表空间）
DROP USER csdu CASCADE;
DROP TABLESPACE csdu_ts INCLUDING CONTENTS AND DATAFILES;
1.10 备份恢复
-- RMAN联机备份
RMAN> BACKUP TABLESPACE csdu_ts FORMAT '/backup/csdu_ts_%U.bak';
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
-- 恢复
RMAN> STARTUP MOUNT;
RMAN> RESTORE TABLESPACE csdu_ts;
RMAN> RECOVER TABLESPACE csdu_ts;
RMAN> ALTER DATABASE OPEN;
