Learn Database Concepts

SET VERIFY OFF ACCEPT par1 prompt "ENTER PARAMETER #1: " ACCEPT par2 prompt "ENTER PARAMETER #2: " execute pkg_TEST_VARIABLES.TEST_PASS_VARIABLES ( &&par1, &&par2);

select grants on tables to users or roles SELECT   OWNER,   GRANTEE,   GRANTOR,   TABLE_NAME,   PRIVILEGE FROM   DBA_TAB_PRIVS WHERE   table_name   = 'TABLENAME' AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE') AND GRANTEE   IN ('ROLE1','ROLE2') ORDER BY   1,2; select grants on table to a user thru role or direct priv select Grantee,'Granted Through Role' as Grant_Type, role, table_name from role_tab_privs rtp, dba_role_privs drp where rtp.role = drp.granted_role and table_name = 'TABLENAME' union select Grantee,'Direct Grant' as Grant_type, null as role, table_name from dba_tab_privs where table_name = 'TABLENAME' ;

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

ORA-00020: maximum number of processes This error occurs when your total numbers of sessions connecting to oracle database has exceeded the max limit set in parameter file. Simplest way to overcome this error is to reset the max sessions value to a greater value than existing.Here is how to do it   oracle@LINUX201:[~] $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:20:26 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-00020: maximum number of processes (500) exceeded oracle@LINUX201:[~] $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:23:42 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-00020: maximum number of processes (500) exceeded Enter user-name: Disconnected from ORACLE I wasn't able get into the oracle database to kill some database session. So I tried to kill few sessions on OS to make my way into DB. oracle@LINUX201:[~] $ ps -ef|grep oracle . . . . . . ....

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

Inserting Data with DML Error Logging: When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature. To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time. DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements. -------------------------------------------------------- --  DDL for Table ATEST1 ---------------------...

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