DM数据库常见操作

        ./DmServiceDMSERVER stop
        restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset         '/home/dmdba/dmdbms/dmbak/923'
        recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' with archivedir         '/home/dmdba/dmdbms/dmarch' until time '2022-09-23 20:43:00'
        recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' update db_magic

二、完成达梦数据库逻辑备份测试，验证是否支持跨模式导入，是否支持部分表导入？如何实现指定时间范围的数据通过DMP进行迁移。

一、验证是否支持跨模式导入

通过dexp导出数据
./dexp SYSDBA/SYSDBA@192.168.254.149:5236 SCHEMAS=DMHR DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak.dmp LOG=dmhs_bak.log

查看数据文件

创建模式test

将dmp文件导入模式test
./dimp SYSDBA/SYSDBA@192.168.254.149:5236 REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak.dmp LOG=dmhs_imp.log

通过指定REMAP_SCHEMA参数，格式为DMHR:TEST，将模式DMHR导入模式TEST。

二、导入部分表

指定tables参数，可以导入指定表，多个表之间’,'号隔开。
./dimp SYSDBA/SYSDBA@192.168.254.149:5236 tables=DMHR.CITY, DMHR.JOB REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak.dmp LOG=dmhs_imp.log

三、验证是否支持指定时间范围的数据通过DMP迁移

在导出时可以通过QUERY参数，指定where参数导出表的子集的select 子句。
要想指定时间范围导出，前提是该表存在时间字段。

选择EMPLOYEE测试

指定参数QUERY导出
./dexp SYSDBA/SYSDBA@192.168.254.149:5236 tables=DMHR.EMPLOYEE QUERY="where HIRE_DATE> '2010-11-27'" DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak2.dmp LOG=dmhs_bak2.log

导入数据
./dimp SYSDBA/SYSDBA@192.168.254.149:5236 tables=DMHR.EMPLOYEE REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak2.dmp LOG=dmhs_imp2.log

三、如何通过存储过程/存储函数传入模式名和要查找的字符串，判断出该模式下表中数据是否存在所要查找的字符串。

存储过程

create table info (table_owner VARCHAR(20),table_name VARCHAR(20),column_name VARCHAR(20),var_value VARCHAR(20));
create or REPLACE PROCEDURE FIND_STRING (var_table_owner VARCHAR(20),var_value VARCHAR(20))
as
DECLARE
var_sql VARCHAR2(1000);
var_cnt int;
begin
for table_detail In (select table_name,column_name from dba_tab_columns where owner=var_table_owner) loop
begin
var_sql := 'select count(*) from '||var_table_owner||'.' ||table_detail.table_name||' where trim('||table_detail.column_name||')='''||var_value||'''';
--print var_sql;
execute immediate var_sql into var_cnt;
--print var_cnt;
if(var_cnt>0)then
insert into info values(var_table_owner,table_detail.table_name,table_detail.column_name,var_value);
commit;
end if;
exception when others then
print SQLCODE || ':' ||SQLERRM;
end;
end loop;
end;
call FIND_STRING('DMHR','105');
select * from info;

dba_tab_columns视图查询

select t.table_name, t.column_name
from dba_tab_columns t
where owner='DMHR';
select EMPLOYEE_ID from DMHR.EMPLOYEE where EMPLOYEE_ID='1005';
select DEPARTMENT_ID from DMHR.DEPARTMENT where DEPARTMENT_ID='101';
select JOB_ID from DMHR.JOB where JOB_ID='72';

四、设计一个记录表，通过每周定时任务统计指定模式下的每个表的行数，已经分配的存储空间和已使用的存储空间。完成3周统计后，统计所有表每周增量。

建表

DROP TABLE IF EXISTS SYSDBA.TABLESIZE_COUNT;
CREATE TABLE TABLESIZE_COUNT (OWNER VARCHAR(100),TABLE_NAME VARCHAR(100),TABLE_USED_PAGES INTEGER,TABLE_USED_SPACE INTEGER,ROW_NUM int, TJDATE TIMESTAMP);

统计信息存储过程

DECLARE
V_OWNER VARCHAR2(100);
V_TABNAME VARCHAR2(100);
STMT1 VARCHAR2(200);
STMT2 VARCHAR2(200);
NUM_ROWS1 NUMBER;
NUM_ROWS2 NUMBER;
count_rows int;
STMT3 varchar(200);

TJDATE TIMESTAMP;
BEGIN
FOR REC IN
(
SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER='DMHR' ORDER BY 1, 2
)
LOOP

SELECT REC.OWNER, REC.TABLE_NAME INTO V_OWNER, V_TABNAME FROM DUAL;

STMT1 := 'SELECT TABLE_USED_PAGES('''||V_OWNER||''','''||V_TABNAME||''')*(PAGE/1024)';
EXECUTE IMMEDIATE STMT1 INTO NUM_ROWS1;
EXECUTE IMMEDIATE 'INSERT INTO TABLESIZE_COUNT(OWNER,TABLE_NAME,TABLE_USED_PAGES) VALUES('''||V_OWNER||''','''||V_TABNAME||''','''||TO_NUMBER(NUM_ROWS1)||''');';
STMT2 := 'SELECT TABLE_USED_SPACE('''||V_OWNER||''','''||V_TABNAME||''')*(PAGE/1024)';
EXECUTE IMMEDIATE STMT2 INTO NUM_ROWS2;
EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET TABLE_USED_SPACE= '''||TO_NUMBER(NUM_ROWS2)||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';
STMT3:='select count(*) from DMHR.'||REC.TABLE_NAME;
execute immediate STMT3 into count_rows;
EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET ROW_NUM= '''||count_rows||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';

EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET TJDATE= '''||SYSDATE||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';
EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET ID = '''||5||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';
COMMIT;
END LOOP;
END;

SELECT * FROM SYSDBA.TABLESIZE_COUNT;

创建统计每周增量建表

CREATE TABLE "SYSDBA"."T_STAT_TAB_INC"
(
"TAB_NAME" VARCHAR(30) NOT NULL,
"STAT_TIME" DATE NOT NULL,
"TOTAL_ROWS" INT,
"PRV_TOTAL_ROWS" INT,
"INCREMENT_ROWS" INT,
NOT CLUSTER PRIMARY KEY("TAB_NAME", "STAT_TIME")) STORAGE(ON "MAIN", CLUSTERBTR) ;

SELECT * FROM SYSDBA.TABLESIZE_COUNT ORDER BY TABLE_NAME ,TJDATE;

模拟数据

INSERT INTO SYSDBA.TABLESIZE_COUNT (OWNER ,TABLE_NAME,TABLE_USED_PAGES,TABLE_USED_SPACE , ROW_NUM ,TJDATE)
SELECT
OWNER
, TABLE_NAME
, TABLE_USED_PAGES + 50 as TABLE_USED_PAGES
,TABLE_USED_SPACE AS TABLE_USED_SPACE
, ROW_NUM + 10 AS ROW_NUM
,'2022-09-23 21:58:13.000000' as TJDATE FROM SYSDBA.TABLESIZE_COUNT WHERE TJDATE ='2022-09-22 21:58:13.000000';
COMMIT ;

统计行数增量

SELECT * FROM T_STAT_TAB_INC
CREATE OR REPLACE procedure P_SEL_TAB_SUM (SCH_NAME VARCHAR(100) )
AS
DECLARE
TAB_NAME VARCHAR(30);
TJDATE DATE ;
TOTAL_ROWS INT;
PRV_TOTAL_ROWS INT;
INCREMENT_ROWS INT ;
COUNT_ROWS INT ;
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
INSERT INTO T_STAT_TAB_INC (TAB_NAME , STAT_TIME ,TOTAL_ROWS ,PRV_TOTAL_ROWS ,INCREMENT_ROWS )
SELECT *,IFNULL(TOTAL_ROWS - PRV_TOTAL_ROWS ,0) as INCREMENT_ROWS
FROM(
SELECT TABLE_NAME,STAT_TIME,IFNULL(TOTAL_ROWS,0) AS TOTAL_ROWS ,IFNULL(LEAD(TOTAL_ROWS) OVER(ORDER BY TABLE_NAME,STAT_TIME DESC),0) PRV_TOTAL_ROWS
FROM (
SELECT TABLE_NAME,LEFT(TJDATE,10) STAT_TIME ,SUM(ROW_NUM) TOTAL_ROWS
FROM SYSDBA.TABLESIZE_COUNT where TABLE_NAME = A.TABLE_NAME
GROUP BY TABLE_NAME , LEFT(TJDATE,10)
order by TABLE_NAME , LEFT(TJDATE,10)
)
)
ORDER BY TABLE_NAME, STAT_TIME ;

COMMIT ;
END LOOP;
END;

CALL P_SEL_TAB_SUM('DMHR.TABLESIZE_COUNT');

SELECT * FROM SYSDBA.T_STAT_TAB_INC

建表

CREATE TABLE T_STAT_TABSPACE_INC_1 AS SELECT * FROM T_STAT_TAB_INC ;

统计存储空间增量

CREATE OR REPLACE procedure P_SEL_TABSPACE_SUM (SCH_NAME VARCHAR(100) )
AS
DECLARE
TAB_NAME VARCHAR(30);
TJDATE DATE ;
TOTAL_ROWS INT;
PRV_TOTAL_ROWS INT;
INCREMENT_ROWS INT ;
COUNT_ROWS INT ;
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
INSERT INTO T_STAT_TABSPACE_INC_1 (TAB_NAME , STAT_TIME ,TOTAL_ROWS ,PRV_TOTAL_ROWS ,INCREMENT_ROWS )
SELECT *,IFNULL(TOTAL_ROWS - PRV_TOTAL_ROWS ,0) as INCREMENT_ROWS
FROM(
SELECT TABLE_NAME,STAT_TIME,IFNULL(TOTAL_ROWS,0) AS TOTAL_ROWS ,IFNULL(LEAD(TOTAL_ROWS) OVER(ORDER BY TABLE_NAME,STAT_TIME DESC),0) PRV_TOTAL_ROWS
FROM (
SELECT TABLE_NAME,LEFT(TJDATE,10) STAT_TIME ,SUM(TABLE_USED_PAGES) TOTAL_ROWS -- ,sum(TABLE_USED_SPACE) as TOTAL_TABLE_USED_SPACE
FROM SYSDBA.TABLESIZE_COUNT where TABLE_NAME = A.TABLE_NAME
GROUP BY TABLE_NAME , LEFT(TJDATE,10)
order by TABLE_NAME , LEFT(TJDATE,10)
)
)
ORDER BY TABLE_NAME, STAT_TIME ;

COMMIT ;
END LOOP;
END;

SELECT * FROM SYSDBA.TABLESIZE_COUNT ORDER BY TABLE_NAME ,TJDATE

TRUNCATE TABLE T_STAT_TABSPACE_INC_1 ;
CALL P_SEL_TAB_SUM('DMHR.TABLESIZE_COUNT');

SELECT * FROM T_STAT_TABSPACE_INC_1 ;

关联查询两张表

SELECT A.* ,B.TOTAL_ROWS AS TOTAL_SPACE ,B. PRV_TOTAL_ROWS AS PRV_TOTAL_SPACE ,B.INCREMENT_ROWS AS INCREMENT_SPACE
FROM T_STAT_TABSPACE_INC_1 B JOIN T_STAT_TAB_INC A ON A.TAB_NAME =B.TAB_NAME AND A.STAT_TIME =B.STAT_TIME
ORDER BY A.TAB_NAME ,A.STAT_TIME

创建定时-每三周执行统计

call SP_CREATE_JOB('static_tab_inc',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('static_tab_inc');

call SP_ADD_JOB_STEP('static_tab_inc', 'static_tab_inc', 0, 'TRUNCATE TABLE T_STAT_TAB_INC;
CALL P_SEL_TAB_SUM(''SYSDBA'');', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('static_tab_inc', 'static_tab_inc', 1, 2, 3, 2, 0, '02:00:00', NULL, '2022-09-21 22:11:09', NULL, '');

call SP_JOB_CONFIG_COMMIT('static_tab_inc');

五、设计一个记录表，通过每周定时任务统计每个表空间的大小。完成3周统计后，统计每个表空间增量情况。

统计表空间大小

CREATE TABLE TABLESPACE (TABLESPACE_NAME VARCHAR(200),USED DEC, FERR DEC, TOTAL DEC ,PER_FREE VARCHAR(200)，TJDATE TIMESTAMP);
DROP TABLE SYSDBA.TABLESPACE;
SELECT * FROM SYSDBA.TABLESPACE;

DECLARE
TABLESPACE_NAME VARCHAR(200);
USED DEC;
FERR DEC;
TOTAL DEC;
PER_FREE VARCHAR(200);
TJDATE TIMESTAMP;
BEGIN
FOR REC IN
(
SELECT TJDATE FROM DBA_TABLES WHERE OWNER='SYSDBA'
)
LOOP

EXECUTE IMMEDIATE 'UPDATE TABLESPACE SET TJDATE= '''||SYSDATE||''';';

COMMIT;
END LOOP;
INSERT INTO TABLESPACE (TABLESPACE_NAME , USED ,FERR ,TOTAL ,PER_FREE )
SELECT F.TABLESPACE_NAME,
(T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",
F.FREE_SPACE "FREE (MB)",
T.TOTAL_SPACE "TOTAL(MB)",
(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '% ' PER_FRE
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS *
(SELECT PARA_VALUE
FROM V$DM_INI
WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1024)) TOTAL_SPACE
FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
END;

统计每个表空间增量建表

CREATE TABLE "SYSDBA"."T_STAT_SPACE_INC"
(
"TAB_NAME" VARCHAR(30) NOT NULL,
"STAT_TIME" DATE NOT NULL,
"TOTAL_ROWS" INT,
"PRV_TOTAL_ROWS" INT,
"INCREMENT_ROWS" INT,
NOT CLUSTER PRIMARY KEY("TAB_NAME", "STAT_TIME")) STORAGE(ON "MAIN", CLUSTERBTR) ;

SELECT * FROM TABLESPACE;

模拟数据

INSERT INTO SYSDBA.TABLESPACE (TABLE_NAME,USED,FERR, TOTAL ,PER_FREE,TJDATE)
SELECT
TABLE_NAME ,
USED + 200 as USED,
FERR AS FERR,
TOTAL AS TOTAL,
PER_FREE AS PER_FREE,
'2022-09-25 21:58:13.000000' as TJDATE FROM SYSDBA.TABLESPACE ;
COMMIT ;

统计每个表空间增量

CREATE OR REPLACE procedure P_SEL_SPACE_SUM (SCH_NAME VARCHAR(100) )
AS
DECLARE
TAB_NAME VARCHAR(30);
TJDATE DATE ;
TOTAL_ROWS INT;
PRV_TOTAL_ROWS INT;
INCREMENT_ROWS INT ;
COUNT_ROWS INT ;
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
INSERT INTO T_STAT_SPACE_INC (TAB_NAME , STAT_TIME ,TOTAL_ROWS ,PRV_TOTAL_ROWS ,INCREMENT_ROWS )
SELECT *,IFNULL(TOTAL_ROWS - PRV_TOTAL_ROWS ,0) as INCREMENT_ROWS
FROM(
SELECT TABLE_NAME,STAT_TIME,IFNULL(TOTAL_ROWS,0) AS TOTAL_ROWS ,IFNULL(LEAD(TOTAL_ROWS) OVER(ORDER BY TABLE_NAME,STAT_TIME DESC),0) PRV_TOTAL_ROWS
FROM (
SELECT TABLE_NAME,LEFT(TJDATE,10) STAT_TIME ,SUM(USED) TOTAL_ROWS
FROM SYSDBA.TABLESPACE where TABLE_NAME = TABLESPACE.TABLE_NAME
GROUP BY TABLE_NAME , LEFT(TJDATE,10)
order by TABLE_NAME , LEFT(TJDATE,10)
)
)
ORDER BY TABLE_NAME, STAT_TIME ;

COMMIT ;
END LOOP;
END;
CALL P_SEL_SPACE_SUM('SYSDBA.TABLESPACE');
SELECT * FROM SYSDBA.T_STAT_SPACE_INC ;

六、利用数据库自身能力，设计一个逻辑，实现对数据库每分钟执行的SQL数进行统计并倒序排列。

CREATE TABLE T_STAT_SQL_COUNT
(
SESS_TYPE VARCHAR(20),
SESS_COUNT INT,
SESS_TIME DATETIME(0)
);

CREATE OR REPLACE PROCEDURE P_STAT_SQL AS
BEGIN
INSERT INTO T_STAT_SQL_COUNT
   SELECT 'SQL' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(START_TIME,19) SESS_TIME
   FROM V$SQL_HISTORY
   WHERE START_TIME > (
       SELECT IFNULL(MAX(SESS_TIME),'2022-09-21 01:01:01')
       FROM T_STAT_SQL_COUNT
       WHERE SESS_TYPE = 'SQL')
   GROUP BY LEFT(START_TIME,19);
END;

调用存储过程

CALL P_STAT_SQL();

倒序排列

SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SQL_COUNT
WHERE SESS_TYPE='SQL'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;

七、利用数据库自身能力，设计一个逻辑，实现对数据库每秒瞬时并发会话数进行统计并倒序排序。

建表

CREATE TABLE T_STAT_SESS_COUNT
(
SESS_TYPE VARCHAR(20),
SESS_COUNT INT,
SESS_TIME DATETIME(0)
);

统计会话并发情况

truncate table T_STAT_SESS_COUNT;
select * from T_STAT_SESS_COUNT;

CREATE OR REPLACE PROCEDURE P_STAT_SESS AS

BEGIN
INSERT INTO T_STAT_SESS_COUNT
   SELECT 'SESS' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(CREATE_TIME,19) SESS_TIME
   FROM V$SESSION_HISTORY
   WHERE CREATE_TIME > (
       SELECT IFNULL(MAX(SESS_TIME),'2022-09-21 01:01:01')
       FROM T_STAT_SESS_COUNT
       WHERE SESS_TYPE = 'SESS')
   GROUP BY LEFT(CREATE_TIME,19);

END;

调用存储过程

call P_STAT_SESS();

倒序排列

SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE='SESS'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台达梦数据库产品体验站，DM8在线试玩，达梦数据库全系列产品免费下载，官方权威的快速上手文档和产品手册，最活跃的达梦技术社区，面向全行业ISV厂商免费的云适配服务。https://eco.dameng.com/

学习两年半

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
DM数据库常见操作

通过指定REMAP_SCHEMA参数，格式为DMHR:TEST，将模式DMHR导入模式TEST。在导出时可以通过QUERY参数，指定where参数导出表的子集的select 子句。指定tables参数，可以导入指定表，多个表之间’,'号隔开。要想指定时间范围导出，前提是该表存在时间字段。将dmp文件导入模式test。选择EMPLOYEE测试。指定参数QUERY导出。通过dexp导出数据。
复制链接

扫一扫