执着_QQ-优快云博客

原创查找某张表中未使用到的连续数值

SELECT * FROM HXL_CL_ZH_20150108select 1106.99 * 8 / 1024 MB FROM dual;SELECT 18633.78 * 26796 FROM dual;--分析数据WITH T AS (SELECT 12340000 beg,12349999 end_n FROM dual UNION ALL

2015-03-19 17:02:40 381

原创 EXP-IMP使用

导出导入操作要确保两个数据库的字符集保持一致一：首先应该查询数据库的字符集用已知的用户名和密码，通过【CMD】黑窗口进入ORACLE的SQLPLUS中，进入方式：在黑窗口中输入【sqlplus 用户名/密码@实例名】，回车进入进入后查询导出服务器的字符集，运行如下命令【select userenv('language') from dual;】

2015-03-19 17:02:33 367

原创使用DBLINK迁移表结构

DECLARE CURSOR c_get_table_sql IS SELECT a.table_name FROM dba_tables@dblink a WHERE a.owner = 'GXKF' AND ROWNUM = 1; --源数据从DBLINK中找出i NUMBER(10);v_tab_name VARCHAR2(100);

2015-03-19 17:01:58 1000

原创查找连续的数据2

WITH t AS (SELECT 1 idd,175093762 nu FROM dual UNION SELECT 1,175093763 FROM dual UNION SELECT 1,175093764 FROM dual UNION SELECT 1,175093765 FROM dual UNION SELECT 1,175093766 FROM dual UNI

2015-03-19 17:00:54 322

原创删除表中重复的数据

SQL1：DELETE FROM HXL WHERE ROWID IN (SELECT ROWID FROM (SELECT A.*, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS RN_　FROM HXL A)

2015-03-19 16:56:40 290

原创 ROWID更新提升UPDATE性能，不能用merge时

declare maxrows number default 100000; row_id_table dbms_sql.urowid_table; --currcount_table dbms_sql.number_Table; object_name_table dbms_sql.varchar2_Table; cursor cur_b is

2015-03-19 16:53:57 626

原创利用分析函数生成连续数据

WITH cat AS ( select '1000' ID, '1010'+1 PID FROM dual union SELECT '1011' ID, '1050'+1 PID FROM dual UNION SELECT '1051' ID, '1056'+1 PID FROM dual UNION SELECT '1059' ID, '1

2015-03-19 16:53:56 401

原创批量游标处理大数据

--批量游标处理大数据declare type t_rid is table of rowid index by binary_integer; l_t_rid t_rid; cursor cur_rid is( select rowid from bigtable where owner = 'SCOTT'); ln_rowcnt numbe

2015-03-19 16:51:10 1177