DM学习笔记

基础操作

-- 创建表空间
create tablespace tbs2 DATAFILE 'TBS01.DBF' size 32;
-- 修改表空间大小 
alter tablespace tbs2 RESIZE DATAFILE 'TBS01.DBF' TO 128;
-- 表空间添加数据文件
alter TABLESPACE tbs2 add DATAFILE 'TBS02.DBF' size 128 
AUTOEXTEND on NEXT 2 MAXSIZE 20480;
-- 表空间剩余大小 BLOCKS 已用大小 
select * from DBA_FREE_SPACE;

-- 创建表指定表空间
create table t_test2(id int , name varchar(20)) tablespace tbs2;
-- 初始化数据
begin
 for i in 1..50000 loop
 execute IMMEDIATE 'insert into t_test2(id, name) values(?,?)' using i,DBMS_RANDOM.string('A', 20);
 end loop;
 commit;
end;

-- 迁移数据文件
alter tablespace tbs2 offline;
alter TABLESPACE tbs2 RENAME DATAFILE 'TBS01.DBF' TO
'/dm/data/DM/TBS/TBS21.DBF';
alter TABLESPACE tbs2 RENAME DATAFILE 'TBS02.DBF' TO
'/dm/data/DM/TBS/TBS22.DBF';
alter tablespace tbs2 online;

-- 查询
select * from t_test2;


-- redo log
select * from v$rlogfile;
select * from v$rlog; --cur_file表示正在使用的联机日志

-- 查看归档状态和归档配置
select arch_mode from v$database;
select * from SYS."V$DM_ARCH_INI"; 
select * from v$arch_file;

-- 开启归档
alter database mount;
alter database ARCHIVELOG;
alter database ADD ARCHIVELOG 'type=local, dest=/dm/arch, file_size=64, space_limit=10240';
alter database open;

-- 修改归档配置上限
alter database modify archivelog 'type=local, dest=/dm/arch, SPACE_LIMIT=20480';

-- 查看归档相关函数
select * from v$ifun t where name like 'SF_ARCHIVELOG_%';
-- 删除十天前的归档
Select SF_ARCHIVELOG_DELETE_BEFORE_TIME(sysdate-10);



select t.INDEX_NAME, t.INDEX_TYPE, t.TABLE_NAME, t.TABLE_OWNER, t.status,
t.VISIBILITY from user_indexes t;

select * from dba_indexes t where t.OWNER ='DMHR' AND t.TABLE_NAME = 'JOB';



--开启索引监控
alter index DMHR.INDEX33555577 MONITORING USAGE;

SELECT * FROM DMHR.JOB WHERE DMHR.JOB.JOB_ID > 11;

--关闭索引监控
alter index DMHR.INDEX33555577 NOMONITORING USAGE;
--查看索引的监控信息
select * from v$object_usage;

-- 系统所有的列定义信息
SELECT * FROM SYSCOLUMNS;



INSERT INTO "DMHR"."JOB" ("JOB_ID","JOB_TITLE","MIN_SALARY","MAX_SALARY") VALUES ('74','秘书GG',3000,6000);

-- 查询锁
select * from v$trxwait;
select * from v$lock t where t.blocked =1;
select a.sess_id, a.sql_text, a.state, b.* from v$sessions a, v$trxwait b
where a.trx_id = b.id;
--查询被锁的表
select * from sys.sysobjects t where id= 1056;

-- 查询未提交事务
select b.object_name, c.sess_id, c.thrd_id, c.state, c.sql_text, a.*
 from v$lock a, dba_objects b, v$sessions c
where a.table_id = b.object_id 
 and a.ltype = 'OBJECT'
 and a.trx_id = c.trx_id
 and a.ign_flag=0;

-- 查询包含锁的会话信息
select * from V$SESSIONS where SESS_ID IN (select  c.sess_id
 from v$lock a, dba_objects b, v$sessions c
where a.table_id = b.object_id 
 and a.ltype = 'OBJECT'
 and a.trx_id = c.trx_id
 and a.ign_flag=0);

 select SESS_ID,SESS_SEQ,SQL_TEXT,STATE, CREATE_TIME from v$sessions where 
 SQL_TEXT not like '%SESS_ID,SESS_SEQ,SQL_TEXT,STATE, CREATE_TIME%'
 and trx_id in (select trx_id from V$LOCK) 
 and (SQL_TEXT like '%insert%' 
 or SQL_TEXT LIKE '%UPDATE%'
 OR SQL_TEXT LIKE '%DELETE%');


-- 结束某个会话 sess_id 释放事务
sp_close_session(111863072);



SELECT * FROM  DBA_TABLES WHERE  TABLE_NAME LIKE '%T_TEST1%';

备份恢复

-- 备份参数
select * from v$parameter t where name in ('BAK_PATH', 'BAK_USE_AP');



create table t3(id int, name varchar(100)) tablespace main;


insert into t3(id, name) values('111111','abs');
insert into t3(id, name) values('222222','abs');
insert into t3(id, name) values('333333','abs');


select count(*) from t3;

-- 执行备份 15
backup database;

-- 删除
-- 重新检查
SP_FILE_SYS_CHECK();
-- 重做表空间
SP_TABLESPACE_PREPARE_RECOVER('MAIN');
SP_TABLESPACE_RECOVER('MAIN');
-- 恢复
-- 数据库挂起
alter database mount;
-- 使用dmrman工具恢复
-- restore database '/dm8/data/DAMENG/dm.ini' tablespace main from backupset 'XXX';
-- 恢复数据
-- recover database '/dm8/data/DAMENG/dm.ini' tablespace main;
-- 数据库启动
alter database open;


select permanent_magic;

select db_magic from v$rlog;

-- 全量备份
backup database full to ONLINEBAK_01 backupset '/dm/bak/full/ONLINEBAK_01';
-- 增量备份
backup database increment base on backupset  '/dm/bak/full/ONLINEBAK_01' to ONLINEBACKINCR_01 backupset '/dm/bak/incr/ONLINEBACKINCR_01';
-- 查看备份集
select * from v$backupset;

select SF_BAKSET_CHECK('DISK','/dm/bak/incr/ONLINEBACKINCR_01');

-- 表空间备份
backup tablespace tbs2 full to ONLINEBAK_TABLESPACE_01 backupset '/dm/bak/table_space/full/ONLINEBAK_TABLESPACE_01';
backup tablespace tbs2 increment  to ONLINEBAKINCR_TABLESPACE_01 backupset '/dm/bak/table_space/incr/ONLINEBAKINCR_TABLESPACE_01';
select table_name from SYS.DBA_TABLES where TABLESPACE_NAME = 'TBS2';

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE', 't_test2')) FROM DUAL;

-- 表备份
backup table t_test2;

-- 归档备份
backup archivelog all;

-- 逻辑导入导出 --

-- ./dexp userid=dmdba:5236 directory=/dm/bak/dexp file=T_TEST1.dmp log=T_TEST1.log tables=DMDBA.T_TEST1
-- ./dimp userid=dmdba:5236 directory=/dm/bak/dexp file=T_TEST1.dmp log=T_TEST1.log tables=DMDBA.T_TEST1 

定时作业

-- 创建DMSBS_定时器 使用管理工具创建
call SP_INIT_DBMS_SCHEDULER_SYS(1);
-- 查看任务列表
select * from sysjob.sysjobs;

dbms_job.run(1742265875);
-- 查看任务执行记录
select * from SYSJOB.SYSJOBHISTORIES2;
checkpoint(100);