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_bak\collection.dmp --按表导入-- imp test2/syj@orcl tables=(collection) file=d:\db_bak\collection.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_bak\dump'; grant read, write on directory my_dire to test; --创建临时表空间 create temporary tablespace test_temp tempfile 'd:\db_bak\tablespace\test_temp.dbf' size 32m autoextend on next 32m extent management local; --创建数据表空间 create tablespace test_data logging datafile 'd:\db_bak\tablespace\test_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;