select table_name from user_tables; //查看当前用户拥有的表
select table_name from all_tables; //查看所有用户的表
select table_name from dba_tables; //查看所有用户的表包括系统表
select * from user_tab_columns where TABLE_NAME='某表名称';
//查看当前用户下某表所有字段
select *from all_tab_columns where TABLE_NAME='某表名称';
select* from dba_tab_columns where TABLE_NAME='某表名称';
user_tab_columns:
table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
all_tab_columns ,dba_tab_columns比user_tab_columns多了一个ower
select * from user_tab_comments //查看当前用户下所有表注释
select * from user_col_comments where TABLE_NAME='某表名称';
//查看当前用户下某表所有字段注释
user_tab_comments:
table_name,table_type,comments
user_col_comments:
table_name,column_name,comments
concat和||的区别 || 可以无限拼接,类似于拼接字符串时候的 +,而concat 是oracle中的函数,CONCAT(char1 , char2)
-- 创建自增序列
create sequence APP_SEQ
minvalue 1
maxvalue 999999
start with 1
increment by 1
nocache;
-- 批量自增序列
select 'create sequence '||table_name||'_ID'||
' minvalue 1
maxvalue 999999
start with 1
increment by 1
nocache;' FROM user_tables;
-- 创建触发器 create or replace trigger APP_TRI before insert on MMC_APP_BASE_INFO for each row begin select APP_SEQ.nextval into :new.appno fromdual; end;
-- 批量创建触发器 select 'create or replace trigger TRI_'||table_name||'_ID '|| 'before insert on '||table_name||' for each row begin select '||table_name||'_ID'||'.nextval into :new.appno from dual; end;' FROM user_tables;
select * from all_triggers where table_name IN(SELECT table_name FROM user_tables);
SELECT USERNAME FROM dba_users;
原文地址:https://blog.csdn.net/qq_32392597/article/details/83013933