Learn Database Concepts

Online table redefinition of a table  Lets create a table with some dependent objects for this setup. DROP PROCEDURE get_description; DROP VIEW redef_tab_v; DROP SEQUENCE redef_tab_seq; DROP TABLE redef_tab PURGE; CREATE TABLE redef_tab (   id           NUMBER,   description  VARCHAR2(50),   CONSTRAINT redef_tab_pk PRIMARY KEY (id) ); CREATE VIEW redef_tab_v AS SELECT * FROM redef_tab; CREATE SEQUENCE redef_tab_seq; CREATE OR REPLACE PROCEDURE get_description (   p_id          IN  redef_tab.id%TYPE,   p_description OUT redef_tab.description%TYPE) AS BEGIN   SELECT description   INTO   p_description   FROM   redef_tab   WHERE  id = p_id; END; / CREATE OR REPLACE TRIGGER redef_tab_bir BEFORE INSERT ON redef_tab FOR EACH ROW WHEN (new.id IS NULL) BEGIN   :new.id := redef_tab_seq.NEXTVAL; END; / Lets confirm ...

Global Temporary Tables in Oracle Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following: A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table. During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped. This statement creates a temporary table that is transaction specific: NOTE :   Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table. ***********************************...

Hints : Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements. Hints should only be used as a last-resort if statistics were gathered and the query is still following a sub-optimal execution plan. Example of the correct syntax for an index hint: select /*+ index(TEST_IDX IDX_OS_USR) */ * from TEST_IDX; If we alias the table (A in below case), you must use the alias in the index hint: select /*+ index(A IDX_OS_USR) */ * from TEST_IDX A; Note : Oracle decides to use weather to use this hint or not, of oracle finds that it has faster execution plan without using hint it ignores it. You might think that an index may be helpfull and provide it as hint but oracle may still ignore it. In below case you...

INVISIBLE INDEX: Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level CREATE AN INVISIBLE INDEX: CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE; lets check the newly created index : SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR'; OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY        --------      -----------                                    ------...

In this scenario I am trying to increase the value of parameter memory_max_target.  My initial  memory_max_target = 804 I want to increase it to 900 SQL> show parameter sga NAME     TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga     boolean FALSE pre_page_sga     boolean FALSE sga_max_size     big integer 804M sga_target     big integer 0 SQL> show parameter max_target NAME     TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_max_target     big integer 804M SQL> show parameter memory NAME     TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address     integer 0 memory_max_target     big integer 804M memory_target     big intege...

This is an awesome script that I have found online blogpost by  Gokhan Atil  (ORACLE ACE).I wanted to share this with my friends, as it will be very help full in daily maintenance. PLSQL for ADDM sent via EMAIL: DECLARE    dbid           NUMBER;    bid            NUMBER;    eid            NUMBER;    db_unique_name VARCHAR2(30);    host_name      VARCHAR2(64);    status         VARCHAR2(11);    starttime      CHAR (5);    endtime        CHAR (5);    output         VARCHAR2 (32000);    v_from         VARCHAR2 (80);    v_recipient    VARCHAR2 (80) := 'arvind@domain.com';    v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';    v_mail_conn ...

This is an awesome script that I have found online blogpost by  Gokhan Atil  (ORACLE ACE).I wanted to share this with my friends, as it will be very help full in daily maintenance. PLSQL to sen AWR report to you email directly. DECLARE    dbid           NUMBER;    inst_id        NUMBER;    bid            NUMBER;    eid            NUMBER;    db_unique_name VARCHAR2(30);    host_name       VARCHAR2(64);    starttime      CHAR (5);    endtime        CHAR (5);    v_from         VARCHAR2 (80);    v_recipient    VARCHAR2 (80) := 'arvind@domain.com';    v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';    v_mail_conn    UTL_SMTP.connection; BEGIN    sta...

I believe this is better explained with an example. Sql code: lets first check if your database table spaces that re in manual and auto segment space management.  SELECT tablespace_name, extent_management, segment_space_management     FROM dba_tablespaces;        TABLESPACE_NAME                EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT ------------------------------ ----------------- ------------------------ SYSTEM                         LOCAL             MANUAL                  SYSAUX                    ...