tag:blogger.com,1999:blog-322105872024-03-08T18:42:22.584+05:30Oracle DBA Notes, Documentations for NewbiesHelping Oracle/MySql Newbies, by providing Notes, Documentation, Tips & Tricks, OCP Q & A, Useful Links, FAQs, Oracle DBA Q & A and lots more......Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comBlogger29125tag:blogger.com,1999:blog-32210587.post-60934371154900132642010-09-05T21:09:00.004+05:302010-09-06T22:58:04.568+05:30Which Command is more efficient: Move vs Shrink?Common Advantages:
1) Reset the high water mark of a table.
2) Resolves the row-chaining.
3) “Alter table move” moves the rows to un-used space and reset the High-Water Mark.
4) Better index access takes place because of a smaller b-tree.
5) Chained-rows are repaired.
6) Can be done online.
Difference:
Shrink:
a) Doesn't lock the objects in exclusive mode.
b) Executes in LOCKED_MODE=3 (Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-91297059375362504562007-06-06T07:52:00.003+05:302010-09-05T02:17:34.518+05:30What is adident utility? Or To know the file Version in Oracle Apps?adident utility in oracle apps is used to find version of any file .
AD Identification:
Example: adident Header < filename >Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-86123372415745999122007-06-06T07:50:00.004+05:302010-09-05T02:24:09.571+05:30Patch Log file in Oracle AppsQ: Where are the log files for the patch process stored?
The log files that are generated during the patch process are stored in the
following locations:
- $APPL_TOP/install/log
- $APPL_TOP/admin/log
Q: What other log files are generated when applying a patch?
When a patch is applied, it may generate other log files besides the log name
that is entered at the start of adpatch. Following are Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-36306447381225236442007-06-06T07:43:00.004+05:302010-09-05T02:28:53.317+05:30What is the oraInventory?The oraInventory is the location for the OUI (Oracle Universal Installer)'s
bookkeeping. The inventory stores information about:
* All Oracle software products installed in all ORACLE_HOMES on a machine
* Other non-Oracle products, such as the Java Runtime Environment (JRE)
In a 11i Application system the RDBMS and iAS ORACLE_HOMEs are registered in
the oraInventory. The 806 ORACLE_HOME, which Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-69405945222900600452007-06-06T07:12:00.005+05:302010-09-05T02:40:20.579+05:30Can't View Reports in Concurrent_RequestPROBLEM: 1) Can't "View Output"/"View Log file" in the Concurrent Request.
2) Can't Copy files (Navigation: Special --> Copy Files). ENVIRONMENT: 11.0.3/11i.
FIX: 1. Check Profile Option: PROFILE > SYSTEM
APPLICATIONS: Web Agent = http://test.abc.com:8007/pls/DEV
2. Check that the FNDWRR.exe exists in the $FND_TOPVamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-1160750724560641152006-10-13T20:06:00.000+05:302006-10-16T21:41:06.713+05:30Install Oracle Application ServerI used to read all of documentations and blogs on Oracle Application Server 10g installation. Out of all I fond best for the installation of Oracle Application Server are follows:1) Installing Oracle 10g Application Server On LinuxThis Includes:-> Pre-installation Tasks-> Installing the Oracle Application Server 10g Infrastructure-> Installing the Oracle Application Server 10g Mid Tier (J2EE and Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-1160740739820506572006-10-13T17:27:00.000+05:302006-10-17T14:36:19.483+05:30Few Important Oracle QueriesI found the one more excellent query which can help the oracle dba in doing performance tuning.--> Find the Large Table Scans:SELECT table_owner, table_name, table_type, size_kb, statement_count, reference_count, executions, executions * reference_count total_scansFROM (SELECT a.object_owner table_owner, a.object_name table_name, Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-1160536507251366162006-10-11T08:19:00.000+05:302006-10-21T08:04:39.686+05:30Oracle Performance Tuning Tips-----------------------------------> Tuning Multi-Threaded Server=================================If SERVERS_HIGHWATER exceeds the MTS_SERVERS parameter, increase the number ofMTS_SERVERS in the init&SID.ora file.set heading offselect 'Current MTS_SERVERS number is ' || value from v$parameter where name = 'mts_servers';set heading onselect * from v$mts;-------------------------------> Tuning The Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-1159429824456012962006-09-28T12:51:00.000+05:302006-10-10T00:50:27.343+05:30How busy your database is?What a transaction?A transaction is one logical piece of work in the database, or 'the execution of a program that includes database access operations'. Some transactions are read-only transactions while other transactions update, or write, to the database. All transactions must exhibit the ACID properties. Transactions that make changes to the database are completed when they are committed or Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com2tag:blogger.com,1999:blog-32210587.post-1158734812086681012006-09-20T11:59:00.000+05:302006-09-20T12:34:36.803+05:30Excessive wait eventsSQL> select a.event e0, count(*) t0 from sys.v_$session_wait a , sys.v_$event_name n where a.event= n.name group by event;E0 T0---------------------------------------------------------------- ----------SQL*Net message from client 135SQL*Net message to clientVamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-1157561263621976792006-09-06T22:17:00.000+05:302006-09-06T22:23:38.396+05:30How The Oracle Database Processes SQL StatementsThe following is a summary of how Oracle processes SQL statements. First the flowchart followed by a brief explanation of each step. Step 1: Create a CursorCursor creation can either occur implicitly or be explicitly declared.Step 2: Parse the StatementWhat is parsing? Parsing is the process of:·Translating a SQL statement, verifying it to be a valid statement·Performing data dictionary lookupsVamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-1157477896556329112006-09-05T22:59:00.000+05:302006-09-05T23:50:31.680+05:30ROLLBACK CONTENTION STATISTICSTo properly configure a system's rollback segments, you must create enough rollback segments, and they must be of a sufficient size. That seems fairly simple, but it is not. You can observe how the rollback segments are being used, and from that determine what needs to be done.Determining the Number of Rollback SegmentsThe number of rollback segments should be determined by the number of Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0tag:blogger.com,1999:blog-32210587.post-1156389387780689172006-08-24T08:40:00.000+05:302006-08-24T08:49:23.216+05:30Oracle 10g RAC On Linux or Windows 2003 Using VMware ServerFor the newbies sometimes it is diffcult to get the test database/box/server to practice Oracle 10G RAC installation on Linux/Windows 2003.Here, Now with this it will be reduceing your difficulties to some extent.Oracle 10g RAC On Linux Using VMware ServerOracle 10g RAC On Windows 2003 Using VMware ServerHope you like it...Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156301194652765002006-08-23T08:15:00.000+05:302006-08-23T08:16:44.056+05:30What is the COST?If you use the Cost Based Optimizer (CBO) in your Oracle database, you may be surprised to know that decreasing the ‘COST’ of a query does not necessarily mean increased performance.The following is a summary of a long thread on AskTom about this topic:The ‘COST’ of a query is Oracle’s estimate of the completion time for a query. But the estimate for the completion time is based on Oracle’s Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156260203626810252006-08-22T20:52:00.000+05:302006-08-22T20:53:25.766+05:30Oracle 1z0-032: Oracle 9i Database Fundamentals-II #5QUESTION NO: 21Which two statements are true regarding the use of UTF-16 encoding? (Choose two)A. Enables easier loading of multinational data.B. Uses a fixed-width Multibyte encoding sequence.C. Asian characters are represented in three characters.D. Used a variable-width Multibyte encoding sequence.E. European characters are represented on one or two bytes.Answer: A, BExplanation:AL16UTF16 is aVamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156259716359847692006-08-22T20:36:00.000+05:302006-08-22T20:45:16.716+05:30Prevention, Detection and Repair of Database Corruption Part-2Types of CorruptionPhysical or structural corruption can be defined as damage to internal data structures which do not allow Oracle software to find user data within the database. Logical corruption involves Oracle being able to find the data, but the data values are incorrect as far as the end user is concerned.Physical corruption due to hardware or software can occur in two general places -- inVamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156182423288868182006-08-21T23:16:00.000+05:302006-08-21T23:17:03.676+05:3017 tips from Oracle9i Performance Tuning Avoiding Problematic Queries1. Avoid Cartesian products2. Avoid full table scans on large tables3. Use SQL standards and conventions to reduce parsing4. Lack of indexes on columns contained in the WHERE clause5. Avoid joining too many tables6. Monitor V$SESSION_LONGOPS to detect long running operations7. Use hints as appropriate8. Use the SHARED_CURSOR parameter9. Use the Rule-based optimizer if I is better than Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156176291905940492006-08-21T21:31:00.000+05:302006-08-21T21:41:33.960+05:30Prevention, Detection and Repair of Database Corruption Part-1Corruption can be defined as an inconsistency in the data block structures or memory structures as a result of disparate problems. Corruption can be the result of human error (including software, firmware, and hardware bugs) or the environment (component failure).Lets first define Prevention, Detection, and Repair.Prevention :Prevention can be defined as a set of steps that can be taken to Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156129868040806192006-08-21T08:39:00.000+05:302006-08-21T08:41:08.196+05:30Interview Questions for Oracle, DBA, Developer CandidatesPL/SQL Questions:1. Describe the difference between a procedure, function and anonymous pl/sql block.Level: LowExpected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn?t have to.2. What is a mutating table error and how can you get around it?Level: IntermediateExpected answer: This happens with triggers. It occurs because the Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156129408996219272006-08-21T08:30:00.000+05:302006-08-21T08:35:48.493+05:30Free Online books or Tutorials on Database(Oracle/Mysql/SqlServer/Postgre)List of Books available for free download:1) Oracle 9i Database Administrators Guide2) Oracle 9i Database Concept3) Oracle 9i Database Getting Started 4) Oracle 9i Security Network Integration5) Oracle 8 How to.zip6) O'Reilly - Oracle Application7) Oreilly Mastering Oracle SQL8) Teach Yourself Oracle 8 In 21 Days9) Oracle Unleashed (SAMS)10)Oracle PL/SQL Bookshelf11)Using Oracle8http://Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1156126869145851482006-08-21T07:49:00.000+05:302006-08-21T07:51:10.136+05:30How do you tune your Oracle database?This article provides technical insight into Oracle database Wait-Event performance issues. Wait-Event Analysis for Oracle Databases is the industry best practice today for database tuning and performance optimization. Understanding the importance of each Oracle Wait-Event or “Resource” is essential for DBAs in keeping their database performing at its optimal level. Each month, Confio Software Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1155457736539684982006-08-13T13:57:00.000+05:302006-08-13T13:58:56.856+05:30Oracle 1z0-032: Oracle 9i Database Fundamentals-II #4QUESTION NO: 16What is the new Oracle Shared Server?A. An improved version of multithreaded server configuration.B. A connection pooling configuration where several clients are connected to the same server process.C. Two more database servers, which share data by means of database links; the client software is unaware to which server it is connected.D. A configuration of Real Application Cluster Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1155263905258628102006-08-11T08:07:00.000+05:302006-08-11T08:08:25.463+05:30Oracle 1z0-032: Oracle 9i Database Fundamentals-II #3QUESTION NO: 11When does Oracle9i flag an index as being used in the V$OBJECT_USAGE view?A. During a query’s parse time only.B. During a query’s execution time only.C. During any kind of DML statement execution.D. During the execution of an INSERT statement only.Answer: AExplanation:During a query's parse time Oracle9i flags an index as being used in the V$OBJECT_USAGE view. You can query the V$Vamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.comtag:blogger.com,1999:blog-32210587.post-1155086685101195542006-08-09T06:53:00.000+05:302006-08-09T06:54:45.113+05:30Oracle 1z0-032: Oracle 9i Database Fundamentals-II #2QUESTION NO: 6Which statement correctly describes the function of Oracle9i Cache Fusion feature?A. It provides each session with its own view of the database at a different point in the past.B. It enables you to execute scalable applications on a clustered database without having to partition the users or the database tables.C. It lets you dynamically reassign memory in your database buffer cacheVamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com1tag:blogger.com,1999:blog-32210587.post-1154920081504734642006-08-07T08:29:00.000+05:302006-08-07T08:38:01.506+05:30Oracle 1z0-032: Oracle 9i Database Fundamentals-II #1QUESTION NO: 1 What criterion does Oracle9i use to determine whether a database file is an Oracle Managed File?A. The filename format.B. Information stored inside a data dictionary table.C. Information stored in the ALERT.LOG file for the corresponding instance.D. Information stored inside the corresponding initialization parameter file for the instance.Answer: AExplanation:Oracle will use namingVamsi Chikkamhttp://www.blogger.com/profile/04823039630914103422noreply@blogger.com0