这篇文章上次修改于 1249 天前，可能其部分内容已经发生变化，如有疑问可询问作者。

SELECT * FROM SYSTABLECOMMENTS;
SELECT COUNT(*) FROM DBA_TABLES;
SELECT * FROM USER_TABLES;
SELECT * FROM DBA_TABLESPACES;
SELECT * FROM USER_TABLESPACES;
SELECT * FROM ALL_OBJECTS;
SELECT * FROM DBA_OBJECTS;
SELECT * FROM USER_OBJECTS;
SELECT * FROM DBA_USERS;

SELECT * FROM DBA_TABLESPACES;
SELECT * FROM DBA_USERS;
SELECT * FROM dba_segments;

SELECT * FROM V$LICENSE;

SELECT * FROM V$DATABASE;
SELECT * FROM V$INSTANCE;
SELECT * FROM V$TABLESPACE;
SELECT * FROM V$VERSION;

SELECT SUM(bytes) from dba_data_files;

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;


--1）SYSTEM表空间--> 系统表空间有很多的数据字典等
--2）ROLL表空间-->回滚表空间 MVCC（事务多版本机制），消除行锁，回滚段，保    留时长（undo_retention 支持毫秒）
--3）TEMP 临时表空间 ，临时段，创建的临时表
--4）MAIN表空间，这是数据库默认的白哦空间，在创建表和数据对象的死后，不指定    存储位置，默认存放在该表空间
--5）HMAIN   存放huge表的表空间

--* SYSTEM-系统表空间，存放数据字典信息
--* ROLL-回滚表空间，存放的回滚数据，MVCC（事务多版本）
--* TEMP-临时表空间，存放临时数据
--* MAIN-main表空间，如果用户创建数据对象不指定存储位置，默认存放到main
--* HMAIN-hmain表空间，存放huge table信息

--SYS //内置用户，不能登陆
--SYSDBA//管理员
--SYSAUDITOR//审计用户
--SYSSSO //安全用户，安全版
--备注：创建用户时，有些达梦数据库版本 用户跟密码默认都是记录成大写的英文字母，因此登陆时要输入大写才能正常登陆；

--DMSQL
--SQL分类：（结构话查询语言）
--DDL 定义 create drop truncate
--DML 管理 select insert update delete
--DCL 控制 grant revoke
--TCL 事务管理 commit rollback save point


select NAME, ID, PID, CRTDATE from SYS.SYSOBJECTS;



select file_name,tablespace_name from dba_data_files;

Select tablespace_name,status from dba_tablespaces;

select path from v$datafile;

select * from v$datafile;

SELECT SF_GET_CASE_SENSITIVE_FLAG();

CALL sp_close_session(sess_id);

CALL SP_SET_PARA_VALUE(1,'PWD_POLICY',0);
SELECT SF_GET_PARA_VALUE(1,'PWD_POLICY');

SELECT * FROM V$PARAMETER WHERE NAME='PWD_POLICY';

CREATE USER HWUSER IDENTIFIED BY HWUSER;



SP_SET_PARA_VALUE (2,'PARALLEL_THRD_NUM',8);

select SF_GET_PARA_VALUE(2,'PARALLEL_THRD_NUM');

select para_name,para_value from v$dm_ini where para_name='PARALLEL_POLICY';
SP_SET_PARA_VALUE (2,'PARALLEL_POLICY',1);
select SF_GET_PARA_VALUE(1,'PARALLEL_POLICY');
SP_SET_PARA_VALUE (1,'MAX_PARALLEL_DEGREE',4);



select user;


CREATE TABLESPACE HWUSER_DAT DATAFILE 'HWUSER_DAT_01.dbf' SIZE 10240;
ALTER TABLESPACE HWUSER_DAT ADD DATAFILE 'HWUSER_DAT_02.dbf' SIZE 10240;
ALTER TABLESPACE HWUSER_DAT ADD DATAFILE 'HWUSER_DAT_03.dbf' SIZE 10240;
ALTER TABLESPACE HWUSER_DAT ADD DATAFILE 'HWUSER_DAT_04.dbf' SIZE 10240;
ALTER TABLESPACE HWUSER_DAT ADD DATAFILE 'HWUSER_DAT_05.dbf' SIZE 10240;

CREATE TABLESPACE HWUSER_IDX DATAFILE 'HWUSER_IDX_01.dbf' SIZE 2048;
ALTER TABLESPACE HWUSER_IDX ADD DATAFILE 'HWUSER_IDX_02.dbf' SIZE 2048;
ALTER TABLESPACE HWUSER_IDX ADD DATAFILE 'HWUSER_IDX_03.dbf' SIZE 2048;
ALTER TABLESPACE HWUSER_IDX ADD DATAFILE 'HWUSER_IDX_04.dbf' SIZE 2048;
ALTER TABLESPACE HWUSER_IDX ADD DATAFILE 'HWUSER_IDX_05.dbf' SIZE 2048;



CREATE TABLESPACE HWUSER_DAT DATAFILE 'HWUSER_DAT_01.dbf' SIZE 10240;

CREATE TABLESPACE HWUSER_IDX DATAFILE 'HWUSER_IDX_01.dbf' SIZE 2048;



CREATE TABLESPACE HWMSAS1000 DATAFILE 'HWMSAS1000_01.dbf' SIZE 10240;
ALTER TABLESPACE HWMSAS1000 ADD DATAFILE 'HWMSAS1000_02.dbf' SIZE 10240;
ALTER TABLESPACE HWMSAS1000 ADD DATAFILE 'HWMSAS1000_03.dbf' SIZE 10240;
ALTER TABLESPACE HWMSAS1000 ADD DATAFILE 'HWMSAS1000_04.dbf' SIZE 10240;
ALTER TABLESPACE HWMSAS1000 ADD DATAFILE 'HWMSAS1000_05.dbf' SIZE 10240;

CREATE TABLESPACE HWMSAS1000_IDX DATAFILE 'HWMSAS1000_IDX_01.dbf' SIZE 2048;
ALTER TABLESPACE HWMSAS1000_IDX ADD DATAFILE 'HWMSAS1000_IDX_02.dbf' SIZE 2048;
ALTER TABLESPACE HWMSAS1000_IDX ADD DATAFILE 'HWMSAS1000_IDX_03.dbf' SIZE 2048;
ALTER TABLESPACE HWMSAS1000_IDX ADD DATAFILE 'HWMSAS1000_IDX_04.dbf' SIZE 2048;
ALTER TABLESPACE HWMSAS1000_IDX ADD DATAFILE 'HWMSAS1000_IDX_05.dbf' SIZE 2048;

--ALTER USER HWUSER IDENTIFIED BY HWUSER DEFAULT TABLESPACE HWMSAS1000;

CREATE USER "HWUSER" IDENTIFIED BY "HWUSER" DEFAULT TABLESPACE "HWMSAS1000" DEFAULT INDEX TABLESPACE "HWMSAS1000_IDX";
GRANT RESOURCE TO HWUSER;



--GRANT CREATE TABLE TO HWUSER;
--REVOKE CREATE TABLE FROM HWUSER;
--GRANT SELECT ON DMHR.EMPLOYEE TO HWUSER;
--GRANT DBA TO HWUSER;
GRANT RESOURCE TO HWUSER;
GRANT PUBLIC TO HWUSER;

REVOKE DBA FROM HWUSER;


DROP USER HWUSER;
DROP USER HWUSER CASCADE;
DROP TABLESPACE HWUSER;
DROP TABLESPACE HWUSER_IDX;
DROP TABLESPACE HWMSAS1000;
DROP TABLESPACE HWMSAS1000_IDX;




create tablespace bookshop datafile 'bookshop.dbf' size 1024;

create schema persons authorization HWUSER;

create table persons.address(
addressid int identity(1,1) primary key,
address1 varchar(60) not null,
address2 varchar(60),
city varchar(30) not null,
postalcode varchar(15) not null);






SELECT * FROM USER_TABLES;

SELECT TABLE_NAME FROM USER_TABLES;

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














DROP TABLE IF EXISTS "BaseDataTab";
CREATE TABLE "BaseDataTab" (
    ID BIGINT IDENTITY(1,1) PRIMARY KEY, 
    "IpAddress" CHAR(16) NOT NULL,
    "Port" INT NOT NULL,
    "DestinationIpAddress" CHAR(16) DEFAULT NULL,
    "DestinationPort" INT DEFAULT NULL,
    "ReportTime" TIMESTAMP(3) NOT NULL,
    "StorageTime" datetime(3) NOT NULL,
    "TextType" INT NOT NULL,
    "DataType" INT NOT NULL,
    "ShapingValue" INT NOT NULL,
    "FloatingValue" DOUBLE NOT NULL,
    "TextValue" TEXT NOT NULL
);

CREATE INDEX base_rpttime_idx ON  "BaseDataTab" ("ReportTime");


SELECT * FROM "BaseDataTab";
SELECT * FROM "BaseDataTab" LIMIT 10000;
SELECT * FROM "BaseDataTab" ORDER BY ReportTime DESC LIMIT 10000;
SELECT COUNT(ID) FROM "BaseDataTab";

TRUNCATE TABLE "BaseDataTab";

INSERT INTO "BaseDataTab" VALUES ('10.100.154.197', '0', '', '0', '2020-08-21 09:26:00.290', '2020-07-17 19:32:07', '1000', '3', '0', '0', 'c8 8d 83 6f f7 b5 00 90 0b 20 1a c6 08 00 45 00 00 40 6b f4 40 00 40 06 a2 7d 14 fe 01 0c 14 01 02 3c b1 47 09 64 99 0f f3 53 71 0b fd b2 80 18 00 d1 45 bc 00 00 01 01 08 0a d8 96 45 44 6f d9 e8 a9 68 04 01 00 80 d0 68 04 01 00 84 d0\r\n');


BEGIN
FOR v_count IN 1..101 Loop

INSERT INTO "BaseDataTab" VALUES ('10.100.154.197', '0', '', '0', CAST(v_count as datetime), CAST(v_count as timestamp), '1000', '3', '0', '0', 'c8 8d 83 6f f7 b5 00 90 0b 20 1a c6 08 00 45 00 00 40 6b f4 40 00 40 06 a2 7d 14 fe 01 0c 14 01 02 3c b1 47 09 64 99 0f f3 53 71 0b fd b2 80 18 00 d1 45 bc 00 00 01 01 08 0a d8 96 45 44 6f d9 e8 a9 68 04 01 00 80 d0 68 04 01 00 84 d0\r\n');

END Loop;
COMMIT;
END;



Select * from v$sessions;
call sp_close_session(139789105959456);

Select * from v$trxwait;

select * from v$sessions where trx_id =1870061;

select sess_id,appname,clnt_ip,sql_text,sf_get_session_sql(sess_id) from v$sessions where trx_id =1870061;

select sess_id,appname,clnt_ip,sql_text,sf_get_session_sql(sess_id) from v$sessions where trx_id =1870167;