ITPub博客

Reset user's password in 11g[@more@]SQL> create user lyon identified by test; User created. SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS ,PROFILE from dba_users where USERNAME='LYON'; USERNAME PASSWORD ------------------------------ ------------------------------ ACCOUNT_STATUS PROFILE -------------------------------- ------------------------------ LYON OPEN DEFAULT SQL> select password,spare4 from user$ where name='AMIT'; no rows selected SQL> select password,spare4 from user$ w

Lock mode in which the process requests the lock:¦ 0 - none¦ 1 - null (NULL)¦ 2 - row-S (SS)¦ 3 - row-X (SX)¦ 4 - Table share (S)¦ 5 - Table Share and Row-X (SSX)¦ 6 - Table exclusive (X)Conclusion in 10gR2.1> create index az_obj_id. (4-TM)2>alter index az_obj_id rebuild online; (2-TM and 4-TM)3> alter index az_obj_id rebuild; (4-TM and 3-TM)4>alter table az move tablespace test; (6-TM)5>alter table az shrink space; (3-TM and 6-TM

For these examples, assume the following statements have been issued:[@more@]CREATE ROLE security_admin IDENTIFIED BY honcho; GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE, CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY, AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER TO security_admin WITH ADMIN OPTION; GRANT SELECT, DELETE ON SYS.AUD$ TO security_admin; GRANT security_admin, CREATE SESSION TO swilliams; GRANT security_admin TO system_administrator; GRANT CREATE SESSION

What is Partition Pruning?Some tables are so large that a Full Table Scan is unthinkable. If these tables are not partitioned, they should be.[@more@]The most common (by far) form of partitioning is Range Partitioning on a DATE column. For example, a table may be partitioned on TXN_DATE, with a separate partition for each year, month, or even day (I have seen a multi-terabyte database where daily partitions were the norm!).If Oracle can use the WHERE predicates to eliminate some partitions from

Use the following approach to create tables with constraints and indexes:[@more@]1. should be created enabled and validated. All other constraints (Create the tables with the constraints. NOT NULL constraints can be unnamed andCHECK, UNIQUE,PRIMARY KEY, and FOREIGN KEY) should be named and created disabled.See Also: information about the Oracle Database SQL Language Reference for moreCREATE INDEX and ALTER INDEXstatements, as well as restrictions on rebuilding indexesSee Also: Database Administr

The same configuration as the case one.[@more@]1 1> Take a full backup at Standby site[oracle@RAC02 lyon_Dont_touch]$ rman target / catalog rman/cat@catadbRecovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 20 11:07:37 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: O01DMS0A (DBID=231561342, not open)connected to recovery catalog databaseRMAN> backup database;Starting backup at 20-JUN-08using channel ORA_DISK_1channel ORA_DISK_1: starting fu

Advanced Backup & Recovery For Oracle Dataguard based on The Reference Using Recovery Manager with Oracle Data Guard in Oracle Database 10gIn a standby environment, backing up data files and archived redo log files taken on the primary or standby system are usable on either system for recovery. Although some files such as the control file and SPFILE must be backed up on the primary database, the process of backing up data files and archived redo log files can be off-loaded to the standby sys

如果我们的备份策略是: 周日0级 周一，二 1 differential 周三 1 cumulative周四,五 1 differential 周六 1 cumulative我们可以通过查询数据字典试图v$backup_datafile了解到每天数据块修改的情况.[@more@]v$backup_datafile : V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.字段解释FILE# : Datafile number; set to 0 for control file DATAFILE_BLOCKS : Size of the datafile in blocks at backup time. This value is also the number of bl

可以被lock manager并行锁的enqueue resources的最大数量由ENQUEUE_RESOURCES初始化参数控制.该参数默认值通常来讲已经足够,如果在应用程序中使用并行DML操作,可以考虑增加ENQUEUE_RESOURCES的值.[@more@]关于ENQUEUE_RESOURCES [10..65535] At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20. For thre

当处理enqueue等待时,熟记这些要点:1 Enqueues 是应用到数据库对象的locks.与latch(应用于SGA内存)不同.2 Enqueues 是由应用程序发起,具有事务性.(具体参见下文)3 Oracle session 正在等候获取一个特定的enqueue. enqueue的名字和mode被记录在P1参数里.针对不同类型的enqueue竞争应采用不同的方式解决.4 一直到Oracle 9i,enqueue wait event 代表所有enqueue waits；从Oracle 10g开始enqueue waits被分类定义(下文详细介绍).[@more@]What Is an Enqueue?一个enqueue是什么由具体环境决定. 如果它被看成是动词,则表示将一个lock请求置于一个

Oracle常见事件如下：10000 control file debug event, name 'control_file'10001 control file crash event110002 control file crash event210003 control file crash event310004 control file crash event410005 trace latch operations for debugging[@more@] 10006 testing - block recovery forced10007 log switch debug crash after new log select, thread %s10008 log switch debug crash after new log header write, thread %s10009 log switch debug crash after old log header write, thread %s10010 Begin Transaction10

原文发表在 http://www.itpub.net/showthread.php?s=&threadid=666743上周五在RAC环境下做数据迁移前的imp测试6进程并行做impimp log中报错:IMP-00003: ORACLE error 604 encounteredORA-00604: error occurred at recursive SQL level 1ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","TAB$","KGLS heap","KGLS MEM BLOCK")IMP-00058: ORACLE error 604 encounteredORA-00604: error occurred at recursive SQL level 1ORA-04031: unable to allocate 2216 byt

机器配置:IBM P690 6U12G数据库版本: Oracle 9.2.0.6需求: 根据主外键关系迁移tb1446表内数据至tb1446_copy表tb1446内有20个Hash分区,根据分区结合bulk collect技术,一次提交25000条记录(需要测试后得出一次提交的记录数)，使得整个单表迁移过程耗时最短.[@more@]SQL> select count(*) from ecifupdate.tb1446; COUNT(*)---------- 7087611SQL> select count(*) from ecifupdate.tb1560; COUNT(*)---------- 74735643编写bulk collect方式的存储过程.migration_tb1446_part_00migration_tb1446_part_01migration_tb1446_part_0

在日常工作中，经常需要了解一个命令发出去，Oracle执行的进度，这里提供了一个很实用的脚本,专门用来解决这个问题.监控数据库执行超过超过6s的操作 包括:1> 监控索引创建过程2> 监控物化视图刷新3> 获取当前数据库之行超过6s的SQL原文发表在:http://www.itpub.net/633751.html[@more@]例子:session A中执行一个超过6s的查询SQL>select l.owner,l.object_name,t.id from large_table l,tiny_table t where t.owner=l.owner;181507036 rows selected.Execution Plan---------------------------------------------------------

Oracle的Nologging何时生效 与 批量insert加载数据效率.首先可以参照俺师傅:http://www.eygle.com/faq/Nologging&append.htm[@more@]一 非归档模式下D:>sqlplus "/ as sysdba"数据库版本为9.2.0.1.0SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 8月 14 10:20:39 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.连接到:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - Production当

oracle 9i提供2种模式导出表数据，传统模式CONVENTIONAL PATH和直接模式DIRECT PATH，通过direct指定。[@more@]两者间差异1. Conventional path Export.传统导出模式使用SELECT语句抽取表数据,将数据从磁盘中读入到buffer cache缓冲区中,并应用SQL表达式，将纪录返回给导出客户端，然后写到dump文件。2. Direct path Export.直接导出模式，数据直接从磁盘中读取到导出session的UGA中，跳过了SQL命令处理层。避免了不必要的数据转换，然后将纪录返回给导出客户端，然后写到到处文件 性能比较:1. Direct Export 比Conventional Export要快很

可参照metalink文章Subject: HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCKDoc ID: Note:122793.1Type: TROUBLESHOOTINGLast Revision Date: 25-JUL-2005Status: PUBLISHED[@more@]查询v$session_waitp1raw表示: 16进制Libarry Cache Handle Address.SQL> select sid,event,p1,p1raw from v$session_wait where event like 'library%';SID EVENT---------- ----------------------------------------------------------------P1 P1RAW---------- --------138 library cache lock1213129416 484EE2C8139 library cache pin1213

http://www.itpub.net/406784.html在测试机上测试了一把发现和楼主测试的结构有些不同表，索引，索引字段已经分析过.我感觉区别在1 NL探测Inner table的次数和这个结果记录数.2 子查询效率，INDEX FAST FULL SCAN 代价.[@more@][PHP]SQL> SQL> select count(*) from track;COUNT(*)----------12337495Elapsed: 00:00:00.01SQL> select count(*) from terminal_user;COUNT(*)----------305Elapsed: 00:00:00.00SQL> SQL> set autotrace traceonlySQL> SQL> SQL> select tu.* from terminal_user tu2 where tu.tu_i

首先可以参见yang兄的bloghttp://yangtingkun.itpub.net/post/468/64662俺写了一个profile的实例当test用户连接总时间超过120分钟 或者 test用户单次空闲时间超过2分钟，自动断开连接[@more@]sql> show parameter resource_limitname type value------------------------------------ ----------- ------------------resource_limit boolean false启用profilesql> alter system set resource_limit = true;系统已更改。sql> create profile test_pro limit idle_time 2;配置文件已创建sql> alter user test profile test_pro

SQL> create table test_desc (id number not null,name varchar2(16)) tablespace users;Table created.SQL> create index test_id_desc_idx on test_desc(id desc) tablespace idx;Index created.Oracle默认情况下是升序索引，这里定义了降序索引。[@more@]我们看一下select会怎么样SQL> select * from test_desc where id<10;ID NAME---------- ----------------9 d8 d7 d6 d5 d4 d3 d2 d1 d下面我们求id小于100的前5条记录SQL> l1 select * from2* (select id,name,rownum rnk from test_desc where id<101) where