1
)當前數據庫中的模式名：
select distinct object_name TABLE_SCHEMA from all_objects where object_type = 'SCH';
2
)查出各模式對應的用戶：
select
SCH_OBJ.NAME?? ,
SCH_OBJ.ID???? ,
SCH_OBJ.CRTDATE,
USER_OBJ.NAME
from
(
select NAME, ID, PID, CRTDATE from SYS.SYSOBJECTS where TYPE$='SCH'
)
SCH_OBJ,
(
select NAME, ID from SYS.SYSOBJECTS where TYPE$='UR' and SUBTYPE$='USER'
)
USER_OBJ
where
SCH_OBJ.PID=USER_OBJ.ID
ORDER BY
SCH_OBJ.NAME
3
)查看數據庫使用內存
SELECT
name????????????????????????????? ,--
內存池描述名稱
sum(org_size)???????????????????? ,--
內存池原始大小，sum
函數：總數
sf_get_ep_seqno(rowid)??????????? ,--rowid
數據對象編號
sum(total_size)/1024.0/1024/1024 G,--
內存池目前大小
sum(reserved_size)??????????????? ,--
內存池內已經分配掉的空間
count(???????? *)????????????????????????? ,
avg(total_size)/1024.0/1024 --
內存池目前大小，avg
函數：平均值
FROM
V$MEM_POOL
group by
name,
sf_get_ep_seqno(rowid)
order by
4 desc;
4
)定時刪除作業
call SP_CREATE_JOB('countall_bakall_delbakall',1,0,'',0,0,'',0,'
每天晚上00
點收集統計信息、全庫備份、刪除30
天前的全庫備份文件');
call SP_JOB_CONFIG_START('countall_bakall_delbakall');
call SP_ADD_JOB_STEP('countall_bakall_delbakall', 'countall', 0, 'CALL SP_DB_STAT_INIT ();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('countall_bakall_delbakall', 'bakall', 6, '01000900', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('countall_bakall_delbakall', 'delbakall', 0, 'sp_db_bakallset_remove_batch(null,sysdate-30);', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('countall_bakall_delbakall', 'countall_bakall_delbakall_time01', 1, 1, 1, 0, 0, '00:01:00', NULL, '2019-02-28 10:37:43', NULL, '');
call SP_JOB_CONFIG_COMMIT('countall_bakall_delbakall');
5
)根據表查看鎖超時
select * from systexts where upper(cast(txt as varchar)) like '%MID.CUS_CREDIT%'
6
)監控表空間使用情況
select
t.name tablespace_name,
d.free_size*SF_GET_PAGE_SIZE()/1024/1024
||'M' free_space,
d.total_size*SF_GET_PAGE_SIZE()/1024/1024
||'M' total_space,
d.free_size*100/d.total_size "% FREE"
from
v$tablespace t,
v$datafile d
where
t.id=d.group_id;
select
tablespace_name?????? ,
file_name???????????? ,
bytes/1024/1024||'M'? total_space,
user_bytes/1024/1024||'M'? user_space,
user_bytes*100/bytes "% FREE"
from
dba_data_files
7
)批量編譯存儲過程
create or replace procedure compile_invalid_procedures(
p_owner varchar2 --
所有者名稱，即 SCHEMA
) as
--
編譯某個用戶下的無效存儲過程
str_sql varchar2(200);
begin
for invalid_procedures in (select object_name from all_objects
where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))
loop
str_sql := 'alter procedure '||p_owner||'.'||invalid_procedures.object_name || ' compile';
begin
execute immediate str_sql;
exception
--When Others Then Null;
when OTHERS Then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
8
)清空模式下所有表數據
declare
begin
for RS in (select * from all_tables where "OWNER" in ('MID','BAS'))LOOP DECLARE
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||RS.OWNER||'.'||RS.TABLE_NAME;
EXCEPTION WHEN OTHERS THEN
PRINT ('TRUNCATE TABLE'||RS.OWNER||'.'||RS.TABLE_NAME);
END;
end loop;
end;
9
)統計存儲過程數量
select sch.name,proc.name,proc.crtdate from sysobjects sch,sysobjects proc where proc.schid=sch.id
and sch.name IN('MID','APP') AND PROC.SUBTYPE$='PROC'; and proc.CRtDATE>SYSDATE-1;
10
)已知表被鎖：解鎖
SELECT ID as "TABLE_ID" from SYSOBJECTS WHERE NAME = 'CITY';--
根據表名查TABLE_ID
SELECT TRX_ID,LTYPE,LMODE,TABLE_ID FROM V$LOCK WHERE TABLE_ID 1199;--
根據TABLE_ID
查TRX_ID
SELECT * FROM V$SESSIONS WHERE TRX_ID = 50702145;--
根據TRX_ID
查看SESS_ID
SP_CLOSE_SESSION(140165318846264);--
根據SESS_ID
解鎖
11
)與oracle
比對表順序
SELECT * FROM DBA_TAB_COLUMNS--
查看達夢信息
SELECT * FROM ALL_TAB_COLS --
查看ORACLE
信息
CREATE TABLE TAB_1 AS SELECT * FROM DBA_TAB_COLUMNS
CREATE TABLE TAB_2 AS SELECT * FROM ALL_TAB_COLS --ORACLE
不能導出所有列
達夢端比對
SELECT
DISTINCT
A.OWNER
,A.TABLE_NAME
FROM SYSDBA.TABLE_2 A LEFT JOIN SYSDBA.TXT_1 B
ON A.OWNER|| A.TABLE_NAME = B.OWNER|| B.TABLE_NAME AND B.COLUMN_NAME = A.COLUMN_NAME AND
B.COLUMN_ID=A.COLUMN_ID
WHERE A.OWNER='XJNX' AND B.OWNER IS NULL
ORDER BY A.TABLE_NAME
12
)執行計劃緩存
CALL SP_CREATE_SYSTEM_PACKAGES(1);--
創建系統包
DBMS_STATS.GATHER_SCHEMA_STATS('SYSDBA',100,FALSE,'FOR ALL COLUMNS SIZE AUTO');
--
收集SYSDBA
模式下
所有索引的統計信息
清理執行計劃緩存 CALL SP_CLEAR_PLAN_CACHE();
