Oracle 查询表结构、主键自增

查询表结构

select t.column_name,
    decode(s.data_type,
                'date',
                'date',
                s.data_type ||'('|| s.data_length||')'),
                t.comments
                from all_col_comments t, all_tab_columns s
            where t.table_name = 'SKK_IMP_OPER_RENT_CONTR'
                and s.table_name = 'SKK_IMP_OPER_RENT_CONTR'
                and t.column_name = s.column_name;

主键自增

-- 查询 oracle 表空间
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

-- 查询 oracle 所有用户
select * from dba_user;
select * from all_users;

-- 查询 用户 pbbase 所有的 skk 开头的表
select owner,table_name,tablespace_name,status from all_tables
where owner = 'pbbase' and table_name like 'SKK%';
select owner,table_name,status from all_tables
where owner = 'pbbase' and table_name like 'SKK%';
--==========================
--建立 skk_tables 表
create table skk_tables(
id int primary key,
batch_id int,
table_name varchar(80)
);
--建立 skk_tables2 表
create table skk_tables2(
id int primary key,
batch_id int,
table_name varchar(80)
);

--建立自增序列
drop sequence skk_table_id_sequence;
create sequence skk_table_id_sequence
minvalue 1
nomaxvalue
increment by 1
start with 1
nocache;

--初始化序列
select skk_table_id_sequence.nextval from dual;
--查询当前序列值
select skk_table_id_sequence.currval from dual;

--创建触发器
drop trigger skk_table_insert_id_trigger
create or replace trigger skk_table_insert_id_trigger
before insert on skk_table for each row
begin
select skk_table_id_sequence.nextval into:new.id from dual;
end;

select * from dba_objects where object_type = 'trigger' and
object_name = 'skk_table_inset_id_trigger'
-- 修改 skk_table2 的主键
update skk_table2 set id=id+100;
insert into skk_table

--新建 skk_table3 数据为 skk_table 的 id ， skk_table2 的表名, 使得 
skk_table 与 skk_table3 的 id 和表名 保证一致
-- 通过 id 联合查询，可查需要导入 impala 而 oracle 不存在的表
create table skk_tables3 as
(select a.id,4 as batch_id,b.table_name from skk_table a inner join skk_table2 b on a.table_name = b.table_name)

--删除多余数据
delete from skk_tables2 where id<100;
truncate table skk_table2;

--查询 skk_table
select * from skk_tables;
select * from skk_tables2;
select * from skk_tables3;

alter table skk_table3 change batct_id batch_id int;
alter table skk_table3 modify column batch_id int;

--Oracle  有的 skk表 但是无导入impala 需求
select * from skk_table where table_name not in (select table_name from skk_table3);
--Oracle 没有的skk 但是需要导入impala
select * from skk_table where table_name not in
(select table_name from skk_table3) order by skk_table.id;


--Oracle 源表不存在
select * from skk_table a inner join skk_table2 b on
a.table_name = b.table_name;

--查询被锁会话ID和系列详情
select session_id from v$locked_object
select sid,serial#,username,osuser from v$session where sid = 2286

--杀掉会话 'sid,serial#'
alter system kill session '2286,25936';

查询表结构、表空间

--查所有表
select * from all_tables;
--oracle查用户下的所有表
select * from all_tables where owner ='table_owner'
--oracle查建表语句
SELECT DBMS_METADATA.GET_DDL ('TABLE','table_name') FROM DUAL
--oracle查表字段标注释(没有类型)
SELECT * FROM user_col_comments WHERE TABLE_NAME='table_name'
--查表结构 number 的精度和尺度 含义
select ut.COLUMN_NAME,uc.comments,ut.DATA_TYPE,ut.DATA_LENGTH,ut.DATA_PRECISION,ut.DATA_SCALE 
from user_tab_columns ut inner JOIN user_col_comments uc on ut.TABLE_NAME = uc.table_name and ut.COLUMN_NAME = uc.column_name 
where ut.Table_Name='table_name'
order by ut.column_name
注释:table_name与table_owner需替换成自己的