表的编辑

修改表名、表注释

--mysql
drop table T1;
alter table T1 rename to T2;
alter table T comment = 'table comment';

--pg、dm、oracle
drop table T1;
alter table T1 rename to T2;
comment on table T is 'comment';

添加、修改、删除表字段

--mysql
alter table T add column age int not null default '0' comment '注释';
alter table T add column id type comment='comment' first | after cName;
alter table T drop column id;
alter table T rename column id1 to id2;

alter table T modify column id type first | after cName;
alter table T change column old_id new_id type;

alter table T alter column id set default 10;
alter table T alter column id drop default;

alter table t modify column id type not null;
alter table t modify column id type null;

--pg
--不支持自动顺序修改
alter table T add column id integer;
alter table T drop column id cascade;
alter table T rename column id1 to id2;
comment on column T.id is 'comment';

alter table T alter column id type integer;
alter table T alter column "id" type integer using "id"::integer;

alter table T alter column id set default 0;
alter table T alter column id drop default;

alter table T alter column id set not null;
alter table T alter column id drop not null;

--dm
--不支持自动顺序修改
alter table T add column id integer;
alter table T drop column id cascade;
comment on column T.id is 'comment';

--达梦修改用户名（高版本才可以）
alter user old_user rename to new_user;

alter table T rename column id to id2;
alter table T alter column id rename to id2;

alter table T modify id integer;

alter table T alter column id set default 0;
alter table T alter column id drop default;

alter table T alter column id set not null;
alter table T alter column id set null;

--oracle
--不支持自动顺序修改
alter table T add (id integer);
alter table T add (id integer, id2 integer);
alter table T drop column id;
alter table T rename column id to id2;
comment on column T.id is 'comment';

--oracle修改字段信息
alter table T modify (id integer);
alter table T modify (id integer default 0);
alter table T modify (id integer not null);
alter table T modify (id integer null);

--oracle修改字段默认值、是否为空时可以不加字段类型
alter table T modify C default 'abc';
alter table T modify C not null;
alter table T modify C default 'abc' not null;

创建外键

--创建外键的条件：
-- 字段 -> 主键 √
-- 字段 -> 唯一索引 √
-- 字段 -> 普通字段 X
-- 字段 -> 普通索引 X

--mysql
alter table T add constraint fk_name foreign key(C) references ref_T(ref_C);
alter table T drop foreign key fk_name;

--pg、dm、oracle
alter table T add constraint fk_name foreign key(C) references ref_T(ref_C);
alter table T drop constraint fk_name;

序列创建

PG：
ALTER SEQUENCE [ IF EXISTS ] name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ RESTART [ [ WITH ] restart ] ]
    [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

ALTER SEQUENCE aa_test_seq RENAME TO aa_test_seq2;
CREATE SEQUENCE aa_test_seq start WITH 2 increment BY 2 MAXVALUE 1000;
ALTER SEQUENCE aa_test_seq2 start WITH 2 increment BY 2 MAXVALUE 1000;

oracle、dm 特性一致：
CREATE SEQUENCE aa_test_seq start WITH 2 increment BY 2 MAXVALUE 1000;
ALTER SEQUENCE aa_test_seq increment BY 1 MAXVALUE 100;
不支持修改序列名称、初始值；可修改步长、最大值

索引创建

--mysql
alter table T add unique idx_name(id);
alter table T add index  idx_name(id);
alter table T drop index idx_name;

--pg创建索引(btree、hash、fulltext)，默认创建btree索引
create unique index idx_name on T(id);
create unique index idx_name on T(id) using btree;
create unique index idx_name on T(id) using hash;
create fulltext index idx_name on T(id);
drop index idx_name;

alter table T add constraint idx_name unique(id);
alter table T drop constraint idx_name;

--dm、oracle
alter table T add constraint idx_name unique(id);
alter table T drop constraint idx_name;

create index idx_name on T(UPPER(id)); --创建函数式索引

create index idx_name on t(id2);
create unique index idx_name on t(id2);
drop index idx_name;

PG创建触发器

-- 创建触发器函数语法
CREATE FUNCTION 函数名() RETURNS trigger as $函数名$
    BEGIN
        函数执行代码;
    end;
$函数名$ LANGUAGE plpgsql;

-- 创建触发器函数示例
create or replace FUNCTION pg_fun2() RETURNS trigger as $$
    BEGIN 
        select 1;
    end;
$$ LANGUAGE plpgsql;

--删除函数
drop function if exists pg_fun2();

--创建触发器语法
CREATE trigger 触发器名 BEFORE|AFTER 触发事件【INSERT|UPDATE|DELETE】 ON 表名
FOR EACH ROW EXECUTE PROCEDURE 触发器函数名();

--创建触发器示例
create trigger trigger_2 AFTER INSERT ON test_1 
FOR EACH ROW EXECUTE PROCEDURE pg_fun2();

--删除触发器
drop trigger if exists trigger_2 on test_1;

主键创建，主键有仅仅可以存在一个

--mysql
--mysql删除主键时，必须先把自增长删除
alter table T add  primary key(id);
alter table T drop primary key;

--pg、dm、oracle
alter table T add constraint pk_name primary key(id);
alter table T drop constraint pk_name;

--oracle删除主键另外的语法
alter table T drop primary key;

查询主键索引

#mysql：
select constraint_name, constraint_type 
from information_schema.table_constraints
WHERE TABLE_NAME = 'tableName' AND TABLE_SCHEMA= 'mysql'
AND CONSTRAINT_NAME = 'indexName'

#pg：
with x as(
   select a.oid,a1.nspname schema_name,a.relname tab_name from pg_class a
   inner join pg_namespace a1 on a1.oid=a.relnamespace
),
y as(
   select t.conname,t.conrelid tab_id,t1.schema_name,t1.tab_name,unnest(t.conkey) col_id
   from pg_constraint t
   inner join x t1 on t1.oid=t.conrelid
       and t1.schema_name='public' and t1.tab_name = 'tableName'
   where t.contype='p'
)
select y.conname,y.schema_name,y.tab_name,y1.attname col_name
from y
INNER JOIN pg_attribute y1 ON y1.attrelid=y.tab_id AND y1.attnum=y.col_id
order by y.tab_name,y.col_id

#oracle：
SELECT  A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_TYPE, A.INDEX_NAME
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B
WHERE A.STATUS='ENABLED'
AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER
AND (A.CONSTRAINT_TYPE='P' OR A.CONSTRAINT_TYPE='R')
AND A.OWNER='ORACLE' AND A.TABLE_NAME = 'tableName'

#dm：
SELECT  A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_TYPE, A.INDEX_NAME
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B
WHERE A.STATUS='ENABLED'
AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER
AND (A.CONSTRAINT_TYPE='P' OR A.CONSTRAINT_TYPE='R')
AND A.OWNER='DM' AND A.TABLE_NAME = 'tableName'

表触发器查询

--mysql：
select event_object_table table_name, trigger_name trigger_name,
action_timing trigger_timing, event_manipulation trigger_event,
action_statement action_statement
from information_schema.triggers where event_object_schema='mysql' 
and event_object_table='tableName'

--pg：
select event_object_table tablename, trigger_name triggername,action_timing triggertiming,
event_manipulation triggerevent,action_statement actionstatement
from information_schema.triggers 
where event_object_schema='public'
and event_object_table='tableName'

--dm：
select table_name, trigger_name, triggering_type, triggering_event, trigger_body 
from all_triggers 
where table_owner='DM' and table_name = 'tableName'

--oracle：
select table_name, trigger_name, trigger_type, triggering_event, trigger_body
from all_triggers where table_owner='ORACLE' and table_name = 'tableName'

判断字段是否自增长

--mysql:
字段EXTRA非法包含：auto_increment

--pg：
字段COLUMN_DEFAULT是否包含：nextval(

--DM：
字段DATA_DEFAULT是否包含：.nextval

--oracle：
查询触发器，查询序列，看触发器中是否包含序列

创建表的自增长字段

--mysql：
create table table_name (
 id Integer not null auto_increment comment '自增主键',
 name varchar(32) comment '名称',
 primary key(id),
 key idx_table_name_idx1(name)
) comment='表';

--pg：
create sequence t_seq_id;
create table T(
id int not null default nextval('t_seq_id'::regclass),
primary key (id)
);

--dm：
create sequence t_seq_id;
create table t(
id INT default t_seq_id.nextval not null,
constraint t_pkey primary key (id)
);

--oracle：
create table tb_10(id integer);
create SEQUENCE tb_10_seq;
create or replace TRIGGER tb_10_trigger before insert ON tb_10 for each row 
BEGIN SELECT tb_10_seq.nextval INTO :new.id from dual end;

1、mysql

--表数量
SELECT COUNT(*)
FROM information_schema.TABLES
WHERE table_schema='rbc_test' AND TABLE_TYPE='BASE TABLE';

--表名称、注释
SELECT TABLE_NAME, data_length AS USED_SPACE, TABLE_ROWS, TABLE_COMMENT
FROM information_schema.TABLES
WHERE table_schema='rbc_test' AND TABLE_TYPE='BASE TABLE' 
order by table_name LIMIT 0, 10;

--表触发器
select EVENT_OBJECT_TABLE tableName, TRIGGER_NAME triggerName,
ACTION_TIMING triggerTiming,
EVENT_MANIPULATION triggerEvent,ACTION_STATEMENT actionStatement
from information_schema.triggers where EVENT_OBJECT_SCHEMA='rbc_test' 
and EVENT_OBJECT_TABLE IN ('table_name');

--分区信息
SELECT TABLE_NAME as tableName, PARTITION_NAME as partitionName, TABLE_ROWS as recordCount,
create_time as createTime, truncate(data_length/1024, 0) as memorySpace
FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_schema='rbc_test' and TABLE_NAME IN('table_name');

--表索引信息
SELECT CASE WHEN SEQ_IN_INDEX = 1 THEN TABLE_NAME ELSE '' END AS tableName,
CASE WHEN SEQ_IN_INDEX = 1 THEN INDEX_NAME ELSE '' END AS indexName,
COLUMN_NAME AS columnName,
CASE WHEN SEQ_IN_INDEX = 1 THEN INDEX_TYPE ELSE '' END AS dataStructure,
CASE WHEN SEQ_IN_INDEX = 1 THEN INDEX_COMMENT ELSE '' END AS comment,
CASE WHEN SEQ_IN_INDEX = 1 THEN (CASE WHEN NON_UNIQUE=1 THEN '' ELSE 'unique' END) ELSE '' END AS indexType
from INFORMATION_SCHEMA.STATISTICS a where Table_schema='rbc_test' and TABLE_NAME IN('test_1')
order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

--表字段信息
SELECT `TABLE_NAME`,`COLUMN_NAME`,`DATA_TYPE`,`COLUMN_TYPE`,
`CHARACTER_MAXIMUM_LENGTH` AS DATA_LENGTH,`IS_NULLABLE` AS NULL_ABLE,
CASE
WHEN `NUMERIC_PRECISION` IS NOT NULL THEN `NUMERIC_PRECISION`
WHEN `DATETIME_PRECISION` IS NOT NULL THEN `DATETIME_PRECISION`
ELSE ''
END AS DATA_PRECISION,
`NUMERIC_SCALE` AS DATA_SCALE,`COLUMN_COMMENT`,`EXTRA`
FROM `INFORMATION_SCHEMA`.`COLUMNS`  
WHERE `TABLE_SCHEMA`='rbc_test'  AND `TABLE_NAME` IN ('table_name') 
ORDER BY `TABLE_NAME`,`ORDINAL_POSITION`;

--表外键信息
SELECT `CONSTRAINT_NAME`,`TABLE_SCHEMA`,`TABLE_NAME`,`COLUMN_NAME`,
`REFERENCED_TABLE_SCHEMA` ,`REFERENCED_TABLE_NAME` ,`REFERENCED_COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE `POSITION_IN_UNIQUE_CONSTRAINT` IS NOT NULL
AND `TABLE_SCHEMA`='rbc_test' AND `TABLE_NAME` IN ('test_1')
ORDER BY `TABLE_NAME`,`CONSTRAINT_NAME`;

--表主键信息
SELECT `TABLE_SCHEMA`,`TABLE_NAME`,`COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_NAME`='PRIMARY'
AND `TABLE_SCHEMA`='rbc_test' AND `TABLE_NAME` IN('test_1');

2、pg

--表数量
SELECT COUNT(*) FROM pg_class AS a, pg_namespace AS b
WHERE a.relnamespace=b.oid AND b.nspname='public' AND a.relkind='r';

--表名称、注释
SELECT a.relname AS TABLE_NAME, a.reltuples AS ROW_NUM,
c.description AS COMMENT,
pg_total_relation_size('"' || b.nspname||'"."'||a.relname||'"') AS USED_SPACE
FROM pg_class AS a JOIN pg_namespace AS b ON a.relnamespace=b.OID
LEFT JOIN pg_description c on c.objoid=a.oid and c.objsubid=0
WHERE b.nspname='public' and a.relkind='r' order by a.relname LIMIT 10 OFFSET 0;

--表触发器
SELECT event_object_table TABLE_NAME, TRIGGER_NAME TRIGGER_NAME,
action_timing TRIGGER_TIMING, event_manipulation TRIGGER_EVENT,
action_statement ACTION_STATEMENT
FROM information_schema.triggers WHERE event_object_schema='public' 
AND event_object_table IN ('table_name')；

--表索引
SELECT tablename, indexname, indexdef
FROM pg_indexes WHERE schemaname='public' AND tablename IN ('test_1')；

--表字段信息
SELECT a.relname AS TABLE_NAME, b.attname AS COLUMN_NAME,
format_type(b.atttypid,b.atttypmod) as DATA_TYPE,
obj_description(regclass(a.relname)) AS COMMENT
, e.character_maximum_length AS DATA_LENGTH
, e.numeric_precision AS DATA_PRECISION
, e.numeric_scale AS DATA_SCALE
, e.column_default AS COLUMN_DEFAULT
, b.attnotnull AS NULL_ABLE
FROM pg_class AS a
join pg_attribute AS b ON b.attrelid = a.oid AND b.attnum>0
join pg_type c on c.oid = b.atttypid
JOIN pg_namespace d ON a.relnamespace=d.OID
left join information_schema.columns e on e.table_catalog='rbc_test_backup' and e.table_schema=d.nspname and e.table_name=a.relname and e.column_name=b.attname
where d.nspname='public' and a.relkind='r'
AND a.relname IN ('test_1') order by b.attnum asc;

--表外键信息
with x as(
   select a.oid,a1.nspname schema_name,a.relname tab_name from pg_class a
   inner join pg_namespace a1 on a1.oid=a.relnamespace
),
y as(
   select t.conname,t.conrelid tab_id,t1.schema_name,t1.tab_name,unnest(t.conkey) col_id,
   t.confrelid tab_id_f,t2.schema_name schema_name_f,t2.tab_name tab_name_f,unnest(t.confkey) col_id_f
   from pg_constraint t
   inner join x t1 on t1.oid=t.conrelid
       and t1.schema_name='public' and t1.tab_name IN ('test_1')
   inner join x t2 on t2.oid=t.confrelid
   where t.contype='f'
)
select y.conname,y.schema_name,y.tab_name,y1.attname col_name,
y.schema_name_f,y.tab_name_f,y2.attname col_name_f
from y
INNER JOIN pg_attribute y1 ON y1.attrelid=y.tab_id AND y1.attnum=y.col_id
INNER JOIN pg_attribute y2 ON y2.attrelid=y.tab_id_f AND y2.attnum=y.col_id_f;

--表主键信息
with x as(
   select a.oid,a1.nspname schema_name,a.relname tab_name from pg_class a
   inner join pg_namespace a1 on a1.oid=a.relnamespace
),
y as(
   select t.conname,t.conrelid tab_id,t1.schema_name,t1.tab_name,unnest(t.conkey) col_id
   from pg_constraint t
   inner join x t1 on t1.oid=t.conrelid
       and t1.schema_name='public' and t1.tab_name IN('test_1')
   where t.contype='p'
)
select y.conname,y.schema_name,y.tab_name,y1.attname col_name
from y
INNER JOIN pg_attribute y1 ON y1.attrelid=y.tab_id AND y1.attnum=y.col_id
order by y.tab_name,y.col_id;

#序列查询
select sequence_name, start_value, increment, maximum_value
from information_schema.sequences
where sequence_schema = 'pg';

3、oracle

--表数量
SELECT COUNT(*) TOTAL_NUM FROM ALL_TABLES WHERE OWNER='LDS';

--表名称、注释
SELECT b.TABLESPACE_NAME,b.TABLE_NAME,c.COMMENTS,
CASE WHEN (NUM_ROWS IS NOT NULL AND AVG_ROW_LEN IS NOT NULL) THEN NUM_ROWS * AVG_ROW_LEN ELSE 0
END AS USED_SPACE,
CASE WHEN NUM_ROWS IS NOT NULL THEN NUM_ROWS ELSE 0 END AS ROW_NUM
FROM
(SELECT a.*, ROWNUM AS rowno
 FROM (SELECT *FROM all_tables WHERE OWNER='LDS' ORDER BY TABLE_NAME ASC) a WHERE ROWNUM <= 10
) b
LEFT JOIN all_tab_comments c ON b.TABLE_NAME=c.TABLE_NAME AND c.OWNER='LDS'
WHERE b.rowno > 0;

--表触发器信息
SELECT TABLE_NAME, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TRIGGER_BODY
FROM ALL_TRIGGERS WHERE TABLE_OWNER='LDS'
AND TABLE_NAME IN('table_name');

--表分区信息
SELECT A.TABLE_NAME, A.PARTITION_NAME, B.CREATED,
CASE WHEN A.NUM_ROWS IS NOT NULL THEN A.NUM_ROWS ELSE 0 END AS RECORD_COUNT,
CASE WHEN (A.NUM_ROWS IS NOT NULL AND A.AVG_ROW_LEN IS NOT NULL) THEN A.NUM_ROWS * A.AVG_ROW_LEN ELSE 0 END AS USED_SPACE
FROM ALL_TAB_PARTITIONS A JOIN ALL_OBJECTS B ON A.TABLE_NAME=B.OBJECT_NAME AND A.PARTITION_NAME=B.SUBOBJECT_NAME
WHERE TABLE_OWNER='LDS' AND A.TABLE_NAME IN('table_name');

--表索引信息
SELECT A.TABLESPACE_NAME, A.TABLE_NAME, A.INDEX_NAME, B.COLUMN_NAME, A.INDEX_TYPE, A.UNIQUENESS, B.COLUMN_POSITION
 FROM ALL_INDEXES A, ALL_IND_COLUMNS B WHERE A.INDEX_NAME=B.INDEX_NAME
 AND A.TABLE_OWNER=B.TABLE_OWNER AND A.OWNER=B.INDEX_OWNER AND A.TABLE_OWNER='LDS' AND A.OWNER='LDS' 
 AND A.TABLE_NAME IN('table_name')
 ORDER BY A.TABLE_NAME, A.INDEX_NAME, B.COLUMN_POSITION;

--表字段信息
SELECT A.TABLE_NAME,C.COLUMN_NAME,C.DATA_TYPE,C.DATA_LENGTH,
C.DATA_PRECISION,C.DATA_SCALE,C.NULLABLE,D.COMMENTS
FROM ALL_TABLES A, ALL_TAB_COLUMNS C, ALL_COL_COMMENTS D
WHERE A.OWNER='LDS' AND A.OWNER = C.OWNER AND A.TABLE_NAME = C.TABLE_NAME
AND C.OWNER=D.OWNER AND C.TABLE_NAME=D.TABLE_NAME AND C.COLUMN_NAME=D.COLUMN_NAME
AND A.TABLE_NAME IN('tab_name') 
ORDER BY C.COLUMN_ID ASC;

--表外键信息
with t1 as(
SELECT x1.OWNER,x1.TABLE_NAME,x1.COLUMN_NAME,x1.CONSTRAINT_NAME,x1.POSITION
FROM ALL_CONSTRAINTS x
INNER JOIN ALL_CONS_COLUMNS x1 ON x1.CONSTRAINT_NAME =x.CONSTRAINT_NAME OR x1.CONSTRAINT_NAME=x.R_CONSTRAINT_NAME
WHERE x.CONSTRAINT_TYPE='R' AND x.OWNER='LDS'
AND x.TABLE_NAME IN('model_908ab9')
ORDER BY x1.POSITION
)
select y1.OWNER,y1.TABLE_NAME,y1.COLUMN_NAME,y1.CONSTRAINT_NAME,
y2.OWNER OWNER_F,y2.TABLE_NAME TABLE_NAME_F,y2.COLUMN_NAME COLUMN_NAME_F,y2.CONSTRAINT_NAME CONSTRAINT_NAME_F
from ALL_CONSTRAINTS y
inner join t1 y1 ON y1.CONSTRAINT_NAME =y.CONSTRAINT_NAME AND y1.OWNER=y.OWNER
inner join t1 y2 ON y2.CONSTRAINT_NAME =y.R_CONSTRAINT_NAME AND y2.OWNER=y.OWNER;

--表主键信息
SELECT t.CONSTRAINT_NAME,t1.OWNER,t1.TABLE_NAME,t1.COLUMN_NAME
FROM ALL_CONSTRAINTS t
INNER JOIN ALL_CONS_COLUMNS t1 ON t1.CONSTRAINT_NAME =t.CONSTRAINT_NAME 
   AND t1.OWNER=t.OWNER AND t1.TABLE_NAME=t.TABLE_NAME
WHERE t.CONSTRAINT_TYPE='P' AND t.OWNER='LDS'
AND t.TABLE_NAME IN('test_1')
ORDER BY t1.TABLE_NAME,t1.POSITION;

--序列查询
select sequence_name, min_value, max_value, increment_by
from all_sequences
where sequence_owner = 'ORACLE';

4、DM

--查询用户列表
select * from dba_users where username like '%RBC%';

--表数量
SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER='DATA-MASTER-DEV' and TEMPORARY='N';

--表名称、注释
SELECT b.TABLESPACE_NAME,b.TABLE_NAME,c.COMMENTS,
TABLE_USED_SPACE(b.OWNER,b.TABLE_NAME)*to_number(page()) AS USED_SPACE,
CASE WHEN NUM_ROWS IS NOT NULL THEN NUM_ROWS ELSE 0 END AS ROW_NUM
FROM
(SELECT a.*, ROWNUM AS rowno
 FROM
 (SELECT *FROM all_tables WHERE OWNER='DATA-MASTER-DEV' and TEMPORARY='N' ORDER BY TABLE_NAME ASC) a
 WHERE ROWNUM <= 10
) b
LEFT JOIN all_tab_comments c ON b.TABLE_NAME=c.TABLE_NAME and c.OWNER=b.OWNER
WHERE b.rowno > 0;

--表触发器
SELECT TABLE_NAME, TRIGGER_NAME, TRIGGERING_TYPE, TRIGGERING_EVENT, TRIGGER_BODY 
FROM ALL_TRIGGERS 
WHERE TABLE_OWNER='DATA-MASTER-DEV' AND TABLE_NAME in('test_1');

--表分区
SELECT A.TABLE_NAME, A.PARTITION_NAME, B.CREATED,
CASE WHEN A.NUM_ROWS IS NOT NULL THEN A.NUM_ROWS ELSE 0 END AS RECORD_COUNT,
CASE WHEN (A.NUM_ROWS IS NOT NULL AND A.AVG_ROW_LEN IS NOT NULL) THEN A.NUM_ROWS * A.AVG_ROW_LEN ELSE 0 END AS USED_SPACE
FROM ALL_TAB_PARTITIONS A JOIN ALL_OBJECTS B ON A.TABLE_NAME=B.OBJECT_NAME AND A.TABLE_OWNER=B.OWNER
WHERE A.TABLE_OWNER='DATA-MASTER-DEV' AND A.TABLE_NAME in('test_1');

--表索引信息
SELECT A.TABLESPACE_NAME, A.TABLE_NAME, A.INDEX_NAME, B.COLUMN_NAME, 
A.INDEX_TYPE, A.UNIQUENESS, B.COLUMN_POSITION
FROM ALL_INDEXES A, ALL_IND_COLUMNS B WHERE A.INDEX_NAME=B.INDEX_NAME
AND A.TABLE_OWNER=B.TABLE_OWNER AND A.OWNER=B.INDEX_OWNER 
AND A.TABLE_OWNER='DATA-MASTER-DEV' AND A.OWNER='DATA-MASTER-DEV'
AND A.TABLE_NAME in('mdm_entity')
ORDER BY A.TABLE_NAME, A.INDEX_NAME, B.COLUMN_POSITION;

--表字段信息
SELECT A.TABLE_NAME, C.COLUMN_NAME, C.DATA_DEFAULT, C.DATA_TYPE, C.DATA_LENGTH, 
C.DATA_PRECISION, C.DATA_SCALE, C.NULLABLE, D.COMMENTS
FROM ALL_TABLES A
JOIN ALL_TAB_COLUMNS C ON A.OWNER = C.OWNER AND A.TABLE_NAME = C.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS D ON C.OWNER=D.SCHEMA_NAME AND C.TABLE_NAME=D.TABLE_NAME AND C.COLUMN_NAME=D.COLUMN_NAME
WHERE A.OWNER='DATA-MASTER-DEV' AND A.TABLE_NAME in('test_1')
ORDER BY C.COLUMN_ID ASC;

--表外键信息
with t1 as(
SELECT x1.OWNER,x1.TABLE_NAME,x1.COLUMN_NAME,x1.CONSTRAINT_NAME,x1.POSITION
FROM ALL_CONSTRAINTS x
INNER JOIN ALL_CONS_COLUMNS x1 ON x1.CONSTRAINT_NAME =x.CONSTRAINT_NAME OR x1.CONSTRAINT_NAME=x.R_CONSTRAINT_NAME
WHERE x.CONSTRAINT_TYPE='R' AND x.OWNER='DATA-MASTER-DEV'
AND x.TABLE_NAME IN('test_1')
ORDER BY x1.POSITION
)
select y1.OWNER,y1.TABLE_NAME,y1.COLUMN_NAME,y1.CONSTRAINT_NAME,
y2.OWNER OWNER_F,y2.TABLE_NAME TABLE_NAME_F,y2.COLUMN_NAME COLUMN_NAME_F,y2.CONSTRAINT_NAME CONSTRAINT_NAME_F
from ALL_CONSTRAINTS y
inner join t1 y1 ON y1.CONSTRAINT_NAME =y.CONSTRAINT_NAME
inner join t1 y2 ON y2.CONSTRAINT_NAME =y.R_CONSTRAINT_NAME;

--表主键信息
SELECT t.CONSTRAINT_NAME,t1.OWNER,t1.TABLE_NAME,t1.COLUMN_NAME
FROM ALL_CONSTRAINTS t
INNER JOIN ALL_CONS_COLUMNS t1 ON t1.CONSTRAINT_NAME =t.CONSTRAINT_NAME 
 AND t1.OWNER=t.OWNER AND t1.TABLE_NAME=t.TABLE_NAME
WHERE t.CONSTRAINT_TYPE='P' AND t.OWNER='DATA-MASTER-DEV'
AND t.TABLE_NAME IN('test_1')
ORDER BY t1.TABLE_NAME,t1.POSITION;

--序列查询
select sequence_name, min_value, max_value, increment_by
from all_sequences
where sequence_owner = 'DM';

--函数式索引查询
select idx.name, fbi_def(idx.ID) from
(SELECT id FROM SYS.SYSOBJECTS WHERE TYPE$='SCH' AND name = 'DATA-MASTER-DEV') sch
join (select * from SYSOBJECTS where name='table_name' and subType$='UTAB') tab on sch.id=tab.schid
join (select * from SYSOBJECTS where name='index_name' and subType$='INDEX') idx on idx.pid=tab.id and idx.schid=sch.id;

查询正在执行的sql

#ORACLE的查询正在执行的sql
select a.sid,
       a.serial#,
       a.paddr,
       a.machine,
       nvl(a.sql_id, a.prev_sql_id) sql_id,
       b.sql_text,
       b.sql_fulltext,
       b.executions,
       b.first_load_time,
       b.last_load_time,
       b.last_active_time,
       b.disk_reads,
       b.direct_writes,
       b.buffer_gets
  from v$session a, v$sql b
 where a.username = sys_context('USERENV', 'CURRENT_USER')
       and nvl(a.sql_id, a.prev_sql_id) = b.sql_id;
       #and a.status = 'ACTIVE'

#DM 查询当前session执行历史：
select * from v$sessions ORDER BY CREATE_TIME DESC;
#dm开启、关闭sql记录
https://www.modb.pro/db/655045