oracle 学习sql

sql检索 拼接所有表所有列的注释语句
select 'COMMENT ON COLUMN DEVSLSC.' || replace(t.TABLE_NAME,'_BAK','') || '.' || t.COLUMN_NAME ||
       ' is ' ||''''|| t.COMMENTS ||''''|| ';'
  from user_col_comments t
 where t.TABLE_NAME like '%_BAK'
   and t.COMMENTS is not null;
   


sql检索 拼接所有表的删除语句
   drop table t_ac_dictdeta_bak;
   
   select 'drop table '||t.TABLE_NAME||' ;' from user_tables t where t.TABLE_NAME like '%_BAK';
   


sql检索  拼接所有表的主键添加语句


select 'alter table '||p.TABLE_NAME||' add constraint ' || p. CONSTRAINT_NAME  ||' primary key (' || u.COLUMN_NAME||');'  
from user_constraints p, user_cons_columns u where  p.owner = 'DEVSLSC' AND p.CONSTRAINT_TYPE='P' AND p.TABLE_NAME IN (
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT LIKE '%_BAK') and p.CONSTRAINT_NAME = u.CONSTRAINT_NAME


oracle  导出语句  导入语句


exp DEVSLSC/oracle@172.16.7.10/TESTDB owner='DEVSLSC' file=d:/to_ceshi.dmp log=d:/to_ceshi.log

imp testsl/oracle@172.16.7.45/HOLYTEST FROMUSER='DEVSLSC' TOUSER='testsl' FILE=D:/to_ceshi22.dmp log=d:/to_ceshi333.log IGNORE=Y

oracle 创建用户

create user devtest10 identified by dev10 

default tablespace TBS_BCP_DAT 

temporary tablespace user_temp; 

grant connect,resource,dba to devtest10;

oracle 创建tablespace

create tablespace TBS_BCP_DAT 

logging 

datafile 'D:\app\Administrator\oradata\orcl\TBS_BCP_DAT.dbf'

size 50m 

autoextend on 

next 50m maxsize 20480m 

extent management local; 

Oracle 11G在用EXPORT导出时，空表不能导出。

　　11G中有个新特性，当表无数据时，不分配segment，以节省空间

　　解决方法：

　　1、insert一行，再rollback就产生segment了。

　　该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。

　　2、设置deferred_segment_creation 参数

　　该参数值默认是TRUE，当改为FALSE时，无论是空表还是非空表，都分配segment。

　　需注意的是：该值设置后对以前导入的空表不产生作用，仍不能导出，只能对后面新增的表产生作用。如需导出之前的空表，只能用第一种方法。

　　搞了我好久，最后查到这个方法。

　　用以下这句查找空表

　　select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0

　　把查询结果导出，执行导出的语句

　　然后再执行

　　exp 用户名/密码@数据库名 file=D:\dmp\exp_sms20110224.dmp log=D:\dmp\exp_smsrun.log

　　成功！

序列检索

select 序列.nextval from dual

merger函数  同时进行update和inster操作

merge  into t2 y using  (select * from t2 where t2.name='bdddd'union

select 3,8,'d','bbbbbbb' from dual) d on (y.id=d.id)

when matched then

  update set description='43214311'

when not matched then

  insert values(3,8,'d','bbbbbbb');

listagg函数 列转行函数

LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

select id,listagg(value,',') within group(order by id ) as value from tb group by id

递归查找

connect by

select direname from t_bd_goods_dire b connect by prior parentid= direid start with direid =807 order by direlevel desc

递归查找并且列转行

select sys_connect_by_path(direname,',') as direname from t_bd_goods_dire b connect by prior parentid= direid start with direid =807 order by direlevel