1 数据库操作：
1.1 --调用存储过程：
DECLARE
P_DATA_DATE VARCHAR2(32767);--定义输入参数
P_O_RESULT VARCHAR2(32767);
BEGIN
P_DATA_DATE := '20220320';--输入日期
P_O_RESULT := NULL;
TPS.PR_TP_RBC34_TMP ( P_DATA_DATE, P_O_RESULT );--输入要执行的存储过程
COMMIT;
END;
1.2 存储过程日志
SELECT * FROM ETL_TRACE_LOG T --WHERE T.PROCE_NAME='PR_TP_RB29'
order by t.end_time desc;--成功的
SELECT * FROM ETL_ERROR_LOG T --WHERE T.PROCE_NAME='PR_TP_RB1032T'
ORDER BY T.LOG_DATE DESC--失败的
1.3 打印日志信息：
DBMS_OUTPUT.put_line('插入数据完毕');
1.4 存储过程访问授权：
grant execute on get_acct_flag（存储过程） to TPS（用户名）;
1.5 查存储过程中的表
select * from user_dependencies where NAME='PR_TP_RB26' AND referenced_type='TABLE' AND type='PROCEDURE';
1.6 索引
1.6.1 查看表使用的索引
select * from ALL_INDEXES WHERE TABLE_NAME=' sta.sym_rb_tda_hist';
select a.* ,b.index_type from user_ind_columns a,user_indexes b where a.INDEX_NAME=b.index_name and a.TABLE_NAME= 'rb_base_acct';
1.6.2 查看索引是否失效
select STATUS from user_indexes where index_name='RB_AUDIT_LOG_IND2';--VALID则没有失效
1.6.3 创建索引
create index idx_base_acct_no on sym_fm_client_declare(base_acct_no);
1.6.4 删除索引：
drop index idx_base_acct_no;
1.7 执行计划(F5或者ctrl+E)
Explain plan for select * from dual;
Select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));--v$sql 当前
select * from table(dbms_xplan.display_awr('sql_id'));--查看历史执行计划
表连接：
排序-合并连接（sort merge join SMJ）：非等值连接，效率比较高，若关联列上都有索引，效率更好，两个较大的row source做连接，比NL连接好一些。但如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为有过多的I/O.
嵌套循环连接（nested loops NL）:驱动表去扫描匹配表里面的每一行，如果外部表比较小，且内部表上有唯一索引，或者有高选择性非唯一索引时，这种效率更好。
哈希连接（hash join HJ）：只能用在CBO优化器中，需要设置合适的hash_area_size参数，才能有更好的效率。有2个较大的row source 之间连接时会取得较好的效率，再一个row source 较小时则能取得更好的效率。只能用于等值连接中
表访问方式：
全表扫描
索引扫描：索引唯一扫描
索引范围扫描
索引全局扫描
索引快速扫描
索引跳跃扫描
1.9 增加分区：
创建表时添加;partition by list (DATA_DATE)
( partition P_INIT values ('99991231'));
创建表之后增加分区：ALTER TABLE TP_RBXQ06 ADD PARTITION P_202301 VALUES(202301);
删除分区：ALTER TABLE TP_DC18T DROP PARTITION P_20230312;
1.10 对表/字段操作
1.10.1 修改列类型：
ALTER TABLE TP_RBS34_TMP MODIFY( FLAG_ACCT_NO VARCHAR2(20 CHAR));
1.10.2 增加列：
ALTER TABLE TP_RB55 ADD seq_no NUMBER;
1.10.3 删除列：
ALTER TABLE TP_RBD03 drop column INTERNAL_KEY;
1.10.4 修改列名
ALTER TABLE TP_RB55 RENAME COLUMN DATE TO DATA_DATE;
1.10.5 更新某个字段的数据：
update 表名
set 字段 =REPLACE(字段,值，值) where…;commit;
1.10.6 删除某一行数据：
delete from sta.sym_rb_aio_acct where AIO_INTERNAL_KEY='202211012022';commit;
1.10.7 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;
1.10.8 复制表结构
CREATE TABLE TP_1 AS SELECT * FROM TP_2 WHERE 1=2;
1.10.9 src_tab表中的数据是根据接口文档修改为大写的表名
SELECT DISTINCT SUBSTR (REFERENCED_NAME, 5, LENGTH (REFERENCED_NAME) - 4)
FROM user_dependencies
WHERE REFERENCED_OWNER = 'STA'
AND SUBSTR (REFERENCED_NAME, 5, LENGTH (REFERENCED_NAME) - 4) NOT IN (SELECT TABNAME FROM src_tab);
1.11 DROP 表后回退
flashback table SYMBOLS.FM_ECIF_CLIENT_MAPPING to before drop rename to FM_ECIF_CLIENT_MAPPING_bk
1.12 系统表
select* from user_tab_columns --查看字段
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' --查看主键
select * from user_constraints c where c.constraint_type = 'R' --查看外键
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name --查看索引
select * from user_tab_comments --表与表描述
select * from user_col_comments --表字段与描述
select * from user_views --查看视图
1.12.1 查某个存储过程运用到的某张表或者字段
select * from all_source where upper(text) like upper('%cd_card_acct%')
and upper(text) like upper('%card_type%')
and type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
1.13 DBA操作
select * from dba_role_privs where grantee='NDSRP_SYM'--查询用户权限
revoke dba from NDSRP_SYM--回收用户权限
drop user ndsrp_front cascade--删除创建的用户
1.13.1 查看表空间大小及使用率等
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name, SUM(bytes_cached) free
FROM v$temp_extent_pool
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name, SUM(bytes) total
FROM dba_temp_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
1.13.2 查看表是否被锁
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;
--锁表解锁
select s.username,s.sid,s.serial#,s.logon_time from v$locked_object l,v$session s where l.session_id=s.sid order by s.logon_time;
alter system kill session 'sid,serial#'
1.13.3 赋权，修改密码，账号被锁
用oracle /oracle 登录
查看环境变量：echo $ORACLE_SID
切换实例： export ORACLE_SID=rpeod
//回收权限
REVOKE 角色|权限FROM 用户（角色）
//修改用户的密码
ALTER USER 用户名IDENTIFIED BY 新密码
//修改用户处于锁定（非锁定）状态
ALTER USER 用户名ACCOUNT LOCK|UNLOCK
1.13.4 查询耗时sql
select * from (
select * from V$SQLSTATS
-- 最耗时的 SQL
-- ELAPSED_TIME 指的是总耗时（毫秒），平均耗时 = ELAPSED_TIME/EXECUTIONS
order by ELAPSED_TIME DESC
-- 查询执行次数最多的 SQL
-- order by EXECUTIONS DESC
-- 读硬盘最多的 SQL
-- order by DISK_READS DESC
-- 最费 CPU 的 SQL
-- order by BUFFER_GETS DESC
) where rownum <=50;
1.13.5 查存储过程相关信息
SELECT X.OWNER
, X.NAME AS PROC_NAME
, X.TEXT
, X.LINE
, X.TYPE
FROM ALL_SOURCE X
WHERE X.TYPE IN ('PACKAGE','PACKAGE BODY','FUNCTION', 'PROCEDURE')
AND X.OWNER ='MDMS';
1.13.6 表与注释信息
/*
提取表信息
*/
WITH tab_info AS (
/* 汇总表及表注释信息 */
SELECT tab.owner
, tab.table_name
, tab_cmts.comments
FROM dba_tables tab LEFT JOIN dba_tab_comments tab_cmts
ON tab.owner = tab_cmts.owner
AND tab.table_name = tab_cmts.table_name
WHERE tab.owner = 'VLOG' /* 变量绑定需要提取表信息的Schema名称 */
AND tab.owner = tab_cmts.owner
-- AND tab.table_name = 'TMP_ZXXH_QUER' /*变量绑定需要提取单个表信息的Table名称,多个表用in()*/
-- AND comments IS NOT NULL /*过滤表注释信息不为空的表*/
), pk_info AS (
/* 汇总主键信息 */
SELECT cnstrt.owner
, cnstrt.table_name
, 'Y' AS pk_yn
FROM dba_constraints cnstrt
WHERE owner = 'VLOG' /* 变量绑定需要提取主键信息的Schema名称 */
-- AND cnstrt.table_name = 'TMP_ZXXH_QUER' /*变量绑定需要提取单个表主键信息的Table名称,多个表用in()*/
AND constraint_type = 'P')
SELECT tab_info.owner AS "模式名(SCHEMA)"
, tab_info.table_name AS "表英文名"
, tab_info.comments AS "表中文名"
, '' AS "表描述"
, '' AS "表类型"
, coalesce(pk_info.pk_yn,'N') AS "是否存在主键"
, '' AS "重要程度"
, '' AS "系统模块"
FROM tab_info
LEFT JOIN pk_info ON ( tab_info.owner = pk_info.owner AND tab_info.table_name = pk_info.table_name );
1.13.7 字段信息
/*
提取字段信息
*/
WITH tab_col_info AS (
/* 提取字段信息 */
SELECT tab_col.owner
, tab_col.table_name
, tab_cmts.comments AS tab_cmts
, tab_col.column_id
, tab_col.column_name
, col_cmts.comments AS col_cmts
, tab_col.data_type
, tab_col.data_length
, tab_col.data_precision
, CASE WHEN tab_col.CHAR_USED = 'C' AND tab_col.CHARACTER_SET_NAME != 'NCHAR_CS'
THEN tab_col.DATA_TYPE||'('||tab_col.CHAR_LENGTH||' CHAR)'
WHEN tab_col.CHAR_USED = 'B' OR tab_col.CHARACTER_SET_NAME = 'NCHAR_CS'
THEN tab_col.DATA_TYPE||'('||tab_col.CHAR_LENGTH||')'
WHEN (tab_col.DATA_PRECISION IS NOT NULL OR tab_col.DATA_SCALE IS NOT NULL)
AND LENGTH(REPLACE(tab_col.DATA_TYPE,'TIMESTAMP',''))=LENGTH(tab_col.DATA_TYPE)
THEN tab_col.DATA_TYPE||'('||NVL(TO_CHAR(tab_col.DATA_PRECISION),'*')||
CASE WHEN tab_col.DATA_PRECISION IS NOT NULL AND NVL(tab_col.DATA_SCALE,0) =0 THEN NULL
ELSE ','||tab_col.DATA_SCALE END ||')'
ELSE tab_col.data_type
END AS col_data_type
, tab_col.data_scale
, tab_col.nullable
FROM dba_tab_columns tab_col
INNER JOIN dba_tables tab
ON tab_col.owner = tab.owner
AND tab_col.table_name = tab.table_name
LEFT JOIN dba_tab_comments tab_cmts
ON tab_col.owner = tab_cmts.owner
AND tab_col.table_name = tab_cmts.table_name
LEFT JOIN dba_col_comments col_cmts
ON tab_col.owner = col_cmts.owner
AND tab_col.table_name = col_cmts.table_name
AND tab_col.column_name = col_cmts.column_name
WHERE tab_col.owner = 'VLOG' /* 变量绑定需要提取字段信息的Schema名称 */
-- AND tab_col.table_name = 'AP88BUG' /*变量绑定需要提取单个表字段信息的Table名称,多个表用in()*/
-- AND tab_cmts.comments IS NOT NULL
-- AND col_cmts.comments IS NOT NULL
), pk_col_info AS (
/* 提取主键字段信息 */
SELECT cons.owner
, cons.table_name
, cons.constraint_name
, cons_cols.column_name
, 'Y' AS pk_yn
FROM dba_constraints cons, dba_cons_columns cons_cols
WHERE cons.owner = 'VLOG' /* 变量绑定需要提取主键字段信息的Schema名称 */
AND cons.constraint_type = 'P'
AND cons.owner = cons_cols.owner
AND cons.table_name = cons_cols.table_name
AND cons.constraint_name = cons_cols.constraint_name
-- AND cons_cols.table_name = 'AP88BUG' /*变量绑定需要提取单个表主键字段信息的Table名称,多个表用in()*/
)
SELECT tab_col_info.owner AS "模式名(SCHEMA)"
, tab_col_info.table_name AS "表英文名"
, column_id AS "字段序号"
, tab_col_info.column_name AS "字段英文名"
, col_cmts AS "字段中文名"
, col_data_type AS "字段数据类型"
, coalesce(pk_col_info.pk_yn,'N') AS "是否主键"
, nullable AS "是否允许空值"
, '' AS "是否代码字段"
, '' AS "是否引用代码表"
, '' AS "字段注释说明"
FROM tab_col_info
LEFT JOIN pk_col_info
ON tab_col_info.owner = pk_col_info.owner
AND tab_col_info.table_name = pk_col_info.table_name
AND tab_col_info.column_name = pk_col_info.column_name
ORDER BY tab_col_info.table_name,column_id;
1.13.8 视图信息
/*
提取视图信息
*/
SELECT vw.owner AS "模式名(SCHEMA)"
, vw.view_name AS "视图英文名"
, vw_cmts.comments AS "视图中文名"
, '' AS "视图描述"
FROM dba_views vw
LEFT JOIN dba_tab_comments vw_cmts
ON vw.owner = vw_cmts.owner
AND vw.view_name = vw_cmts.table_name
WHERE vw.owner = 'MDMSA' /* 变量绑定需要提取视图信息的Schema名称 */
-- AND vw.view_name = '' /*变量绑定需要提取单个视图信息的View名称,多个视图用in()*/
AND vw_cmts.table_type = 'VIEW';
1.13.9 视图字段信息
/*
提取视图字段信息
*/
SELECT vw.owner AS "模式名(SCHEMA)"
, vw.view_name AS "视图英文名"
, vw_cols.column_id AS "字段序号"
, vw_cols.column_name AS "字段英文名"
, vw_col_cmts.comments AS "字段中文名"
FROM dba_views vw
LEFT JOIN dba_tab_columns vw_cols
ON vw.owner = vw_cols.owner
AND vw.view_name = vw_cols.table_name
LEFT JOIN dba_col_comments vw_col_cmts
ON vw_cols.owner = vw_col_cmts.owner
AND vw_cols.table_name = vw_col_cmts.table_name
AND vw_cols.column_name = vw_col_cmts.column_name
WHERE vw.owner = 'MDMSA' /* 变量绑定需要提取视图信息的Schema名称 */
-- AND vw.view_name = '' /*变量绑定需要提取单个视图信息的View名称,多个视图用in()*/
;
1.13.10 索引信息
/*
提取索引信息
*/
SELECT a.owner AS "模式名(SCHEMA)"
, a.index_name AS "索引名"
, a.table_name AS "表英文名"
, CASE
WHEN a.uniqueness = 'UNIQUE'
THEN 'Y'
ELSE 'N' END AS "是否唯一索引"
FROM dba_indexes a
WHERE owner = 'MDMSA' /* 变量绑定需要提取表索引信息的Schema名称 */
AND EXISTS (
SELECT 1
FROM dba_ind_columns b
WHERE a.index_name = b.index_name
AND a.owner = b.index_owner
)
-- AND a.table_name = '' /*变量绑定需要提取单个表索引信息的Table名称,多个表索引用in()*/
;
1.13.11 索引字段信息
/*
提取索引字段
*/
SELECT a.index_owner AS "模式名(SCHEMA)"
, a.index_name AS "索引名"
, a.table_name AS "表英文名"
, a.column_name AS "索引字段英文名"
, a.column_position AS "索引字段序号"
FROM dba_ind_columns a
INNER JOIN dba_indexes b
ON a.index_owner = b.owner
AND a.index_name = b.index_name
INNER JOIN dba_tab_columns c
ON a.table_owner = c.owner
AND a.table_name = c.table_name
AND a.column_name = c.column_name
WHERE index_owner = 'MDMSA' /* 变量绑定需要提取表索引字段信息的Schema名称 */
AND EXISTS (
SELECT 1
FROM dba_tables d
WHERE d.table_name = a.table_name
AND d.owner = a.table_owner
)
-- AND a.table_name = '' /*变量绑定需要提取单个表索引字段信息的Table名称,多个表索引字段用in()*/
;
1.14 ORACLE常用函数
1.14.1 查一行中的最大值函数
GREATEST（A,B,C）
1.14.2 去除空格或者字符串函数
Trim('A') –直接去除A字段的空格
Trim（leading /trailing ’x’ from ‘xdylanx’）from dual –去除最左边/最右边的‘x’—dylanx/xdylan
Ltrim(‘xdylanx’,’x’)—去除最左边的’x’-- ‘dylanx’
rtrim(‘xdylanx’,’x’)—去除最右边的’x’-- ‘xdylan’
Trim（both ’x’ from ‘xdylanx’）from dual –去除左右两边的‘x’—dylanx
1.15 造测试数据
--方法1：fm_audit_auth插入1200万条数据
INSERT INTO fm_audit_auth (AUTH_NO,
ATTEMPT_NO,
USER_ID,
AUTH_ID,
WS_ID,
AUTH_TIME,
STATUS,
AUTH_LEVEL,
USER_LEVEL,
FORM_NAME,
AUTH_SEQ_NO,
AUTH_REASON)
SELECT ROWNUM AS AUTH_NO,
0 ATTEMPT_NO,
'' USER_ID,
DBMS_RANDOM.string ('X', 10) AS AUTH_ID, --获取10位随机数字加字母
'127.0.0.1' AS WS_ID,
SYSDATE AUTH_TIME,
'SUCCESS' STATUS,
'' AUTH_LEVEL,
'' USER_LEVEL,
'' FORM_NAME,
TRUNC (DBMS_RANDOM.VALUE (1, 12000000)) AS AUTH_SEQ_NO, --获取1-10的随机整数
'TEST' || ROWNUM AS AUTH_REASON
FROM DUAL
CONNECT BY LEVEL <= 12000000;
--方法2：用存储过程插入1000万条数据
CREATE OR REPLACE PROCEDURE INSER_fm_audit_auth
AS
BEGIN
--EXECUTE IMMEDIATE 'TRUNCATE TABLE fm_audit_auth';
FOR I IN 1 .. 12000000
LOOP
INSERT INTO fm_audit_auth (AUTH_NO,
ATTEMPT_NO,
USER_ID,
AUTH_ID,
WS_ID,
AUTH_TIME,
STATUS,
AUTH_LEVEL,
USER_LEVEL,
FORM_NAME,
AUTH_SEQ_NO,
AUTH_REASON)
VALUES (I,
0,
'',
DBMS_RANDOM.string ('X', 10), --获取10位随机数字加字母
'127.0.0.1',
SYSDATE,
'SUCCESS',
'',
'',
'',
TRUNC (DBMS_RANDOM.VALUE (1, 12000000)), --获取1-10的随机整数
'TEST');
--每一万条提交一次
IF (MOD (I, 50000) = 0)
THEN
COMMIT;
END IF;
END LOOP;
END;
--调用存储过程
DECLARE
BEGIN
INSER_fm_audit_auth ();
END;
1.16 ORA报错
1.16.1 Ora-01403
方法一：
BEGIN
SELECT NVL (r.cr_int_type_call7, 'A')
INTO v_int_type
FROM sta.sym_rb_prod_default r
WHERE acct_type = '375';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_int_type :=NULL;
END;
方法二：
/* BEGIN
SELECT COUNT(1)
INTO v_int_cnt
FROM sta.sym_rb_prod_default r
WHERE acct_type = '375';
IF v_int_cnt>0 THEN
SELECT NVL (r.cr_int_type_call7, 'A')
INTO v_int_type
FROM sta.sym_rb_prod_default r
WHERE acct_type = '375';
END IF ;
END ;*/
1.17 动态执行语句：
EXECUTE IMMEDIATE
'TRUNCATE TABLE TPS.' || V_TABLE_NAME || ' DROP STORAGE ';
1.18 重启数据库
Oracle /oracle -用oracle 用户登录
export ORACLE_SID=rpeod --切换实例
lsnrctl status –查看监听状态
lsnrctl start –-启动监听
sqlplus sys as sysdba --用dba启动
startup --启动
shutdown immediate; --停止