Joins A join is a query that combines rows from two or more tables. Oracle performs a join whenever multiple tables appear in the FROM clause of the query. Below is a SQL join statement, legacy syntax and ANSI/ISO syntax (also referred as new join syntax, was first introduced in Oracle 9i release) in a …
Virtual Indexes (aka NOSEGMENT Indexes or Fake Indexes) are useful to check whether the creation of an Index affects the execution plan with out having to create the actual index. Virtual index is just a definition without a physical index segment so it does not consume disk space. Additionally, virtual index is available to a …
Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements …
CREATE TABLE has lot of options to go with it; it is hard to remember all of them. Most people will get lost while reading the rail road diagrams. It is fairly quick to create the table in its simple form and obtain all the parameters associated with it. Once the table is created in …
Get a complete CREATE TABLE definition using DBMS_METADATA package Read More »
This poster, supplied by Oracle University contains a comprehensive list that categorizes the database background processes and a single diagram that illustrates the relationships between key database memory, processes and strorage structures.
Still quite a few remain in 7.3, 8.1.7 and 9.2.0.8 (ofcourse, without a support on very old versions and happy with what they have) – Others are gearing towards to upgrade to Oracle 11g Release 1. The recommended direct and indirect path upgrade listed below: Direct Upgrade Path Source Database Target Database 9.2.0.4.0 (or higher) 11.1.x 10.1.0.2.0 …
Oracle database statistic names in this post is from Oracle Database 11g Release 11.1.0.6.0. Many of these statistics are tied to the internal implementation of Oracle and therefore are subject to change or deletion without notice, even between patch releases. Application developers should be aware of this and write their code to tolerate missing or …
To install statspack – you must be connected to database as SYSDBA and execute the script spcreate.sql located under $ORACLE_HOME/rdbms/admin. Additionally, you need have: the password you would like to set for PERFSTATS schema the default tablespace for PERFSTAT schema the temporary tablespace for PERFSTAT schema If you run it through SQL*Plus the output will …
Setting up Oracle Statistics Package (STATSPACK) Read More »
The initialization parameter REMOTE_OS_AUTHENT offers a trusted authentication model to the network, users can have OS accounts on machines other than the database server and gain access to database, convenience of single sign-on through remote OS authentication REMOTE_OS_AUTHENT accepts BOOLEAN value, FALSE is a default value, which can be altered via ALTER SYSTEM command. This …
How to create an Oracle User to use both OS Authentication and Password Authentication Read More »
Table x$ksppi contains all documented and undocumented (also know as hidden parameters, which starts with an underscore) parameters; joining with x$ksppcv table on column indx with filter predicate substr(x$ksppi.ksppinm,1,1) = ‘_’ get you parameter name, value and description. A complete list of undocumented parameters below: Oracle 11g (11.1.0.6) Undocumented Parameters Oracle 10g (10.2.0.4) Undocumented Parameters Oracle 9i …