Oracle常用语句（建议收藏）

1.复制表结构及其数据

create table table_name_new as select * from table_name_old

2.只复制表结构

create table table_name_new as select * from table_name_old where 1=2; 

3.只复制表数据

如果两个表结构一样：
insert into table_name_new select * from table_name_old 

如果两个表结构不一样：
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

4.如何统计两个表的记录总数?

　select (select count(id) from aa)+(select count(id) from bb) 总数 from dual; --总数那是没有单引号的，双引号可以。

5.返回大于等于N的最小整数值?

SELECT CEIL(N) FROM DUAL; 

6.返回当前月的最后一天?

　SELECT LAST_DAY(SYSDATE) FROM DUAL; 

7.如何查找重复记录？

　SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAMe WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

8.如何删除重复记录？

DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2); 

9.表

        查看用户下所有的表 
        select * from user_tables; 

        查看名称包含log字符的表 
        select object_name,object_id from user_objects 
            where instr(object_name,'LOG')>0; 

        查看某表的创建时间 
       select object_name,created from user_objects where object_name=upper('&table_name'); 

        查看某表的大小 
       select sum(bytes)/(1024*1024) as "size(M)" from user_segments 
            where segment_name=upper('&table_name'); 

        查看放在ORACLE的内存区里的表 
      select table_name,cache from user_tables where instr(cache,'Y')>0; 

10.序列号

查看序列号，last_number是当前值

select * from user_sequences;