oracle工作随笔

-- Create table
create table table_02
(
std_id varchar2(100),
std_name varchar2(100),
std_age varchar2(100),
std_class varchar2(100),
std_addr varchar2(100),
std_img clob
)
tablespace USERS
storage
(
initial 64K
minextents 1
maxextents unlimited
);

-- Create QUEUE
begin
sys.dbms_aqadm.create_queue_table(
queue_table => 'QUE_TABLE01',
queue_payload_type => 'RAW',
sort_list => 'ENQ_TIME',
compatible => '10.0.0',
primary_instance => 0,
secondary_instance => 0,
storage_clause => 'tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited )');
end;

-- Create Indexes
create index indx_std_id2 on table_02 (std_id)
tablespace USERS
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index indx_std_name2 on table_02 (std_name)
tablespace USERS
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index indx_std_class2 on table_02 (std_class)
tablespace USERS
storage
(
initial 64K
minextents 1
maxextents unlimited
);

-- Create TRIGGER
create or replace trigger insert_std_id2
after insert on table_02
for each row
declare
begin
dbms_output.put_line('新增学员成功');
end insert_std_id;

-- Create SEQUENCE
create sequence seq_std_id2
minvalue 100
maxvalue 9999999999999
start with 1000
increment by 1;

-- Create VIEW
create or replace view vm_table_02 as
select *
from table_02
where std_img is not null

-- Create FUNCTION
create or replace function Fnc_std_name2
return varchar2
is
Result varchar2(100);
begin
NULL;
return(Result);
end Fnc_std_name;

-- Create PROCEDURE
create or replace procedure putNum2(P_Date in date, P_year out varchar2) is
v_num number(8) := 1;
v_days number;
v_date date;
begin
dbms_output.put_line('intput value:' || P_year);
v_num := 1;
v_days := 1;
v_days := to_number(to_char(P_Date, 'dd'));
for i in 1 .. v_days Loop
v_date := to_date('2011/11/' || to_char(i), 'yyyy/MM/dd');
end loop;
P_year := '2012';
end;

-- Create PACKAGE
create or replace package sdept_or_grade2 as
procedure print_sdept(psno char);
procedure print_grade(psno char);
end;

-- Create PACKAGE BODY
create or replace package body sdept_or_grade2 as
procedure print_sdept(psno char) as
psdept table_01.std_name%type;
begin
dbms_output.put_line('psdept');
end;

procedure print_grade(psno char) as
psdept table_01.std_name%type;
begin
dbms_output.put_line('psdept');
end;
end;

-- Create TYPE
create or replace type typ_calendar2 as object(
年 varchar2(8),
月 varchar2(8),
星期日 varchar2(8),
星期一 varchar2(8),
星期二 varchar2(8),
星期三 varchar2(8),
星期四 varchar2(8),
星期五 varchar2(8),
星期六 varchar2(8),
本月最后一日 varchar2(2)
);

-- Create SYNONYM
create or replace synonym syn_std_name2
for SOE.TABLE_01@DBLINK137;

-- Create database link
create database link DBLINK1132
connect to soe identified by soe
using '10.0.20.113:1521/orcl';

--sql
select count(*)
from v$sqlarea
where parsing_schema_name = 'SOE'
and module is not null
and last_active_time between to_date('2020-07-21 15:10:00','YYYY-MM-DD hh24:mi:ss') and to_date('2020-08-21 16:10:00','YYYY-MM-DD hh24:mi:ss')

--TPS
select sum(value) from v$sysstat where name in ('user commits','user rollbacks')

--QPS
select value from v$sysstat where name = 'execute count';

--保存数据库tps、qps、会话数、进程数
create table sech_tps_ops
(
s_time date,
s_tps varchar2(200),
s_qps varchar2(200),
v_session varchar2(200),
v_process varchar2(200)
)
tablespace USERS
storage
(
initial 64K
minextents 1
maxextents unlimited
);

--循环获取数据库tps、qps、会话数、进程数
begin
for i in 1..18000 loop --18000为循环次数
insert into sech_tps_ops
select SYSDATE as s_time,
(select sum(value)
from v$sysstat
where name in ('user commits', 'user rollbacks')) as s_tps,
(select value as s_qps
from v$sysstat
where name = 'execute count') as s_qps,
(select count(1) from v$session where status != 'INACTIVE') as v_session,
(select count(1) from v$process) as v_process
from dual;
commit;
DBMS_LOCK.SLEEP(10); --没查询一次数值保存至表以后休眠10秒
end loop;
end;

--统计
select *
from sech_tps_ops
where s_time between
to_date('2020/8/31 11:23:00', 'yyyy/mm/dd hh24:mi:ss') and --采集开始时间
to_date('2020/8/31 13:40:00', 'yyyy/mm/dd hh24:mi:ss') --采集结束时间

--统计出tps、qps、会话数、进程数数值
select 'tps' as aa, to_char(max(lag_tps) / 10) as bb
from (SELECT rownum rn,
s_time,
s_tps,
s_tps - LAG(s_tps, 1, 0) OVER(ORDER BY s_time) lag_tps
FROM sech_tps_ops where s_time between
to_date('2020/8/31 11:23:00', 'yyyy/mm/dd hh24:mi:ss') and
to_date('2020/8/31 13:40:00', 'yyyy/mm/dd hh24:mi:ss')
order by s_time)
where rn <> 1 --这个数值需要在统计前排除下第一行值（LAG(s_tps, 1, 0)）
union all
select 'qps' as aa, to_char(max(lag_qps) / 10) as bb
from (SELECT rownum rn,
s_time,
s_qps,
s_qps - LAG(s_qps, 1, 0) OVER(ORDER BY s_time) lag_qps
FROM sech_tps_ops where s_time between
to_date('2020/8/31 11:23:00', 'yyyy/mm/dd hh24:mi:ss') and
to_date('2020/8/31 13:40:00', 'yyyy/mm/dd hh24:mi:ss')
order by s_time)
where rn <> 1 --这个数值需要在统计前排除下第一行值（LAG(s_qps, 1, 0)）
union all
select 'v_session' as aa, max(v_session) as bb from sech_tps_ops where s_time between
to_date('2020/8/31 11:23:00', 'yyyy/mm/dd hh24:mi:ss') and
to_date('2020/8/31 13:40:00', 'yyyy/mm/dd hh24:mi:ss')
union all
select 'v_process' as aa, max(v_process) as bb from sech_tps_ops where s_time between
to_date('2020/8/31 11:23:00', 'yyyy/mm/dd hh24:mi:ss') and
to_date('2020/8/31 13:40:00', 'yyyy/mm/dd hh24:mi:ss')

--主要业务对象
select OBJECT_TYPE,count(1) as sum_mub from dba_objects where owner = 'SOE' group by OBJECT_TYPE;

--查询表TABLE_01所关联的INDEX
select table_name,
index_name,
to_char(wm_concat(column_name)) as column_list
from dba_ind_columns
where index_owner = 'SOE'
and table_name = 'TABLE_01'
group by table_name, index_name

--查询表TABLE_01所关联的TRIGGER
select table_name,
trigger_name,
to_char(wm_concat(column_name)) as column_list
from dba_trigger_cols
where trigger_owner = 'SOE'
and table_name = 'TABLE_01'
group by table_name, trigger_name;

--查询表TABLE_01所关联的LOB
select table_name, column_name, segment_name, tablespace_name
from dba_lobs
where owner = 'SOE' and table_name = 'TABLE_01'

--查询表TABLE_01所关联的VIEW
select owner, name as view_name, referenced_name, referenced_type
from dba_dependencies
where TYPE = 'VIEW'
and owner = 'SOE'
and referenced_name = 'TABLE_01';

--查看对象的DDL语句
SELECT object_type,
object_name,
DBMS_METADATA.GET_DDL(object_type, object_name, owner) as obj_ddl
FROM (select object_type, object_name, owner
from dba_objects
where object_name = 'INDX_STD_ID2' --此处以INDX_STD_ID2索引为例
and object_type not in
('LOB', 'QUEUE', 'PACKAGE BODY', 'DATABASE LINK')
order by object_type);