how to create global temporary table in oracle

A temporary table has a definition and structure like that of a regular table, except that the data only exists for the duration of the current transaction or session. CREATE GLOBAL TEMPORARY TABLE temp_table      (a number,       b varchar2(10)      ); Temporary table is cleaned up atomically at session end or transaction end.    INSERT INTO temp_table    VALUES (1,'ABC'); If you fire select statement, it will display data. SELECT * FROM temp_table; OUTPUT ------------------- A   B --------------- 1   ABC Once you commit, the data from temporary table is deleted. COMMIT; If you fire select statement, it will not display any data. SELECT * FROM temp_table; i.e., in same session once the data is committed it will delete data from temporary table. The default behaviour of temporary table is ON COMMIT DELETE ROWS, which means that rows are visible only within the transaction. ON COMMIT PRESERVE ROWS - The rows are visible after the transaction, but within the session only. create global temporary table temp1(a number, b varchar2(5))      ON COMMIT PRESERVE ROWS;

what are the different optimisation techniques

	Execute Plan - we can see the plan of the query and change it accordingly based on the indexes
	Optimizer_hint - set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS'); Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept where (Deptno > 25)
	Optimize_Sql - By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for ever time the SQL must be parsed whenever they are executed.
	Optimize_Tp - By setting the Optimize_Tp= No, Oracle Forms assigns separate cursor only for each query SELECT statement. All other SQL statements reuse the cursor. f45run module = my_firstform userid = scott/tiger optimize_Tp = No

how to implement If statement in the select statement

We can implement the if statement in the select statement by using the Decode statement. Example      SELECT DECODE (EMP_CAT,'1','First','2','Second'Null); Here the Null is the else statement where null is done . Here DECODE implement below IF – ELSE logic                      IF EMP_CAT = ‘1’ THEN                                   ‘FIRST’                       ELSIF EMP_CAT = ‘2’ THEN                                   ‘SECOND’                      ELSE                                    NULL                      END IF;

how many types of sql statements are there in oracle

There are basically 6 types of sql statement

Data Definition Language(DDL) - The DDL  statements define and maintain objects and drop objects.

Data Manipulation Language(DML) - The DML statements manipulate database data.

Transaction Control Statements - Manage change by DML

Session Control - Used to control the properties of current session enabling and disabling roles and changing .e.g.,  Alter Statements, Set Role

System Control Statements - Change Properties of Oracle Instance .e.g., Alter System

Embedded Sql - Incorporate DDL,DML and T.C.S in Programming Language.e.g.,Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

how many Integrity Rules are there

	Entity Integrity Rule - The Entity Integrity Rule enforces that the Primary key cannot be Null.
	Foreign Key Integrity Rule  - The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
	Business Integrity Rules - The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

what does database do during mounting process

During database mount process, Oracle would check for the existence of control files mentioned in init.ora file but it wont check the contents of the control file which is done during the opening of database.

what is the use of redo log information

Redo log information are used to recover database if it get corrupt.

what is the frequency of log updated

COMMIT or ROLLABCK Time out occurs (3 secs) 1/3 of Log full 1 MB of Redo Checkpoint occurs

Can objects of the same schema reside in different tablespaces

Yes, it can. For example if you specify a different tablespace (B) for indexes, the indexes of the tables that the user create would be residing in B, and the table would reside in the user's default tablespace A.

Yes: Schema objects can stored in different tablespace and a tablespace can contained one or more schema objects data.

does view contain data

A view does not contain any data of its own, but is like a window through which data from other tables can be viewed and changed

The answer depends on the type of view. In case of normal view, the answer is NO it only contains query based on a base table but in case of materialized view, YES it does contain data and for the updated data in the base table, it needs to be refreshed.

check which user has which role

You can use the below tables to find which user has assigned which role.

DBA_TAB_PRIVS ROLE_TAB_PRIVS DBA_ROLE_PRIVS

Run the below script:

SELECT *                                 FROM dba_role_privs                               ORDER BY grantee;

what is an Oracle index

An Index is a tree structure that allows direct access to a row in a table. Indexes can be classified based on their logical design or on their physical implementation. The Logical classification groups indexes from an application perspective, while the physical classification is derived from the way the indexes are stored.

An index is a schema object that can speed up the retrieval of rows by using pointers. If you do not have an index, then a full table scan occurs. Its purpose is to reduce disk I/O by using an indexed path to locate data quickly. If a table is dropped, the corresponding indexes are also dropped.

It is created in existing table to locate rows more quickly & efficiently. The users cannot see the indexes; they are just used to speed up the queries

Oracle provides many different types of indexes:

B*Tree Indexes Index Organized Tables B*Tree Cluster Indexes Reverse Key Indexes Descending Indexes Bitmap Indexes Function-based indexes

USER_INDEXES data dictionary view contains the name of the index and its uniqueness USER_IND_COLUMNS view contains the index name, the table name, and the column name.

can we create index on long column

NO we can't create index on long raw column.

what are clusters

Groups of tables physically stored together because they share common columns and are often used together is called clusters.

what is a database instance

An Oracle instance is a combination of background processes and memory structures (SGA).

What is bitmap index

Bitmap indexes are designed for data warehousing/ad‐hoc query environments where the full set of queries that may be asked of the data is not totally known at system implementation time.

Bitmap indexes are structures that store pointers to many rows with a single index key entry, as compared to a B*Tree structure where there is parity between the index keys and the rows in a table.

Example              CREATE BITMAP index job_idx on emp(job); Oracle will store something like this:

Value	1	2	3	4	5	6	7	8	9	10
ANALYST	0	0	0	0	0	0	0	1	0	1
CLERK	1	0	0	0	0	0	0	0	0	0
MANAGER	0	0	0	1	0	1	1	0	0	0
PRESIDENT	0	0	0	0	0	0	0	0	1	0
SALESMAN	0	1	1	0	1	0	0	0	0	0

Row 8 and 10 have the value for ANALYST whereas row 4,6 and 7 have the value for MANAGER.

what is a functional index

Function-based indexes can use any Function or Object method that is declared as repeatable. Function‐based indexes give us the ability to index computed columns and use these indexes in a query.

Queries using expressions can use the index. Example              CREATE INDEX sales_margin_inx                        ON sales (revenue - cost); SELECT ordid    FROM sales WHERE (revenue - cost) > 1000;

We have to enable Function-based indexes by enabling the following initialization parameters:                  ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; Function based Index

difference between and constraints and triggers

Constraints are used to maintain the integrity and atomicity of database.
In other words it can be said they are used to prevent invalid data entry.
The main 5 constraints:

1. NOT NULL

2. PRIMARY KEY

3. FOREIGN KEY

4. UNIQUE KEY

5. CHECK

Triggers are basically stored procedures, which automatically fired when any insert, update
or delete is issued on table.

Trigger effected only those row after which trigger applied but constraint effected all row of table.

what is normalization and its advantage

1	The process of separating data into distinct, unique sets is called normalization. This is implemented to improve the performance of the RDBMS, such as reduces redundancy of data and data inconsistency.
2	Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability.
3	Database normalization is a series of steps followed to obtain a database design that allows for consistent storage and efficient access of data in a relational database. These steps reduce data redundancy and the risk of data becoming inconsistent.
4	Normalization is the process used to reduce the unnecessary repetition of data i.e., redundant data. It is performed on the data, which is redundant and makes the data in a normalized format. It is of step-by-step process Ist Normal, Form IInd Normal, form IIIrd Normal, form IVth Normal form or Boyce odd Normal form By performing this we will get the data in the Normalized formatted from DBMS to RDBMS.

Find how many database resides in Oracle Server

select count(*) from v$database;

Script will return the number of database that resides on Oracle Server.