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. ***********************************...

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        --------      -----------                                    ------...

SELECT   E.EMPNO,   E.ENAME,   E.JOB,   D.DEPTNO,   D.LOC,   E.SAL FROM   scott.emp E LEFT JOIN SCOTT.DEPT D ON   E.DEPTNO=D.DEPTNO; SELECT   E.EMPNO,   E.ENAME,   E.JOB,   D.DEPTNO,   D.LOC,   E.SAL,   (     SELECT      grade     FROM       SCOTT.SALGRADE S     WHERE       E.SAL BETWEEN S.LOSAL AND S.HISAL   ) AS SALGRADE FROM   scott.emp E LEFT JOIN SCOTT.DEPT D ON   E.DEPTNO=D.DEPTNO;

chr function returns the ascii letter for that integer. We know that there are 255 ascii characters defined. SQL> select chr(65) as CHR from dual; CHR – A Below code print all 255 ascii characters Sample code to check the values : begin   for i in 1..255 loop       dbms_output.put_line( 'CHR('||i||')' ||'=='|| chr(i) );     end loop;     end;     / This output might differ actually based on the chacterset you have choosen while installing you Database. DBMS_OUTPUT :    CHR(1)== CHR(2)== CHR(3)== CHR(4)== CHR(5)== CHR(6)== CHR(7)== CHR(8)== CHR(9)==    CHR(10)== CHR(11)== CHR(12)== CHR(13)== CHR(14)== CHR(15)== CHR(16)== CHR(17)== CHR(18)== CHR(19)== CHR(20)== CHR(21)== CHR(22)== CHR(23)== CHR(24)== CHR(25)== CHR(26)== CHR(27)== CHR(28)== CHR(29)== CHR(30)== CHR(31)== CHR(32)== CHR(33)==! CHR(34)==" CHR(35)==# CHR(36)==$ CHR(37)==% CHR(38)==& CHR(39)==' CHR(40)==( CHR(41)==) CHR(42...

alter table table_name set unused  There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused. MARKING UNUSED COLUMN sql>  desc abc_test Name       Null Type          ---------- ---- ------------  NAME            VARCHAR2(20)  TOTAL_ROWS      NUMBER                                                                                   ...

There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused. MARKING UNUSED COLUMN sql>  desc abc_test Name       Null Type          ---------- ---- ------------  NAME            VARCHAR2(20)  TOTAL_ROWS      NUMBER                                                                                           ...