Select username from t_userinfo

where (phonenumber like ‘135%8888’

or phonenumber like ‘136%8888’

or phonenumber like ‘137%8888’

or phonenumber like ‘138%8888’)

and length(phonenumber) = 13;

那时就会很羡慕java程序员可以使用一个正则表达式轻松搞定。10g中，再也不需要这么复杂了， oracle也提供了几个正则表达式函数... ...

全文链接：http://www.hellodba.com/Doc/oracle10g_regular_expression.htm

Oracle 9i之前，提供了一个命令’warp’来加密代码。但它是命令行方式的，是针对脚本的。10g中，Oracle包DBMS_DDL提供了一个新的函数CREATE_WRAPPED来实现对代码的混淆... ...

全文链接：http://www.hellodba.com/Doc/oracle10g_code_wrapped.htm

Oracle10g中提供了一个十分方便开发人员的新特性——选择性编译。即可以通过条件，只编译PL/SQL中的部分代码。如果你对C++很熟悉，那你会对这个特性感觉非常亲切，因为它和C++中的条件宏十分相似。在9i的时候，开发人员将自己的调试信息加入到程序中，往往release之后都没有删掉，这些信息轻则影响可读性，严重的话会影响系统性能。有了这个特性后，这个问题就可以解决了... ...

全文链接：http://www.hellodba.com/Doc/oracle10g_chooseful_compile.htm

我自己机器上有很多很有价值的文档（有自己写的，也有下载的），但是总是有时候想找一篇文档，当时不记得放在哪了。

机器上正好装了Ultra Search，我就在我的文档目录上创建一个搜索系统，以后找起文档来方便多了。很酷吧！（当然google desktop也是一个不错的工具，但是，它有一定局限性，比如不能针对某个目录搜索，没有高级搜索。关键是顺便还可以配置一下Ultra Search，增加经验值J）... ...

全文链接：http://www.hellodba.com/Doc/Oracle_Ultra_Search_desktop_Search.htm

自己写的一个Oracle压力测试工具。支持windows, unix和linux。但是必须要有java环境（java 1.4.1.03以上）。

没有界面。但是压强不小，我的必备工具。

当然肯定还有问题，发现了bug或者有什么好建议就发送到我的邮箱吧：fuyuncat@gmail.com

下载地址：http://www.hellodba.com/Download/SQLStress.html

更新日期：2005.12.23

最新版本：v1.12

更新内容：制作成exe文件、在unix下可能会产生严重错误，暂时不支持

使用sqlplus时要注意的几个问题

by fuyuncat

1.1 ‘&’:

The first thing we should be care when using SQL*Plus or PL/SQL Developer is the start character of substitution variable reference, which default is ‘&’. Its effective scope is whole PL/SQL block, including your defined variable in the PL/SQL block or the comments of PL/SQL block. If the string following ‘&’ is a valid naming-string of PL/SQL, it will be treated as a input variable. For example:

SQL> declare

2 v_aaa varchar2(10):= 'aaa&b.bb'; --this is the comment, but include aaa&ccc also.

3 begin

4 dbms_output.put_line(v_aaa);

5 end;

6 /

Enter value for b:

Enter value for ccc:

old 2: v_aaa varchar2(10):= 'aaa&b.bb'; --this is the comment, but include aaa

&ccc also.

new 2: v_aaa varchar2(10):= 'aaabb'; --this is the comment, but include aaa al

so.

aaabb

PL/SQL procedure successfully completed.

We have many methods to avoid it. Such as using ’set’ command, convert ‘&’ to ASCII code. We recommend just split it isolated, or quote it by ‘”’:

SQL> declare

2 v_aaa varchar2(10):= 'aaa'||'&'||'b.bb';--this is the comment, but include aaa"&"ccc also.

3 begin

4 dbms_output.put_line(v_aaa);

5 end;

6 /

aaa&b.bb

PL/SQL procedure successfully completed.

1.2 ‘/’:

Another thing we should remember when using PL/SQL Developer or SQL*Plus is we should add a ‘/’ at the end of each PL/SQL scripts. Because the character means ask oracle to execute the PL/SQL block. If missing it, SQL*Plus will never execute the script and it will not be ended:

SQL> declare

2 v_aaa varchar2(10):= 'aaa';

3 begin

4 dbms_output.put_line(v_aaa);

5 end;

6

7

8

… …

1.3 Set command:

The ‘set’ command is using to set the environment of SQL*Plus and PL/SQL Developer. It can help us to format the select result. But be careful when using it. Because it will impact the following scripts. For example, when you set scan off in the first script, then you want accept input variable. But the ‘&’ will not effect at that time when executing the second script.

To recover all variables to be default value, we suggest to exit current session and recreate another one.

1.4 Spool

Spool is a very useful command. It can let you get the result report of a set of commands, including SQL commands and SQL*Plus commands. You can use ‘set’ or other commands to customize your report. However, if you use SQL*Plus to get the report, you will not get the result you want if you does not execute the commands in a scripts file. For example, if you execute these commands in SQL*Plus dierectly:

08:58:36 SQL> set echo off

08:58:44 SQL> spool c:test.txt

08:58:51 SQL> select sysdate from dual;

SYSDATE

---------

06-DEC-05

08:59:00 SQL> spool off

You wanna get this result:

SYSDATE

---------

06-DEC-05

But if you open the file ‘c:test.txt’, you will find the content of it is:

08:58:51 SQL> select sysdate from dual;

SYSDATE

---------

06-DEC-05

08:59:00 SQL> spool off

You must write those commands in a script file, e.g. ‘c:test.sql’, and execute it

09:06:07 SQL> @c:test.sql

SYSDATE

---------

06-DEC-05

And you can find its result is what you want.

For PL/SQL developer, it is another fact: you can get the same result whenever execute these commands in command window directly or execute the script file including them.

1.5 Comments in script

When we add comments in a script file, we should very careful. Comments will lead to unexpected result in some special conditions.

1.5.1 An English letter follows closely in comment symbol '/*'

In SQL*Plus, it will lead the SQL statement before the comment to be executed twice. For example, the script file “test.sql” includes follow SQL statements:

select 'aaa' from dual;

/*abc*/

select 'bbb' from dual;

We just want the statement ‘select 'aaa' from dual;’ to be executed once. However, we will get another result:

SQL> @c:test.sql

'AA

---

aaa

'AA

---

aaa

'BB

---

bbb

As you can see, the statement were executed twice.

This bug would not impact PL/SQL developer.

And this is the correct script (there is a blank char between ‘/*’ and ‘abc’):

select 'aaa' from dual;

/* abc */

select 'bbb' from dual;

The right result:

SQL> @c:test.sql

'AA

---

aaa

'BB

---

bbb

1.5.2 Commends follow a SQL statement

If a comment follows a SQL statement (the comments including ‘--’ mode and ‘/**/’ mode), this statement will be treated as comments, and will not be executed. For example, here a ‘test.sql’ script:

select 'aaa' from dual; --aaa

select 'bbb' from dual;

You will get this result when execute it:

SQL> @c:test.sql

'BB

---

bbb

Noted, the first SQL statement was not executed, because it was followed by a comments with ‘--’.

And, if the comment was placed in another line follow closely the SQL statement, it will also raise this bug:

select 'aaa' from dual;

--aaa

select 'bbb' from dual;

Execute the script:

SQL> @c:test.sql

'BB

---

bbb

This bug impact both SQL*Plus and PL/SQL developer.

And this is the correct script:

-- aaa

select 'aaa' from dual;

select 'bbb' from dual;

The right result:

SQL> @c:test.sql

'AA

---

aaa

'BB

---

bbb

1.5.3 Commends following a SQL statement, followed by another SQL statement closely

If a commend follows a SQL statement, and it was followed by another SQL statement closely. It will lead to a execution error. For example, this is the script:

select 'aaa' from dual; --aaa

select 'bbb' from dual;

Execute the script:

SQL> @c:test.sql

select 'aaa' from dual; --aaa

*

ERROR at line 1:

ORA-00911: invalid character

This bug will ignore the comments between them. For example, this is the script:

select 'aaa' from dual;--aaa

/* abc */

select 'bbb' from dual;

It will still raise the error:

SQL> @c:test.sql

select 'aaa' from dual;--aaa

*

ERROR at line 1:

ORA-00911: invalid character

This bug impact both SQL*Plus and PL/SQL developer.

And this is the correct script:

-- aaa

select 'aaa' from dual;

select 'bbb' from dual;

The right result:

SQL> @c:test.sql

'AA

---

aaa

'BB

---

bbb

hash join(HJ)是一种用于equi-join（而anti-join就是使用NOT IN时的join）的技术。在Oracle中，它是从7.3开始引入的，以代替sort-merge和nested-loop join方式，提高效率。在CBO（hash join只有在CBO才可能被使用到）模式下，优化器计算代价时，资源足够时，首先会考虑hash join... ...

全文链接：http://www.hellodba.com/Doc/Oracle_Hash_Join.htm

安装配置Oralce Ultra Search的全过程... ...

全文链接：http://www.hellodba.com/Doc/Oracle_Ultra_Search.htm

SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具，它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用... ...

全文链接：http://www.hellodba.com/Doc/how_to_oracle10_sta(1).htm

SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具，它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用... ...

全文链接：http://www.hellodba.com/Doc/how_to_oracle10_sta(1).htm

SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具，它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用... ...

全文链接：http://www.hellodba.com/Doc/how_to_oracle10_sta(1).htm

在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快的定位性能问题。但这些工具都只给出一些统计数据，然后再由DBA们根据自己的经验进行优化...

全文链接：

http://www.hellodba.com/Doc/oracle10g_addm(1).htm

在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快的定位性能问题。但这些工具都只给出一些统计数据，然后再由DBA们根据自己的经验进行优化...

全文链接：

http://www.hellodba.com/Doc/oracle10g_addm(1).htm

在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快的定位性能问题。但这些工具都只给出一些统计数据，然后再由DBA们根据自己的经验进行优化...

全文链接：

http://www.hellodba.com/Doc/oracle10g_addm(1).htm

在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快的定位性能问题。但这些工具都只给出一些统计数据，然后再由DBA们根据自己的经验进行优化...

全文链接：

http://www.hellodba.com/Doc/oracle10g_addm(1).htm

本文介绍了在RAC模式中，如何配置客户端以及服务端，以实现负载均衡（Load Balance）和failover。

全文链接：http://www.hellodba.com/Doc/oracle_load_balance_and_fail_over.htm

SGA_MAX_SIZE、_ksm_granule_size和db_cache_size之间的关系

_ksm_granule_size为最低粒度大小。如果SGA_MAX_SIZE小于128M，则_ksm_granule_size为4M；如果SGA_MAX_SIZE大于等于128M，则_ksm_granule_size为8M

_ksm_granule_size决定了其他一些参数的大小。

假如SGA_MAX_SIZE为64M，则_ksm_granule_size为4M，那么如果设定db_cache_size为9M，则实际db_cache_size会被设置为12M，因为最低粒度为4M；

假如SGA_MAX_SIZE为128M，则_ksm_granule_size为8M，那么如果设定db_cache_size为9M，则实际db_cache_size会被设置为16M，因为最低粒度为8M；

数据段压缩，又称为“块级压缩”，是Oracle 9.2 中出现的新特性。他对于数据仓库和那些只需要读的大表来说很有用。数据段压缩可以使存储空间减少，又可以提高查询速度（需要读入的数据块更少）。本文分析了数据段压缩的使用方法已经它的内部存储方式... ...

全文链接：http://www.hellodba.com/Doc/data_compress.htm

Oracle 10G OEM 无法正常启动的解决办法（重建OEM）

by fuyuncat

现在的操作系统不支持中文，原先存的中文信息都丢了:(

SQL> connect / as sysdba

SQL> drop role MGMT_USER;

SQL> drop user mgmt_view cascade;

SQL> drop public synonym mgmt_target_blackouts;

SQL> drop public synonym setemviewusercontext;

SQL> drop user sysman cascade;

delte directory ORACLE_HOMEoc4jj2eeOC4J_DBConsole_HOSTNAME_SID

C:> emca -x <SID>

C:> emca

XML DB是Oracle 9.2中出现的新特性，这里介绍了如何在Oracle中存储、控制、使用XML...

全文链接：http://www.hellodba.com/Doc/oracle_xml_db.htm

ORACLE各个版本的新特性一览表

by fuyuncat

Oracle 10g Release 2 (10.2.0)

• SQL Access Advisor
• Segment Advisor
• Database Managed Undo Retention Time Period
• Database Managed Multiblock Read Count
• Size-Based Tablespace Freespace Alerts
• Enterprise Manager Direct SGA Attach
• Dropping Empty Datafiles
• V$SQLSTATS Performance View

Oracle 10g Release 1 (10.1.0)

• Simplified Oracle10g installation
• Enhanced Enterprise Manager
• Introducing DataPump - The load/unload utility
• Automatic Database Diagnostic Monitor
• Automatic Shared Memory Management
• Automatic Optimizer Statistics Gathering
• Automatic Undo Tuning
• Automatic Workload Repository
• Automatic Storage Management
• Automatic Maintenance Tasks
• SQL Tuning Advisor
• SQL Access Advisor
• Undo Advisor
• Redo Logfile Size Advisor
• Bigfile Tablespaces
• New Flashback Features
• The SYSAUX Tablespace
• Direct Upgrade to 10g
• UNIX Style Regular Expressions in SQL
• HTML DB
• New (bunch of) Wait Events
• New Initialization Parameters
• Rename Tablespace
• Flush buffer cache
• Temporary Tablespace Groups
• Default Permanent Tablespace
• Segment Shrink
• Skip Unusable Indexes
• Drop Database
• Server Alerts
• Transport tablespace across platforms

Oracle 9i Release 2 (9.2.0)

• Oracle Streams (for data movement - can potentially replace Oracle Advance Replication and Standby Databases).
• XML DB (Oracle is now a standards compliant XML database)
• Data segment compression (compress keys in tables - only when loading data)
• Cluster file system for Windows and Linux (raw devices are no longer required).
• Create logical standby databases with Data Guard
• Java 1.3 used inside the database

Oracle 9i Release 1 (9.0.1)

• Traditional rollback segments (RBS) are still available, but can be replaced with automated System Managed Undo (SMU). Using SMU, Oracle will create it's own "Rollback Segments" and size them automatically without any DBA involvement.
• Flashback query (dbms_flashback.enable) - one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.
• Use Oracle Ultra Search for searching databases, file systems, etc. The UltraSearch crawler fetch data and hand it to Oracle Text to be indexed.
• Oracle Nameserver is still available, but deprecate in favor of LDAP Naming (using the Oracle Internet Directory Server). A nameserver proxy is provided for backwards compatibility as pre-8i client cannot resolve names from an LDAP server.
• Oracle Parallel Server's (OPS) scalebility was improved - now called Real Application Clusters (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications doesn't need to be cluster aware anymore.
• The Oracle Standby DB feature renamed to Oracle Data Guard. New Logical Standby databases replay SQL on standby site allowing the database to be used for normal read write operations. The Data Guard Broker allows single step fail-over when disaster strikes.
• Scrolling cursor support. Oracle9i allows fetching backwards in a result set.
• Dynamic Memory Management - Buffer Pools and shared pool can be resized on-the-fly. This eliminates the need to restart the database each time parameter changes were made.
• On-line table and index reorganization.
• VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
• Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI's, etc. XML integrated with AQ.
• Cost Based Optimizer now also consider memory and CPU, not only disk access cost as before.
• PL/SQL programs can be natively compiled to binaries.
• Deep data protection - fine grained security and auditing. Put security on DB level. SQL access do not mean unrestricted access.
• Resumable backups and statements - suspend statement instead of rolling back immediately.
• List Partitioning - partitioning on a list of values.
• ETL (eXtract, transformation, load) Operations - with external tables and pipelining.
• OLAP - Express functionality included in the DB.
• Data Mining - Oracle Darwin's features included in the DB.

Oracle 8i (8.1.7)

• Static HTTP server included (Apache)
• JVM Accelerator to improve performance of Java code
• Java Server Pages (JSP) engine
• MemStat - A new utility for analyzing Java Memory footprints
• OIS - Oracle Intergration Server introduced.
• PLSQL Gateway introduced for deploying PL/SQL based solutions on the Web
• Enterprise Manager Enhancements - including new HTML based reporting and Advanced Replication functionality included.
• New Database Character Ser Migration utilility included.

Oracle 8i (8.1.6)

• PL/SQL Server Pages (PSP's)
• DBA Studio Introduced
• Statspack
• New SQL Functions (rank, moving average)
• ALTER FREELISTS command (previously done by DROP/CREATE TABLE)
• Checksums always on for SYSTEM tablespace allowing many possible corruptions to be fixed before writing to disk
• XML Parser for Java
• New PLSQL encrypt/decrypt package introduced
• User and Schemas sepapated
• Numerous Performance Enhancements

Oracle 8i (8.1.5)

• Fast Start recovery - Checkpoint rate auto-adjusted to meet roll forward criteria
• Reorganize indexes/index only tables which users accessing data - Online index rebuilds
• Log Miner introduced - Allows on-line or archived redo logs to be viewed via SQL
• OPS Cache Fusion introduced avoiding disk I/O during cross-node communication
• Advanced Queueing improvements (security, performance, OO4O support
• User Security Improvements - more centralisation, single enterprise user, users/roles across multiple databases.
• Virtual private database
• JAVA stored procedures (Oracle Java VM)
• Oracle iFS
• Resource Management using proirities - resource classes
• Hash and Composite partitioned table types
• SQL*Loader direct load API
• Copy optimizer statistics across databases to ensure same access paths across different environments.
• Stanby Database - Auto shipping and application of redo logs. Read Only queries on standby database allowed.
• Enterprise Manager v2 delivered
• NLS - Euro Symbol supported
• Analyze tables in parallel
• Temporary tables supported.
• Net8 support for SSL, HTTP, HOP protocols
• Transportable tablespaces between databases
• Locally managed tablespaces - automatic sizing of extents, elimination of tablespace fragmentation, tablespace information managed in tablespace (i.e noved from data dictionary) improving tablespace reliability
• Drop Column on table (Finally !!!!!)
• DBMS_DEBUG PL/SQL package, DBMS_SQL replaced by new EXECUTE IMMEDIATE statement
• Progress Monitor to track long running DML,DDL
• Functional Indexes - NLS, case insensitive, descending

Oracle 8.0

• Object Relational database
• Object Types (not just date, character, number as in v7
• SQL3 standard
• Call external procedures
• LOB >1 per table
• Partitioned Tables and Indexes
• export/import individual partitions
• partitions in multiple tablespaces
• online/offline, backup/recover individual partitions
• merge/balance partitions
• Advanced Queuing for message handling
• Many performance improvements to SQL/PLSQL/OCI making more efficient use of CPU/Memory. V7 limits extended (e.g. 1000 columns/table, 4000 bytes VARCHAR2)
• Parallel DML statements
• Connection Pooling ( uses the physical connection for idle users and transparently re-establishes the connection when needed ) to support more concurrent users.
• Improved "STAR" Query optimizer
• Integrated Distributed Lock Manager in Oracle PS (as opposed to Operating system DLM in v7).
• Performance improvements in OPS - global V$ views introduced across all instances, transparent failover to a new node
• Data Cartridges introduced on database (e.g. image, video, context, time, spatial)
• Backup/Recovery improvements - Tablespace point in time recovery, incremental backups, parallel backup/recovery. Recovery manager introduced
• Security Server introduced for central user administration. User password expiry, password profiles, allow custom password scheme. Privileged database links (no need for password to be stored)
• Fast Refresh for complex snapshots, parallel replication, PL/SQL replication code moved in to Oracle kernel. Replication manager introduced.
• Index Organized tables
• Deferred integrity constraint checking (deferred until end of transaction instead of end of statement).
• SQL*Net replaced by Net8
• Reverse Key indexes
• Any VIEW updateable
• New ROWID format

Oracle 7.3

• Partitioned Views
• Bitmapped Indexes
• Asynchronous read ahead for table scans
• Standby Database
• Deferred transaction recovery on instance startup
• Updatable Join Views (with restrictions)
• SQLDBA no longer shipped.
• Index rebuilds
• db_verify introduced
• Context Option
• Spatial Data Option
• Tablespaces changes - Coalesce, Temporary Permanent,
• Trigger compilation, debug
• Unlimited extents on STORAGE clause.
• Some init.ora parameters modifiable - TIMED_STATISTICS
• HASH Joins, Antijoins
• Histograms
• Dependencies
• Oracle Trace
• Advanced Replication Object Groups
• PL/SQL - UTL_FILE

Oracle 7.2

• Resizable, autoextend data files
• Shrink Rollback Segments manually
• Create table, index UNRECOVERABLE
• Subquery in FROM clause
• PL/SQL wrapper
• PL/SQL Cursor variables
• Checksums - DB_BLOCK_CHECKSUM, LOG_BLOCK_CHECKSUM
• Parallel create table
• Job Queues - DBMS_JOB
• DBMS_SPACE
• DBMS Application Info
• Sorting Improvements - SORT_DIRECT_WRITES

Oracle 7.1

• ANSI/ISO SQL92 Entry Level
• Advanced Replication - Symmetric Data replication
• Snapshot Refresh Groups
• Parallel Recovery
• Dynamic SQL - DBMS_SQL
• Parallel Query Options - query, index creation, data loading
• Server Manager introduced
• Read Only tablespaces

Oracle 7.0

• Database Integrity Constraints (primary, foreign keys, check constraints, default values)
• Stored procedures and functions, procedure packages
• Database Triggers
• View compilation
• User defined SQL functions
• Role based security
• Multiple Redo members - mirrored online redo log files
• Resource Limits - Profiles
• Much enhanced Auditing
• Enhanced Distributed database functionality - INSERTS, UPDATES,DELETES, 2PC
• Incomplete database recovery (e.g SCN)
• Cost based optimiser
• TRUNCATE tables
• Datatype changes (i.e VARCHAR2 CHAR, VARCHAR)
• SQL*Net v2, MTS
• Checkpoint process
• Data replication - Snapshots

SQL*Load技巧三则

by fuyuncat

1.1 SQL*Load过滤数据

LOAD DATA

INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'

APPEND

INTO TABLE my_selective_table

WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '20031217'

(

region CONSTANT '31',

service_key POSITION(01:11) INTEGER EXTERNAL,

call_b_no POSITION(12:29) CHAR

)

其中，01表示一个字符，30:37表示从30个字符到37个字符。

注意：在When条件中，不支持OR。可以采用以下代替方法：

LOAD DATA

INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'

APPEND

INTO TABLE my_selective_table

WHEN (01) <> 'H' and (01) <> 'T'

(

region CONSTANT '31',

service_key POSITION(01:11) INTEGER EXTERNAL,

call_b_no POSITION(12:29) CHAR

)

INTO TABLE my_selective_table

WHEN (30:37) = '20031217'

(

region CONSTANT '31',

service_key POSITION(01:11) INTEGER EXTERNAL,

call_b_no POSITION(12:29) CHAR

)

1.2 SQL*Load时过滤字段

LOAD DATA

TRUNCATE INTO TABLE T1

FIELDS TERMINATED BY ','

( field1,

field2 FILLER,

field3

)

1.3 SQL*Load导入BLOB和CLOB字段

LOAD DATA

INFILE *

INTO TABLE image_table

REPLACE

FIELDS TERMINATED BY ','

(

image_id INTEGER(5),

file_name CHAR(30),

image_data LOBFILE (file_name) TERMINATED BY EOF

)

BEGINDATA

001,image1.gif

002,image2.jpg

彻底将一个数据文件从表空间中删除

by fuyuncat

首先说明一下alter database datafile ... offline drop：

在非归档模式下，可以使用... offline drop，在归档模式下，使用... offline. 这个命令的意思并不是从表空间将这个数据文件彻底删除，而是说将这个数据文件“冻结”住：它的scn不在变化，不会再向它写入任何数据；但是原有存在这个文件中对象和数据还可以继续使用。因此在使用这个命令后，通过修改控制文件将它删除，数据库在启动时就会报文件丢失的错误，并且会在数据字典中产生一个MISGXXXXX的纪录。因此，以一个数据文件是无法被直接从数据库中删除掉的。

如果需要减少表空间的数据文件，可以采用以下办法：

export除表空间的内容：exp ... tablespaces=xxx;

使用include content子句删除表空间：drop tablespace xxx include content;

重见表空间，使之不包括需要去除掉的数据文件；

import表空间的所有数据对象。

使用shutdown immediate无法关闭数据库（长时间无反应）情况下，如何关闭数据库

by fuyuncat

alter system checkpoint;

shutdown abort

startup restrict

利用RMAN克隆数据库

by fuyuncat

1、 在目标机器上创建一个新的init.ora文件，注意修改其中SID、DBNAMES、DUMP_DIR等参数

2、 在目标机器上用orapwd命令创建一个新的password文件

3、 在目标机器上设者相关的环境变量(ORACLE_SID等)

4、 在目标机器上启动sqlplus，执行startup nomount

5、 执行以下RMAN脚本：

connect target sys/secure@origdb --源DB

connect catalog rman/rman@catdb --源CATALOG

connect auxiliary / --目标DB

run {

set newname for datafile 1 to '/ORADATA/system01.dbf';

set newname for datafile 2 to '/ORADATA/undotbs01.dbf';

set newname for datafile 3 to '/ORADATA/users01.dbf';

set newname for datafile 4 to '/ORADATA/indx01.dbf';

set newname for datafile 5 to '/ORADATA/example01.dbf';

allocate auxiliary channel dupdb1 type disk;

set until sequence 2 thread 1;

duplicate target database to dupdb

logfile

GROUP 1 ('/ORADATA/redo01.log') SIZE 200k REUSE,

GROUP 2 ('/ORADATA/redo02.log') SIZE 200k REUSE;

}

注意：如果目标DB的数据文件和源DB的相同，就没有必要执行set newname命令了。

我们通常知道的锁一般就是TM、TX等。但实际上Oracle有很多种类型的锁。当你的系统中发生很多enqueue等待事件时，如何定位是那种锁呢？这里介绍一种方法... ...

全文链接：http://www.hellodba.com/Doc/identify_lock.htm

如何将单实例模式改为RAC

by fuyuncat

1. Make a full database backup before you change anything.
2. Copy the existing $ORACLE_HOME/dbs/init<SID1>.ora to 
   $ORACLE_HOME/dbs/init<db_name>.ora.  Add the following parameters to 
   $ORACLE_HOME/dbs/init<db_name>.ora:
        *.cluster_database =  TRUE
        *.cluster_database_instances  = 2 
        *.undo_management=AUTO      (Add if you don't have it )
        <SID1>.undo_tablespace=undotbs  (undo tablespace which already exists)
        <SID1>.instance_name=RAC1
        <SID1>.instance_number=1
        <SID1>.thread=1
        <SID1>.local_listener=LISTENER_RAC1
   where LISTENER_RAC1 is an entry in the tnsnames.ora file like:
        LISTENER_RAC1 =
          (ADDRESS = (PROTOCOL = TCP)(HOST = <node1>)(PORT = 1521))
   Keep only one line in $ORACLE_HOME/dbs/init<SID1>.ora:
         ifile=$ORACLE_HOME/dbs/init<db_name>.ora
   You could also create a common spfile from this pfile and add a line 
   like spfile=$ORACLE_HOME/dbs/spfile<db_name>.ora in each init<SIDn>.ora 
   
3. Open your database and run $ORACLE_HOME/rdbms/admin/catclust.sql to create 
   cluster database specific views within the existing instance.
4. Recreate control file if you defined maxinstances to be 1 when you created 
   the single instance database. 
   To check your current setting of maxinstances, run the following command 
   while the database is mounted or open and connected as a user with DBA 
   privileges:  
       % sqlplus /nolog 
       SQL> connect / as sysdba 
       SQL> startup mount 
       SQL> alter database backup controlfile to trace;   
   The trace file is located in udump directory.  Check the maxinstance value 
   in the CREATE CONTROLFILE statement.  Please refer to Note 118931.1 
   Recreating the Controlfile in RAC and OPS  
5. Add instance specific parameters in the init<db_name>.ora for the second 
   instance on the second node and set appropriate values for it:
        *** Names may need to be modified
        <SID2>.instance_name=RAC2
        <SID2>.instance_number=2
        <SID2>.local_listener=LISTENER_RAC2
        <SID2>.thread=2
        <SID2>.undo_tablespace=UNDOTBS2
        <SID2>.cluster_database =  TRUE
        <SID2>.cluster_database_instances  = 2
   where LISTENER_RAC2 is an entry in the tnsnames.ora file like:
   LISTENER_RAC2 =
     (ADDRESS = (PROTOCOL = TCP)(HOST = <node2>)(PORT = 1521))
 
6. From the first instance, mount the database and run the following command:
        *** Path names, file names, and sizes will need to be modified
        alter database
        add logfile thread 2
          group 3 ('/dev/RAC/redo2_01_100.dbf') size 100M,
          group 4 ('/dev/RAC/redo2_02_100.dbf') size 100M;
        alter database enable public thread 2; 
       
7. Create a second Undo Tablespace from the existing instance:
        *** Path names, file names, and sizes will need to be modified
        CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 
        '/dev/RAC/undotbs_02_210.dbf' SIZE 200M ;
        
8. Set ORACLE_SID and ORACLE_HOME environment variables on the second node.


	   

 

在Oracle9i中，有一个新的特性：跳跃式索引(Skip Scan Index)。当表有一个复合索引，而在查询中有除了索引中第一列的其他列作为条件，并且优化器模式为CBO，这时候查询计划就有可能使用到SS。此外，还可以通过使用提示index_ss（CBO下）来强制使用SS ... ...

全文链接：http://www.hellodba.com/Doc/skiped_index.htm

如果某个用户不小心删除了一个十分重要的表，后果将非常严重。在9i中提供的闪回特性只能恢复DML语句造成的影响，而无法恢复DDL语句的影响。DBA只能通过重建一张表，然后从备份数据中导入。

利用Oracle 10G中的闪回表的特性，DBA可以轻松完成这项工作，并将影响降到最小。下面就举一个例子说明... ...

全文链接：http://www.hellodba.com/Doc/Oracle%2010G_flashback.htm

如果某个用户不小心删除了一个十分重要的表，后果将非常严重。在9i中提供的闪回特性只能恢复DML语句造成的影响，而无法恢复DDL语句的影响。DBA只能通过重建一张表，然后从备份数据中导入。

利用Oracle 10G中的闪回表的特性，DBA可以轻松完成这项工作，并将影响降到最小... ...

全文链接：http://www.hellodba.com/Doc/Oracle%2010G_flashback.htm

DBA们经常会遇到一个这样令人头疼的问题：不知道谁在Oracle上创建了一个用户，创建时，没有给这个用户指定默认表空间，所以这个用户就会采用默认的表空间——system表空间。导致系统表空间迅速被用户数据占满，直至宕机。

在10G中，DBA有办法避免这种问题了——在线指定系统默认表空间... ...

全文链接：http://www.hellodba.com/Doc/Oracle%2010G_tablespace_management.htm

在处理被ADDM捕捉的突发的性能问题中，10g等待接口提供非常有价值的数据用于诊断。作为一个DBA，你可能遇到过很多次用户抱怨“数据库非常慢了”。那么你是如何定位这种问题的呢？你第一步要做的肯定是查看是否有会话在等待数据库内部或外部的什么资源。

Oracle提供了一个简单但是又很有效的机制来获取这些信息：视图V$SESSION_WAIT。这一视图提供了各种信息以帮助诊断如一个会话正在等待和已经等待的事件、及等待了多少、等待时间多长。例如，如果会话正在等待事件“db file sequential read”，字段P1和P2就标识会话正在等待的数据块的file_id和block_id。

对于大多数等待事件来说，这个视图已经足够了。但由于以下两个原因，它很难成为一个强有力的优化工具... ...

全文链接：http://www.hellodba.com/Doc/10g_wait.htm

在10g将查询重写并且引进了新的强大的调优建议者使管理物化视图变得容易多了。

物化视图(Materialized Views MVs)，也被称为快照，现在已经被广泛应用了。MV将一个查询的结果存储在一个段中，并且当用户提交查询时返回查询结果，而不需要重新执行查询——如果查询会被执行多次（经常出现在数据仓库环境中），这就会非常有效。MV可以从基础表中完全刷新或通过使用快速刷新机制增量刷新。

全文链接：http://www.hellodba.com/Doc/Oracle%2010G_materialized_view.htm

当数据库发生了性能问题时，如何去定位？比较常用的方法是采用一个既定的模式：解决诸如“是不是同一问题的再现？”、“是否在某一特殊时间段发生？”、“两个问题之间是否存在联系？”等问题，这样通常能得到一个比较好的诊断结果。作为一个DBA，你可能使用一个第三方或者自己开发的工具来收集数据库运行期间的精细统计数据，并从中得到性能度量数据。你需要将这些发生问题时的度量数据与当前数据进行比较。重现以前的时间能使现在的问题变得明朗。因此，持续的收集相关统计数据对于性能分析来说十分重要。在某些情况下，在解决收集统计数据这方面的问题上有自己内置的工具——statspack。尽管在某些情况下的作用非常大，但它缺乏解决性能问题所必须的健壮性。提供了一个标志性的改进特性：自动工作量存储（Automatic Workload Repository AWR）。AWR是随着数据库一起被安装的，它不仅能收集统计数据，还能从统计数据中分析出度量数据... ...

全文链接：http://www.hellodba.com/Doc/10g_awr.htm

审计功能对于DBA来说，是一项非常好的对数据库发生结构性变化的监控工具。

在Oracle 10g中，审计功能得到了大大的提升，可以在非常细节的层次上捕捉用户的动作。它可以是手动审计、基于触发器审计... ...

全文链接：http://www.hellodba.com/Doc/10g_audit.htm

在Oracle 10G中，SQL Plus这一小小而又强大的DBA工具已经得到了显著的改进，包括十分有用的提示和高级文件操作... ...

全文链接：http://www.hellodba.com/Doc/10g_sqlplus.htm

RMAN增量备份方案、增量备份的离线恢复、恢复预览、从resetlogs中恢复、文件压

缩等被重新设计后变得更加强大了。

大多数人都赞同RMAN就是Oracle事实上的数据库备份工具。尽管早期版本的RMAN已经很强大，但是人们对它的期待还是有很多。很多DBA对于一些很希望有但实际上没有的特性很烦恼。很幸运，在10g中解决了很多问题并且增加了很多受期待的特性，下面就一起看一下。

全文链接：http://www.hellodba.com/Doc/10g_rman.htm

从最终权威那获得SQL调优的帮助：Oracle数据库本身！通过使用SQL profiles来确定查询行为、学习如何使用ADDM快速和轻松地解决普通的性能问题

作为一个高级DBA，你当然不愿总是被调优某条SQL语句这种杂事缠身。

在10g中，你有了自动数据库诊断监视器（Automatic Database Diagnostic Monitor ADDM），他是一个不知疲倦的收集数据库性能统计信息来定位性能瓶颈、分析SQL语句和不停的提供各种类型的建议以提高性能的机器DBA，它一般和其他“建议器”如SQL Tuning Advisor一起工作。在本文中，你将了解到它是如何工作的。

全文链接：http://www.hellodba.com/Doc/Oracle%2010G_addm_advisor.htm

从最终权威那获得SQL调优的帮助：Oracle数据库本身！通过使用SQL profiles来确定查询行为、学习如何使用ADDM快速和轻松地解决普通的性能问题

作为一个高级DBA，你当然不愿总是被调优某条SQL语句这种杂事缠身。

在10g中，你有了自动数据库诊断监视器（Automatic Database Diagnostic Monitor ADDM），他是一个不知疲倦的收集数据库性能统计信息来定位性能瓶颈、分析SQL语句和不停的提供各种类型的建议以提高性能的机器DBA，它一般和其他“建议器”如SQL Tuning Advisor一起工作。在本文中，你将了解到它是如何工作的。

全文链接：http://www.hellodba.com/Doc/Oracle%2010G_addm_advisor.htm

为了更好的查询一个树状结构的表，在Oracle的PL/SQL中提供乐一个诱人的特性——CONNECT BY子句。它大大的方便了我们查找树状表：遍历一棵树、寻找某个分支……，但还是存在一些不足。在Oracle 10G，就对这个特性做了增强 ......

全文链接：http://www.hellodba.com/Doc/10g_connect_by.htm

--By Fuyuncat

--+++++++++++++++++++++Ä¿±ê»úÆ÷
:+++++++++++++++++++++++++++++++++++++++++++++++++

--======================STREAMS SETUP =================================
--==Instance
Setup===============================^_^===================================
CONN sys/sys@web AS SYSDBA

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;
ALTER SYSTEM SET AQ_TM_PROCESSES=10;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
ALTER SYSTEM SET LOGMNR_MAX_PERSISTENT_SESSIONS=10 SCOPE=SPFILE;
ALTER SYSTEM SET OPEN_LINKS=6 SCOPE=SPFILE;

STARTUP FORCE;

--=======Stream Administrator Setup================--

CONN sys/sys@web AS SYSDBA

CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE ring QUOTA UNLIMITED ON ring;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, AQ_ADMINISTRATOR_ROLE TO
strmadmin;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin;
GRANT EXECUTE ON DBMS_RULE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

connect STRMADMIN/STRMADMIN
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
queue_name => 'STREAMS_QUEUE',
queue_user => 'STRMADMIN');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hw.t_userinfo',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ABC');
END;
/

BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'STRMADMIN_APPLY',
apply_user => 'hw');
END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'STRMADMIN_APPLY',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );
END;
/

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STRMADMIN_APPLY');
END;
/

--+++++++++++++++++++++Ô´»úÆ÷
:+++++++++++++++++++++++++++++++++++++++++++++++++

CONN sys/sys@abc AS SYSDBA

CREATE TABLESPACE logmnr_ts DATAFILE '/dev/vg_data/rreport_idx'
SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_ts');

ALTER TABLE hr.employee2 ADD SUPPLEMENTAL LOG GROUP log_group_emp_pk
(employee_id) ALWAYS;

--==Instance
Setup===============================^_^===================================
CONN sys/sys@abc AS SYSDBA

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;
ALTER SYSTEM SET AQ_TM_PROCESSES=10;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
ALTER SYSTEM SET LOGMNR_MAX_PERSISTENT_SESSIONS=10 SCOPE=SPFILE;
ALTER SYSTEM SET OPEN_LINKS=6 SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;

--=======Stream Administrator Setup================--

CONN sys/sys@abc AS SYSDBA

CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, AQ_ADMINISTRATOR_ROLE TO
strmadmin;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin;
GRANT EXECUTE ON DBMS_RULE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

connect STRMADMIN/STRMADMIN
CREATE DATABASE LINK ibmora connect to STRMADMIN identified by STRMADMIN
using 'ibmora';

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_QUEUE',
queue_table =>'STREAMS_QUEUE_TABLE',
queue_user => 'STRMADMIN');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hr.employee2',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ABC');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'hr.employee2',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@ibmora',
include_dml => true,
include_ddl => true,
source_database => 'ABC');
END;
/

!exp USERID=SYSTEM/MANAGER@abc TABLES=hr.employee2 FILE=tables.dmp
GRANTS=Y ROWS=Y LOG=exportTables.log OBJECT_CONSISTENT=Y INDEXES=Y
STATISTICS = NONE

!imp USERID=SYSTEM/MANAGER@ibmora FULL=Y CONSTRAINTS=Y FILE=tables.dmp
IGNORE=Y GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log
STREAMS_INSTANTIATION=Y

--+++++++++++++++++++++Ä¿±ê»úÆ÷
:+++++++++++++++++++++++++++++++++++++++++++++++++
connect STRMADMIN/STRMADMIN
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STRMADMIN_CAPTURE');
END;
/

Ô´½ÚµãÇå³ý£º

connect / as sysdba

@remove_streams92.plb

exec remove_streams92.remove_streams('strmadmin')

ALTER TABLE hr.employee2 DROP SUPPLEMENTAL LOG GROUP log_group_emp_pk;

--optional
connect STRMADMIN/STRMADMIN
DROP DATABASE LINK ibmora ;

conn / as sysdba
drop user strmadmin cascade;

Ä¿±ê½ÚµãÇå³ý£º

CONNECT sys/sys@web as sysdba
connect / as sysdba

SQL>@remove_streams92.plb

exec remove_streams92.remove_streams('strmadmin')

ALTER TABLE hr.employee2 DROP SUPPLEMENTAL LOG GROUP log_group_emp_pk;

--optional
conn / as sysdba
drop user strmadmin cascade;

ORACLE数据库是一个复杂的、高度可调的软件产品。对于Oracle的系统参数是高度可调的，对于单个SQL语句也能通过使用提示来调整其优化规则...

全文链接：http://www.hellodba.com/Doc/Oracle_hint(1).htm

XML DB是Oracle 9.2中出现的新特性，这里介绍了如何在Oracle中存储、控制、使用XML...

全文链接：http://www.hellodba.com/Doc/oracle_xml_db.htm

在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快的定位性能问题。但这些工具都只给出一些统计数据，然后再由DBA们根据自己的经验进行优化...

全文链接：

http://www.hellodba.com/Doc/oracle10g_addm(1).htm

在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快的定位性能问题。但这些工具都只给出一些统计数据，然后再由DBA们根据自己的经验进行优化...

全文链接：

http://www.hellodba.com/Doc/oracle10g_addm(1).htm