6、简易研究ROWNUM的方法



实验获取的知识点（同时也是易错点）

1、永远找不到rownum>1的数据

原因：ROWNUM是从1开始排序的，第一条始终是1，当查询到第一条记录时，该记录的ROWNUM为1，但条件要求ROWNUM>1，不符合要求继续查询下一条，由于前面无符合条件的记录，所以下一条就变成第一条，而第一条ORACLE始终规定为1，如此下去，自然找不到ROWNUM>1的值了。

2、永远找不到rownum=2 的数据（不只是2，只要是大于1的，都找不到，如rownum=3也不可能找到）
原因：不用多说了，就是上面的解释。

3、上诉的两种错误的主观想法该如何实现呢？只要使用视图，将rownum实体化，即可成功，具体见实验。

4、字段排序再取前n条数据的误区

oracle会先按物理顺序取出满足rownum条件的记录，然后再对这些记录按照order by 的字段排序，因此如果不注意写法，往往不是我们想要的结果
        
drop table ljb_test ;
create table ljb_test as select * from dba_objects;

SQL> drop table ljb_test ;

Table dropped

SQL> create table ljb_test as select * from dba_objects;

Table created

SQL> select count(*) from ljb_test;

  COUNT(*)
----------
     80010

SQL> select count(*) from ljb_test where rownum>0;

  COUNT(*)
----------
     80010

SQL> select count(*) from ljb_test where rownum>1;

  COUNT(*)
----------
         0  ----根本找不到你所想要的记录！


SQL> select count(*)  from ljb_test where rownum=1;

  COUNT(*)
----------
         1

SQL> select count(*)  from ljb_test where rownum=2;

  COUNT(*)
----------
         0   ----根本找不到你所想要的记录


如果真要取你想的记录，该如何做呢？
方法很简单，用上子查询，将rownum实体化，即可解决问题！

SQL> select count(*) from (select t.*,rownum rn from ljb_test t) where rn>1;

  COUNT(*)
----------
     80009
SQL> select count(*) from (select t.*,rownum rn from ljb_test t) where rn=2;

  COUNT(*)
----------
         1

--以下写法是从表中按照物理顺序取出5条记录，然后再把这些记录按照object_name的列进行排序，往往不是我们想要的结果。
SQL> select object_id,object_name from ljb_test where rownum <= 5 order by object_name;

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
        28 CON$
        29 C_COBJ#
        20 ICOL$
        44 I_USER1
        15 UNDO$

---以下写法是根据object_name先排序，这个正是我们所期望的先排序、再取特定记录数的算法。
SQL> select object_id,object_name from (select object_id,object_name from ljb_test order by object_name) where rownum <= 5;

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
     30796 /1000e8d1_LinkedHashMapValueIt
     30797 /1000e8d1_LinkedHashMapValueIt
     17108 /1005bd30_LnkdConstant
     17109 /1005bd30_LnkdConstant
     12512 /10076b23_OraCustomDatumClosur

--以上是rownum的一些基础知识，实际应用中rownum的应用范围非常广泛，体现在下面三点，但是限于简练的原则，避免文章太过复杂，将在后续优化篇中体现三点思路（这里就略去不说）
1、在分页算法中灵活应用，将起到意想不到的效果。
2、减少函数调用（利用实体化视图）
3、利用rownum优化表连接

7、缩小表空间方法的简易研究

做了个小实验，关于表空间的小小研究，具体步骤如下：

获取知识点

1、查看表空间使用的脚本方法
     简单，无非就是dba_data_Files和dba_free_space两张数据字典的结合。

2、缩小表空间可以考虑使用resize方法（10g也可以考虑shrink)

3、表空间能缩小多少的估算方法

4、10G中，要把当前表空间的回收站清空一下，否则无法resize表空间，这点我忽略了，多谢小V兄弟提醒！

5、临时表空间使用的关键字tempfile
alter database tempfile '文件路径名'  resize xxM;
可以通过select max(segblk#)  from v$sort_usage来查看当前能缩小的最大空间，可以和本实验普通文件的缩小方法比较。



----首先，用如下方法找查看当前表空间使用情况

SELECT A.TABLESPACE_NAME "表空间名",
       A.TOTAL_SPACE "总空间(M)",
       NVL(B.FREE_SPACE, 0) "剩余空间(M)",
       A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(M)",
       trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) "剩余百分比%"
  FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024 ,2) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024  ),2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;


           表空间名        总空间(M)        剩余空间(M)        使用空间(M)        剩余百分比%
        TBS_FJZHJS        7167                           6648.37        518.63                        92.76

SQL>  ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M;

ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M

ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

奇怪，这个是为什么呢？

1、计算表空间能缩小多少的方法

SQL> select max(block_id) from dba_extents where file_id=78;

MAX(BLOCK_ID)
-------------
       524153

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> select 524153*8/1024 from dual;

524153*8/1024
-------------
4094.9453125  ---由此可见，这个表空间的当前的数据文件，至少需要4G这么大，前面要缩小为2G，当然失败了！

--我们还可以看看是哪个对象占用
SQL> select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=78 and block_id=524153
  2  ;

OWNER                          SEGMENT_NAME                                                                     SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID      BYTES     BLOCKS
------------------------------ -------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ---------- ----------
ZHJS_CONFIG                    PK_TPR_TABLE_GROUP                                                               INDEX              TBS_FJZHJS                              0      65536          8

SQL>

----这里非常明显的问题是，表空间明明还有90%几剩余，居然缩小不下来，显而易见是HWM的问题，这种情况我们忽然想到用EXP/IMP来解决这个问题

这里略去EXP/IMP传输表空间的步骤，单独列在下一个实验步骤第8小节中（为了单独成文）

做完传输表空间的导出再次导入，原本以为可以缩小表空间了，实际发现并非如此！

SQL> select file#,name from v$datafile where name like '%FJ_ZHJS%';

     FILE# NAME
---------- --------------------------------------------------------------------------------
        16 D:\ORACLE\PRODUCT\10.2.0\ORADATA\FJ_ZHJS_03.DBF
        17 D:\ORACLE\PRODUCT\10.2.0\ORADATA\FJ_ZHJS_02.DBF
        18 D:\ORACLE\PRODUCT\10.2.0\ORADATA\FJ_ZHJS.DBF

SQL> select max(block_id) from dba_extents where file_id=17;

MAX(BLOCK_ID)
-------------
       524153

---看到这个524153，大喊糟糕，玩了EXP/IMP表空间，对缩小数据文件没帮助！

SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M;

ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M

ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

啥原因呢？？？

换个方法

执行这个
SELECT 'alter table ' || OWNER || '.' || TABLE_NAME ||
       ' move TABLESPACE USERS;'
  FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'TBS_FJZHJS'


然后再MOVE回来

结果还是不成立

SQL> select max(block_id) from dba_extents where file_id=17;

MAX(BLOCK_ID)
-------------
       524153


SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M;

ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M

ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

---把索引也移走一下

SELECT 'alter  index' || OWNER || '.' || INDEX_NAME ||
       ' rebuild TABLESPACE USERS;'
  FROM DBA_INDEXES
WHERE TABLESPACE_NAME = 'TBS_FJZHJS'

然后再REBUILD一下MOVE回来

SQL> select max(block_id) from dba_extents where file_id=17;

MAX(BLOCK_ID)
-------------
       519681

SQL> select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=17 and block_id= 519681;

OWNER                          SEGMENT_NAME                                                                     SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID      BYTES     BLOCKS
------------------------------ -------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ---------- ----------
ZHJS_CONFIG                    IDX_CLASSIFIED_TARIFF_A_6                                                        INDEX              TBS_FJZHJS                              0      65536          8

SQL>
SQL> select max(block_id) from dba_extents where file_id=17;

MAX(BLOCK_ID)
-------------
       519673

---看到这个519673，心里一寒，没啥用，还是没啥缩小！

--执行如下发现一堆很大得到BLOCK_ID的，到某个层面后，忽然变小了。。
select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks,block_Id from dba_extents where file_id=17
ORDER BY Block_id DESC

要不考虑一下直接MOVE走，不移回来了。。。。
然后查看如下：

SQL>  select max(block_id) from dba_extents where file_id=17;

MAX(BLOCK_ID)
-------------
         2025

现在肯定可以缩小表空间了，看到这个2025，心花怒放，现在肯定可以缩小了！


SQL> select 2025*8/1024 from dual;

2025*8/1024
-----------
15.8203125

SQL>
可以缩小到15M啊

实际操作 ，可是实际上居然不能缩小？？？？？
SQL>  ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M;

ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M

ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

实在奇怪，因为这个file_id=17的文件只生效一个记录了，其他都被我MOVE了啊？而且BOCK_ID是2025，超级小啊！！！！

select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks,block_Id from dba_extents where file_id=17
ORDER BY Block_id DESC
           OWNER        SEGMENT_NAME        SEGMENT_TYPE        TABLESPACE_NAME        EXTENT_ID        BYTES        BLOCKS        BLOCK_ID
1        VAJS        PLAN_TABLE        TABLE        TBS_FJZHJS        0        65536        8        2025

SQL> drop table VAJS.PLAN_TABLE;

Table dropped

SQL>  select max(block_id) from dba_extents where file_id=17;

MAX(BLOCK_ID)
-------------

select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks,block_Id from dba_extents where file_id=17
ORDER BY Block_id DESC

都已经无记录了

SQL>  ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M;

ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M

ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据


---这里感谢小V兄弟的提醒

SELECT * FROM dba_recyclebin;
发现刚才DROP 的东东在垃圾箱里！！！！

SQL> PURGE recyclebin
  2  ;


SQL>  ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M;

Database altered







8、EXP/IMP传输表空间的简易方法

--玩啥呢，就玩EXP/IMP表空间吧，我用EXP/IMP多次了，从未用过EXP出表空间的方法，虽然官方文档是看过多次了，但是从未实验过。

知识点：

1、传输表空间必须是SYSDBA用户操作

2、表空间里的对象集是自包含的(对象及其索引都在此表空间中)
   这个要用 exec sys.dbms_tts.transport_set_check来执行校验，然后在transport_set_violations中检查

3、要将传输的表空间设置为只读
  alter tablespace tbs_fjzhjs  read only;

4、完成后要将传输导入的表空间属性设置为可读写。
  alter tablespace tbs_fjzhjs read write;




C:\Documents and Settings\fujitsu>exp system/rqrq@rqrq tablespaces='TBS_FJZHJS' transport_tablespace
=y file=c:\tbs_fjzhjs.dmp

Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 20:40:47 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
EXP-00044: 必须以 'SYSDBA' 身份连接, 才能进行时间点恢复或可传输的表空间导入
EXP-00000: 导出终止失败

C:\Documents and Settings\fujitsu>exp 'sys/change_on_install as sysdba ' tablespaces='TBS_FJZHJS' t
ansport_tablespace=y file=c:\tbs_fjzhjs.dmp

Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 20:47:57 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
EXP-00008: 遇到 ORACLE 错误 29335
ORA-29335: 表空间 'TBS_FJZHJS' 不为只读
ORA-06512: 在 "SYS.DBMS_PLUGTS", line 507
ORA-06512: 在 line 1
EXP-00000: 导出终止失败

C:\Documents and Settings\fujitsu>

C:\Documents and Settings\fujitsu>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 4 20:48:32 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace tbs_fjzhjs  read only;

C:\Documents and Settings\fujitsu>exp 'sys/change_on_install as sysdba ' tablespaces='TBS_FJZHJS' t
ansport_tablespace=y file=c:\tbs_fjzhjs.dmp

Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 21:20:57 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
前面略去。。。
. . 正在导出表                      PLAN_TABLE
. . 正在导出表                            TEST
. . 正在导出表          TPVA_MGACCT_MAP_091028
. . 正在导出表         TPVA_MGACCT_RULE_091028
. . 正在导出表         TPVA_SETT_DEFINE_091028
. . 正在导出表          TPVA_SETT_JIETI_091028
. . 正在导出表           TPVA_SETT_RULE_091028
. . 正在导出表         TPVA_TANFEN_RULE_091028
. . 正在导出表                    TP_JUDGE_ACC
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
成功终止导出, 没有出现警告。

Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 21:29:41 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
EXP-00008: 遇到 ORACLE 错误 29341
ORA-29341: 可传送集不是自包含的
ORA-06512: 在 "SYS.DBMS_PLUGTS", line 1387
ORA-06512: 在 line 1

SQL> exec sys.dbms_tts.transport_set_check('TBS_FJZHJS',true);

PL/SQL 过程已成功完成。


SQL>  select * from transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
Index LJB.IDX1_BILLING_NBR in tablespace TBS_FJZHJS points to table LJB.TICKET_1
60 in tablespace USERS

Index LJB.IDX2_BILLING_NBR in tablespace TBS_FJZHJS points to table LJB.TICKET_1
68 in tablespace USERS

SQL> drop index LJB.IDX1_BILLING_NBR ;

索引已删除。

SQL> drop index LJB.IDX2_BILLING_NBR ;

索引已删除。

SQL> exec sys.dbms_tts.transport_set_check('TBS_FJZHJS',true);

PL/SQL 过程已成功完成。

SQL>
SQL>  select * from transport_set_violations;

未选定行

. . 正在导出表                      PLAN_TABLE
. . 正在导出表                            TEST
. . 正在导出表          TPVA_MGACCT_MAP_091028
. . 正在导出表         TPVA_MGACCT_RULE_091028
. . 正在导出表         TPVA_SETT_DEFINE_091028
. . 正在导出表          TPVA_SETT_JIETI_091028
. . 正在导出表           TPVA_SETT_RULE_091028
. . 正在导出表         TPVA_TANFEN_RULE_091028
. . 正在导出表                    TP_JUDGE_ACC
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
成功终止导出, 没有出现警告。


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> drop tablespace tbs_fjzhjs;
drop tablespace tbs_fjzhjs
*
第 1 行出现错误:
ORA-01549: 表空间非空, 请使用 INCLUDING CONTENTS 选项


SQL> drop tablespace tbs_fjzhjs including contents
  2  ;

表空间已删除。


C:\Documents and Settings\fujitsu>imp 'sys/change_on_install as sysdba ' tablespaces='TBS_FJZHJS' tr
ansport_tablespace=y datafiles='D:\oracle\product\10.2.0\oradata\FJ_ZHJS.DBF','D:\oracle\product\10.
2.0\oradata\FJ_ZHJS_02.dbf','D:\oracle\product\10.2.0\oradata\FJ_ZHJS_03.dbf'   file='c:\tbs_fjzhjs.
dmp'


. . 正在导入表        "TPVA_MGACCT_MAP_091028"
. . 正在导入表       "TPVA_MGACCT_RULE_091028"
. . 正在导入表       "TPVA_SETT_DEFINE_091028"
. . 正在导入表        "TPVA_SETT_JIETI_091028"
. . 正在导入表         "TPVA_SETT_RULE_091028"
. . 正在导入表       "TPVA_TANFEN_RULE_091028"
. . 正在导入表                  "TP_JUDGE_ACC"
. 正在将 LJB 的对象导入到 LJB
. 正在将 ZHJS_CONFIG 的对象导入到 ZHJS_CONFIG
. 正在将 LJB 的对象导入到 LJB
. 正在将 ZHJS_CONFIG 的对象导入到 ZHJS_CONFIG
即将启用约束条件...
. 正在将 SYS 的对象导入到 SYS
成功终止导入, 没有出现警告。


Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace tbs_fjzhjs read write;

表空间已更改。


9、切换临时表空间和回滚段表空间简易方法

知识点（某些场合，在临时表空间和回滚段表空间异常扩展无法回收的时候，需要采用这种方法）

1、了解当前临时表空间是哪个
2、如何切换临时表空间
3、了解当前回滚段表空间是哪个
4、如何切换回滚段表空间。


以下是我轻松完成切换临时表空间的实验过程：

SQL> SELECT username,temporary_tablespace FROM dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
LJB                            TEMP2
ZHJS_APPTEST                   TEMP2
ZHJS_APP                       TEMP2
MGMT_VIEW                      TEMP2
SYS                            TEMP2
SYSTEM                         TEMP2

SQL> SELECT NAME FROM v$tempfile;

NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\TEMP02.DBF


SQL> create temporary tablespace temp tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\TEMP03.DBF' size 100M;

Tablespace created

SQL> alter tablespace temp add tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\TEMP04.DBF' size 20M;

Tablespace altered

SQL> alter database default temporary tablespace temp;

Database altered

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
LJB                            TEMP
WMSYS                          TEMP
CTXSYS                         TEMP
MGMT_VIEW                      TEMP
SYS                            TEMP
SYSTEM                         TEMP


SQL> drop tablespace temp2;

Tablespace dropped


以下是我轻松完成切换回滚段表空间的过程

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb

--可看出当前回滚段表空间为undotbs1

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

--查看回滚表空间的数据文件路径及大小
SQL> select file_name,bytes/1024/1024/1024 from dba_data_Files where tablespace_name like 'UNDOTBS1';

FILE_NAME                                                                        BYTES/1024/1024/1024
-------------------------------------------------------------------------------- --------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\UNDOTBS01.DBF                                      0.7568359375

---undo segment的扩展状态无异常：
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------------- ---------------------- ----------
         0          0   0.00035858154296875    0.00035858154296875          0
         2          0   0.00109100341796875    0.00597381591796875          1
         3          0   0.00109100341796875    0.00499725341796875          1
         4          0   0.00109100341796875    0.00402069091796875          1
        10          0   0.00109100341796875    0.00389862060546875          3
         6          0   0.00109100341796875     0.0108566284179688          3
         7          0   0.00109100341796875    0.00499725341796875          1
         8          0   0.00109100341796875    0.00695037841796875          1
         9          0   0.00109100341796875    0.00499725341796875          2
         1          0   0.00109100341796875    0.00499725341796875          1
         5          0   0.00109100341796875    0.00499725341796875          2

11 rows selected

---新建一回滚段表空间
SQL> create undo tablespace undotbs2 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\UNDOTBS03.DBF' size 100M;

Tablespace created

---切换
SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered

--生效
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

SQL>

10、SQLPLUS工具研究

   知识是由点到线，由线到面的一个过程，我觉得平常经常记录下工作中的心得体会，并记录下实验心得，汇总起来，形成面后，解决问题就有可能做到融会贯通。下面来探讨一下最近在工作中用到SQLPLUS的系列小结。后续会多一些类似这样开发相关的研究，并发出与大家共享。其中10.2是数据库开发大牛NEWKID兄指点的，感谢大牛NEWKID兄。
   SQLPLUS很多人用的并不多，在我观察周围来看，很多人都在使用PLSQL DEVELOPER，尤其是开发人员，更是如此，那学习SQLPLUS有啥好处呢？在我看来有如下三点
  1、当我们要在UNIX平台用SHELL访问数据库（如：做一些后台操作，定时任务等等），这个时候SQLPLUS是唯一选择。
  2、SQLPLUS 是ORACLE自带的工具，只要安装了数据库就有了，而PLSQL DEVELOPE等工具还要独立安装。并且由于是客户端工具，在网络故障或者是主机性能障碍情况下，往往根本就无法登陆进该工具。
  3、每个工具都各有所长，SQLPLUS少了PLSQL DEVELOPER的可视化的方便性，自然有其他独到的优势，在下文中会简要说明。。。

  10.1 sqlplus工具特有的COPY功能

SQL*Plus Copy 命令的语法：
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query
Append - 向已有的目标表中追加记录，如果目标表不存在，自动创建，这种情况下和Create等效。
Create - 创建目标表并且向其中追加记录，如果目标表已经存在，则会返回错误。
Insert - 向已有的目标表中插入记录，与Append不同的是，如果目标表不存在，不自动创建而是返回错误。
Replace - 用查询出来的数据覆盖已有的目标表中的数据，如果目标表不存在，自动创建。



  根据最近使用的经验来看，我认为好处有如下：

               1、直接在不同数据库中进行拷贝动作，比传统方法需建DBLINK更方便易用多了。
               2、DBLINK一旦建立，如果出于安全考虑，很可能要删除掉，否则会留下安全隐患(想着想着多想了一个理由）。
               3、CREATE TABLE XXX AS SELECT *　FROM XXX@dblink或
                 inser into table select *from xxx@dblink的方法必须要保证目标表没有该表或者已经有该表。           而COPY APPEND,REPLACE不存在自动创建，有存在则自动插入，灵活的多。（如果要判断了选择CRDATE,INSERT,可让我们有选择的余地）              
               4、在已经有数据的情况下，SQLPLUS的APPEND命令追加，比INSERT INTO SELECT 高效
               5、如果不考虑性能，比如只是实时同步两库的参数配置小表，那在SHELL中批量写COPY+REPLACE语句，CRONTAB定时同步，就可以保证两库配置表是基本一致的，这个在大表不实用，在小表就一定大有用武之地！（这个是在自己多次反复同步配置表后，无意想到的）

  此外我整了几个注意点：
               1、这些都是直接路径读，所以COPY+APPEND，INSERT也是直接提交成功的，所有的动作操作后都无法回滚。
               2、虽然主要适用于不同库之间，但是在同一库不同用户也使用，甚至同一库同一用户也一样（同用户表名要不一样），就是FROM TO写好则可。
               3、写法习惯要注意，COPY+CREATE 后面不要增加TABLE的关键字，经常收到ORACLE语法误导，会多写了这个。

实验：
本机笔记本上10G环境测试可以连到测试环境9I数据库
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\fujitsu>tnsping dev_db
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-11月-2009 10:43:26
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
已使用的参数文件:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fjpta)))OK (580 毫秒)

下面实验直接COPY命令
C:\Documents and Settings\fujitsu>sqlplus ljb/ljb
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db create  ljb_test using select * from ljb_test;

数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 LJB_TEST 已创建。

267 行选自 ljb@rqrq。
   267 行已插入 LJB_TEST。
   267 行已提交至 LJB_TEST (位于 vajs@dev_db)。

--这里注意，create ljb_test 我老是会根据ORACLE语法习惯写成create table ljb_test，忘记了SQLPLUS语法中没有这个TABLE关键字，这就是思维习惯了，呵呵。
这个等效的方法在ORACLE中如何实现，大家都知道，应该是 CREATE TABLE LJB_TEST AS SELECT *　FROM LJB_TEST@DBLINK WHERE XXX条件,这个时候就一定要建DBLINK，多了一个步骤，而且大家一定有过没有权限建DBLINK或者是忘记DBLINK语法的经历，如此COPY命令就省事多了（此外我还想到另一种情况，就是DBLINK一旦建立了，就有可能被人使用，如果出于安全不想让人用，建立了还要立即删除了，如果用COPY命令，这个安全问题就无需考虑了）。

当我们要继续新插入数据该如何写呢？
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db append vajs.ljb_test using select * from ljb.ljb_tes
t

数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
267 行选自 ljb@rqrq。
   267 行已插入 VAJS.LJB_TEST。
   267 行已提交至 VAJS.LJB_TEST (位于 vajs@dev_db)。

查查看数据，本地10G环境ljb_test表记录267条
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> SELECT COUNT(*) FROM ljb_test;

  COUNT(*)
----------
       267
9I测试环境ljb_test表记录534条，看来确实是APPEND进去了。
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as vajs
SQL> SELECT COUNT(*) FROM ljb_test;

  COUNT(*)
----------
       534
再看看LJB_TEST存在情况下，COPY +INSERT如何？
发现插入OK，记录增加到801，为了篇幅，步骤不贴！
那DROP 掉vajs 用户下的ljb_test表后呢？
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db insert vajs.ljb_test using select * from ljb.ljb_tes
t

数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)

ERROR:
ORA-00942: table or view does not exist
看出来这个INSERT 是一定需要有表存在的。不像APPEND，可以追加。

最后实验一下REPLACE(前面说过了，想依赖这个来实时同步两库的小记录配置表）

SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db replace vajs.ljb_test using select * from ljb.ljb_test

数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 VAJS.LJB_TEST 已创建。

267 行选自 ljb@rqrq。
   267 行已插入 VAJS.LJB_TEST。
   267 行已提交至 VAJS.LJB_TEST (位于 vajs@dev_db)。

发现VAJS用户下被DROP的表被建立了，记录为267
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as vajs
SQL> SELECT COUNT(*) FROM vajs.ljb_test;
  COUNT(*)
----------
       267
--再执行一次！
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db replace vajs.ljb_test using select * from ljb.ljb_test

数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 VAJS.LJB_TEST 已删除。 ----呵呵，有表后的玄机就在这，不过是先删除表再插入表而已！

表 VAJS.LJB_TEST 已创建。

267 行选自 ljb@rqrq。
   267 行已插入 VAJS.LJB_TEST。
   267 行已提交至 VAJS.LJB_TEST (位于 vajs@dev_db)。

发现9I数据库的VAJS的ljb_test表记录保持不变，看来用来做同步，还真好用：）
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as vajs
SQL> SELECT COUNT(*) FROM vajs.ljb_test;
  COUNT(*)
----------
       267

最后，经过测试，发现传统CREATE TABLE XXX AS SELECT *　FROM XXX@dblink 所花费的REDO最少
             但是INSERT INTO XXX SELECT *　FROM XXX@DBLINK却不如COPY +APPEND所花费的REDO少。
通过这个了解到，如果考虑新增数据，甚至COPY+APPND还有性能上的优势。
这个如何测试呢？我是测过了，但是考虑到文章的简洁，限于篇幅，贴出来又删除了。
请借鉴大家关注我的系列实验的第一贴，呵呵，整理还是会有派场的：）

  10.2 利用SQLPLUS的自动PRINT功能+REF CURSOR可以实现SQL SERVER中的过程中写SELECT语句的写法

  这个小节源于同事提出的问题，经大牛NEWKID兄指点后，正好整理出来，作为SQLPLUS的一个功能补充

  接触过SQL SERVER的人都知道，在SQL SERVER中可以直接在过程中写如下：
  BEGIN
   SELECT * FROM TEST where rownum=1;
  end;
  而这样写法在ORACLE的过程里是行不通的，一定会报“中缺少 INTO 子句”这样的错误，我最早接触的数据库是SQL SERVER,后来接触到ORACLE后，立即就对这个差异产生了困惑，那我们如果要这样实现，咋办呢？
  其实想想，这样的需求还真是有的：
  1、以前我最早使用SQL SERVER的时候，喜欢把非常非常复杂逻辑的SQL查询语句整到过程中去，为啥，因为到时候执行查询的时候就不要复制粘贴一大段，如果名字起的好记些，我背都背住了，然后执行一下过程名就把我要的东西查询出来了，写什么上线操作步骤，文章也可精炼多了！
  2、如果是前台展现语句能捕获输出的结果集，直接展现就OK了，否则还要插入到某表中，然后再展现某表，这个比较麻烦，（此外如果展现结果是动态的，字段在变化，岂不是要建立N张表去展现了？）

实验：
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test (id1 int,id2 int);
Table created
SQL> insert into ljb_test select rownum,rownum+2 from dual connect by rownum<=5;
5 rows inserted
SQL> commit;
Commit complete
SQL> select * from ljb_test;
                                    ID1                                     ID2
--------------------------------------- ---------------------------------------
                                      1                                       3
                                      2                                       4
                                      3                                       5
                                      4                                       6
                                      5                                       7
SQL> CREATE OR REPLACE PROCEDURE p_test ( p_min in number,p_max in number,p_ref OUT SYS_REFCURSOR)
  2  AS
  3  BEGIN
  4     OPEN p_ref for SELECT * from ljb_test where id1>=p_min and id1<=p_max;
  5  END p_test;
  6  /
过程已创建。
SQL>
SQL> SET AUTOPRINT ON
SQL> VAR p_ref REFCURSOR;
SQL> EXEC p_test(1,2,:p_ref);
PL/SQL 过程已成功完成。
       ID1        ID2
---------- ----------
         1          3
         2          4
此外大家一定对select * from table(dbms_xplan.display)这个写法不陌生，很多人一看就明白，这个是查看执行计划的语句（是在EXPLAIN PLAN FOR SQL语句后紧接着执行的命令），由此也可以得到启发，但是这样的查询出
来的结果集是由SELECT语句触发的，而非命令执行的。
table()典型例子如下：
SQL> CREATE or replace TYPE t_test AS OBJECT (
  2       id1    NUMBER
  3      ,id2    NUMBER
  4      )
  5  /
类型已创建。
SQL> CREATE TYPE tb_test AS TABLE OF t_test
  2  /
类型已创建。
SQL> CREATE OR REPLACE FUNCTION f_test RETURN tb_test
  2  AS
  3     v_ret tb_test:=tb_test();
  4  BEGIN
  5     FOR i IN 1..2 LOOP
  6         v_ret.EXTEND;
  7         v_ret(i) := t_test(i,i+2);
  8     END LOOP;
  9     RETURN v_ret;
10  END f_test;
11  /
函数已创建。
SQL> select * from table(f_test);
       ID1        ID2
---------- ----------
         1          3
         2          4
TABLE()的方法就得先定义嵌套表，然后从表里用SELECT BULK COLLECT INTO取得数据(把table()典型子中那个循环改为SELECT取数据)，再返回这个嵌套表，就能实现这个select * from ljb_test表的输出了，不过相对而言，当前需求用这个方法相比“SQLPLUS的自动PRINT功能+REF CURSOR”的方法有些不适当，因为非常繁琐！

如果一定要用select * from table()来处理，具体如下（t_test 和 tb_test 的type就不再重复建立了）

SQL> CREATE OR REPLACE FUNCTION f_test(p_min IN NUMBER,p_max IN NUMBER) RETURN tb_test
  2  AS
  3     v_ret tb_test:=tb_test();
  4  BEGIN
  5     SELECT t_test(id1,id2)
  6       BULK COLLECT INTO v_ret
  7       FROM ljb_TEST WHERE id1>=p_min AND id1<=p_max;
  8  
  9     RETURN v_ret;
10  END f_test;
11  /

Function created

SQL> select * from table(f_test(1,2));

       ID1        ID2
---------- ----------
         1          3
         2          4

SQL>
  
  10.3 SQLPLUS SPOOL 功能

SQL> select count(*) from dba_tables where wner='YXL';

  COUNT(*)
----------
         4
SQL> SPOOL c:drop_table.sql
SQL> SELECT 'DROP TABLE '|| table_name ||';' FROM dba_tables where wner='YXL';

'DROPTABLE'||TABLE_NAME||';'
--------------------------------------------------------------------------------
DROP TABLE TEST_YXL;
DROP TABLE TEST_YXL1;
DROP TABLE YXL_TEST;
DROP TABLE YXL_TEST1;

SQL> SPOOL OFF
SQL> show user
USER 为 "YXL"
SQL> @c:\drop_table.sql
SP2-0734: 未知的命令开头 "SQL> SELEC..." - 忽略了剩余的行。
SP2-0734: 未知的命令开头 "'DROPTABLE..." - 忽略了剩余的行。
表已删除。
表已删除。
表已删除。
表已删除。
SP2-0734: 未知的命令开头 "SQL> SPOOL..." - 忽略了剩余的行。

以上spool+@执行命令脚本可以写进SHELL中，轻松实现定时批量对数据库进程操作的任务。

注：大家可能注意到执行过程中有一点错误提示，虽然不影响最终的正确结果，但是却甚为不美观。原因在于
SQL> edit c:\drop_table.sql
会发现drop_table.sql中的记录如下：

SQL> SELECT 'DROP TABLE '|| table_name ||';' dba_tables where wner='YXL';

'DROPTABLE'||TABLE_NAME||';'                                                   
--------------------------------------------------------------------------------
DROP TABLE TEST_YXL;                                                            
DROP TABLE TEST_YXL1;                                                           
DROP TABLE YXL_TEST;                                                            
DROP TABLE YXL_TEST1;                                                           

SQL> SPOOL OFF

其实实际上，我们只要有如下的结果就足矣了：
DROP TABLE TEST_YXL;                                                            
DROP TABLE TEST_YXL1;                                                           
DROP TABLE YXL_TEST;                                                            
DROP TABLE YXL_TEST1;
现在这个是批量删除命令，出错无非就不执行，还好，要是进行与sqlldr进程结合的操作，那出错sqlldr就
会出错了。咋办呢，继续看下面

  10.4 其他相关参数实验

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading off
set timing off
set verify off
set numwidth 38
SPOOL c:\delete_table.sql
SELECT 'DELETE '|| table_name ||';' from dba_tables where wner='LJB' and rownum<=3;
SPOOL OFF

查看
SQL> SELECT 'DELETE '|| table_name ||';' from dba_tables where wner='LJB' and rownum<=3;
DELETE TEST_ABCD;
DELETE TEST222;
DELETE TEST111;
SQL> SPOOL OFF

看上去好像还有点小问题，咋有这个SQL>的东东头尾两个啊，呵呵，用上sqlplus -s 参数进sqlplus后，再操作就消失了，最终结果就变成是很清爽的如下了
DELETE TEST_ABCD;
DELETE TEST222;
DELETE TEST111;




参数重点说明几个，其他就不实验了，一一罗列出简单在附录中说明

  1、sqlplus 的s 参数（大小写不区分）
  大家平时可能没有注意到使用这个参数，这个参数是干什么用的呢，原来-s的这个含义表示silent，将交互动作的提示符给隐藏了。
下面做个实验一看便知。

C:\Documents and Settings\fujitsu>sqlplus -s ljb/ljb
select * from  ljb_test;

       ID1        ID2
---------- ----------
         1          3
exit

C:\Documents and Settings\fujitsu>sqlplus ljb/ljb
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 15 14:03:26 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from  ljb_test;
       ID1        ID2
---------- ----------
         1          3

2、set heading off可以将列名去掉
C:\Documents and Settings\fujitsu>sqlplus -s ljb/ljb
set heading off
select * from ljb_test;

         1          3

具体其他详细参数设置说明，这里我就不一一实验了，理解上面脚本的其他参数设置可以去相关文档搜索。此外还有其他各类参数，有兴趣也可一并研究。