Oracle 数据库 有用的sql语句

linux Oracle环境变量：source /home/oracle/.profile_...

select rownum id from dual connect by level<=10;

SELECT to_date('2014-12-01', 'yyyy-mm-dd') +
       numtodsinterval(rownum - 1, 'day')
  FROM DUAL
CONNECT BY ROWNUM <= (to_date('2015-01-01', 'yyyy-mm-dd') -
           to_date('2014-12-01', 'yyyy-mm-dd'))

wm_concat(分组时 列值相加) 加排序

select ts.peplename peplename,
       max(ts.inspath) inspath,
       max(ts.instime) instime
  from (select t.executorid peplename,
               wm_concat('[' || t.track_lon || ',' || t.track_lat || ']') over(partition by t.executorid order by t.tracktime) inspath,
               t.tracktime instime
          from ins_track t
         where 1 = 1
           and to_char(t.tracktime, 'yyyy-MM-dd') = '2015-03-08') ts
 group by ts.peplename

复制表

create table abc as select * from abc_dblink where rownum<3

  

 查询重复的数据

select * from table1 a where rowid !=(select max(rowid) from table1 b where a.name1=b.name1 and a.name2=b.name2……)

oracle 导入、导出

--按表导出--
exp test/1234@orcl tables=(collection) file=d:db_bakcollection.dmp
--按表导入--
imp test2/syj@orcl tables=(collection) file=d:db_bakcollection.dmp


--按用户导出--
expdp test/123456 schemas=epdb dumpfile=test.dmp directory=my_dire logfile=test.log
--按用户导入--
impdp test/123456 schemas=epdb dumpfile=test.dmp directory=my_dire logfile=test.log remap_schema=test:test2 remap_tablespace = test_data:test_data2


--全库导出--
expdp test/123456 directory=my_dire dumpfile=test.dmp full=y
--全库导入--
impdp test/123456 directory=my_dire dumpfile=test.dmp full=y


--创建用户：
create user test identified by 123456;
grant dba to test;


--创建导入/导出路径：
create directory my_dire as 'd:db_bakdump';
grant read, write on directory my_dire to test;


--创建临时表空间
create temporary tablespace test_temp 
tempfile 'd:db_bak	ablespace	est_temp.dbf' 
size 32m 
autoextend on 
next 32m 
extent management local;


--创建数据表空间 
create tablespace test_data 
logging 
datafile 'd:db_bak	ablespace	est_data.dbf' 
size 1024m 
autoextend on 
next 32m 
extent management local;

创建database link

drop public database link zxdb;

create public database link zxdb
connect to zxjc identified by Hr123456
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';

查询数字翻译

select to_char( to_date(1216513,'J'),'Jsp') from dual

  

 递归查询

SELECT t.orgid,
       t.orgname,
       SUBSTR(SYS_CONNECT_BY_PATH(t.orgname, '->'), 3) NAME_PATH
  FROM sys_org t
 START WITH t.orgid = '0'
CONNECT BY PRIOR t.orgid = t.parentorgid;

数据库表数据回滚

alter table 表名 enable row movement;
flashback table 表名 to timestamp to_timestamp('2011-03-04 05:00:00','yyyy-mm-dd HH24:MI:SS');

数据量查询

select t1.table_name, t1.col_num, t2.data_num, t1.col_num * t2.data_num
  from (select t.table_name table_name, count(1) col_num
          from user_tab_cols t
         group by table_name) t1,
       (select t.table_name table_name, t.num_rows data_num
          from user_tables t) t2
 where t1.table_name = t2.table_name;

存储过程循环添加测试数据

create or replace procedure InsertIntoGPS is
i int;
begin
  i:=0;
  while i<100000 loop 
  insert into BIZ_T_GPSDATA (ID, TYPE, XPOINT, YPOINT, LOCATIONTIME, USERID, COORDINATE)
              values (sys_guid(), '1', '106.62055', '29.44947', sysdate, '刘若英', '1');
  insert into BIZ_T_GPSDATA (ID, TYPE, XPOINT, YPOINT, LOCATIONTIME, USERID, COORDINATE)
              values (sys_guid(), '1', '106.62455', '29.44947', sysdate, '刘德华', '1');
  insert into BIZ_T_GPSDATA (ID, TYPE, XPOINT, YPOINT, LOCATIONTIME, USERID, COORDINATE)
              values (sys_guid(), '1', '106.62855', '29.44947', sysdate, '张家辉', '1');
  insert into BIZ_T_GPSDATA (ID, TYPE, XPOINT, YPOINT, LOCATIONTIME, USERID, COORDINATE)
              values (sys_guid(), '1', '106.70455', '29.44947', sysdate, '贾静雯', '1');
   i:=i+1;
  end loop;
  commit;
  end;