Monday 25 November 2013

User & Schema

The Difference between User and Schema in Oracle

an Oracle user is a database account with login access to the database;
an Oracle schema is an Oracle user plus the collection of database objects owned by the user.

Though user and schema differ in their definitions, for most practical purposes the words can be used interchangeably. When you create a user using the CREATE USER command, Oracle automatically creates a schema owned by that user. The schema name is the same as the user name. So the schema owned by user SCOTT is also SCOTT.

USER and SCHEMA are not always identical

When you login to a database using a particular Oracle user, you connect by default to that user’s schema. However, in Oracle it is possible to be connected to the database via one Oracle user and switch to another Oracle user’s schema.

The alter session set current_schema command in Oracle can be used to switch to another schema while being connected to a particular Oracle user. Take a look at the article User != Schema for a lucid illustration of how this can be achieved.

CREATE USER vs CREATE SCHEMA

Let’s revisit a statement made earlier in this post:

When you create a user using the CREATE USER command, Oracle automatically creates a schema owned by that user.

This begs the question: if CREATE USER creates a schema, what then does CREATE SCHEMA do?

Given that CREATE TABLE creates a table, CREATE INDEX creates an index, and so on, one might assume that CREATE SCHEMA would create a schema.

Not so.

CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction.

Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view.

SQL> CREATE SCHEMA AUTHORIZATION hr

  2  -- must be the same as the current schema name

  3     CREATE TABLE test1

  4        (col1 VARCHAR2(10) PRIMARY KEY

  5       , col2 NUMBER)

  6     CREATE VIEW test2

  7        AS SELECT col1

  8           FROM test1 WHERE col2 = 3

  9     GRANT select ON test2 TO dip;

Schema created.

In Conclusion

Oracle schema = Oracle user + database objects owned by that user.
When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default.
There is a one-to-one correspondence between Oracle user name and Oracle schema name.
While user = schema in most circumstances, that isn’t true all the time.

Views and Materialized

Differences between views and materialized

1. Moment Of Execution

A view’s SQL is executed at run-time. The results are fetched from the view’s base tables when the view is queried.

A materialized view (called snapshot in older Oracle versions) is a "pre-answered" query – the query is executed when the materialized view is refreshed. Its result is stored in the database and the query only browses the result.

2. Space

A view occupies no space (other than that for its definition in the data dictionary).

A materialized view occupies space. It exists in the same way as a table: it sits on a disk and could be indexed or partitioned.

3. Freshness of Output

A view’s output is built on the fly; it shows real-time data from the base tables being queried.

A materialized view does not reflect real-time data. The data is only as up to date as the last time the materialized view was refreshed.

4. Base on Rowid

Another difference between View vs materialized view is that, when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.

5. Where To Use

A view is best used when:

You want to hide the implementation details of a complex query
You want to restrict access to a set of rows/columns in the base tables

A materialized view is best used when:

You have a really big table and people do frequent aggregates on it, and you want fast response
You don’t mind the result being a little out of date, or your application data has more queries than updates (as in a BI/data warehousing system)

REF Cursor & Normal Cursor

What is difference between Cursor and Ref Cursor or Cursor variable in PL/SQL?

1) Ref cursors are used for dynamic query building and explicit cursors are used for static queries.

2) Ref cursors can be passed as parameters in procedures/functions where as explicit
cursors cannot be passed.

3) Explicit cursors should be defined at design time, whereas ref cursors can be defined at run time.

4. A "normal" plsql cursor is static in defintion. Ref cursors may be dynamically opened or opened
based on logic. Cursor C will always be select * from dual. The ref cursor can be anything.

5. Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure /function).

6. Ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

7. Another difference is a ref cursor can be returned to a client. a plsql "cursor cursor"
cannot be returned to a client.

Ex:-

DECLARE
TYPErcIS REFCURSOR;
CURSORcIS SELECT*FROMdual;
l_cursor rc;
BEGIN
IF (to_char(SYSDATE,''dd'')=30)THEN
OPENl_cursor FORSELECT* FROMemp;
ELSIF(to_char(SYSDATE,''dd'')=29)THEN
OPENl_cursor FORSELECT* FROMdept;
ELSE
OPENl_cursorFOR SELECT*FROMdual;
ENDIF;
OPENc;
CLOSEc;
END;

Sunday 24 November 2013

OLTP and OLAP

What is the Difference between OLTP and OLAP?

OLAP - On-line Analytical Process
OLTP - On-line Transactional Process

1)OLTP: This stores current data
OLAP: This stores current + History data for analysis.

2)OLTP:More no of Users
OLAP:Less no of Users

3)OLTP:E-R model for data modeling
OLAP:Dimension model for data modeling

4)OLTP:Its contains parent and child table
OLAP:Its contains Dimension table and Fact Table

5)OLTP:Designed for daily Transaction Purpose
OLAP:Designed for Queuing and reporting.

6)OLTP:data is present in 2d format
OLAP: 6.data is present in muti dim format

7)OLTP:No of index Few.
OLAP:No of index Many.

8)OLTP:Volatile data behavior
OLAP:Non-volatile data behavior

Cursor_Sharing Parameter

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Example:

SET TIMI ON

SHOW PARAMETER CURSOR_SHARING

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing string EXACT

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Elapsed: 00:00:00.18

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Elapsed: 00:00:10.37

SQL> CONN SCOTT/*****

Connected.

SQL> SELECT * FROM EMP WHERE DEPTNO=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

9001 SCOTT ANALYST 1500 10

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7839 KING PRESIDENT 17-NOV-81 5000 10

7934 MILLER CLERK 7782 23-JAN-82 1300 10

Elapsed: 00:00:00.06

SQL> SELECT * FROM EMP WHERE DEPTNO=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7566 JONES MANAGER 7839 02-APR-81 2975 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

Elapsed: 00:00:00.01

SQL> SELECT * FROM EMP WHERE DEPTNO=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

6 rows selected.

Elapsed: 00:00:00.01

SQL> SELECT * FROM EMP WHERE DEPTNO=40;

no rows selected

Elapsed: 00:00:00.00

SQL>

SQL> CONN SYS/****** AS SYSDBA

Connected.

SQL> DESC v$SQL

Name Null? Type

----------------------------------------------------------- -------- -----------------------------------------

SQL_TEXT VARCHAR2(1000)

SQL_FULLTEXT CLOB

SQL_ID VARCHAR2(13)

SHARABLE_MEM NUMBER

PERSISTENT_MEM NUMBER

RUNTIME_MEM NUMBER

SORTS NUMBER

LOADED_VERSIONS NUMBER

OPEN_VERSIONS NUMBER

USERS_OPENING NUMBER

FETCHES NUMBER

EXECUTIONS NUMBER

PX_SERVERS_EXECUTIONS NUMBER

END_OF_FETCH_COUNT NUMBER

USERS_EXECUTING NUMBER

LOADS NUMBER

FIRST_LOAD_TIME VARCHAR2(19)

INVALIDATIONS NUMBER

PARSE_CALLS NUMBER

DISK_READS NUMBER

DIRECT_WRITES NUMBER

BUFFER_GETS NUMBER

APPLICATION_WAIT_TIME NUMBER

CONCURRENCY_WAIT_TIME NUMBER

CLUSTER_WAIT_TIME NUMBER

USER_IO_WAIT_TIME NUMBER

PLSQL_EXEC_TIME NUMBER

JAVA_EXEC_TIME NUMBER

ROWS_PROCESSED NUMBER

COMMAND_TYPE NUMBER

OPTIMIZER_MODE VARCHAR2(10)

OPTIMIZER_COST NUMBER

OPTIMIZER_ENV RAW(797)

OPTIMIZER_ENV_HASH_VALUE NUMBER

PARSING_USER_ID NUMBER

PARSING_SCHEMA_ID NUMBER

PARSING_SCHEMA_NAME VARCHAR2(30)

KEPT_VERSIONS NUMBER

ADDRESS RAW(4)

TYPE_CHK_HEAP RAW(4)

HASH_VALUE NUMBER

OLD_HASH_VALUE NUMBER

PLAN_HASH_VALUE NUMBER

CHILD_NUMBER NUMBER

SERVICE VARCHAR2(64)

SERVICE_HASH NUMBER

MODULE VARCHAR2(64)

MODULE_HASH NUMBER

ACTION VARCHAR2(64)

ACTION_HASH NUMBER

SERIALIZABLE_ABORTS NUMBER

OUTLINE_CATEGORY VARCHAR2(64)

CPU_TIME NUMBER

ELAPSED_TIME NUMBER

OUTLINE_SID NUMBER

CHILD_ADDRESS RAW(4)

SQLTYPE NUMBER

REMOTE VARCHAR2(1)

OBJECT_STATUS VARCHAR2(19)

LITERAL_HASH_VALUE NUMBER

LAST_LOAD_TIME VARCHAR2(19)

IS_OBSOLETE VARCHAR2(1)

CHILD_LATCH NUMBER

SQL_PROFILE VARCHAR2(64)

PROGRAM_ID NUMBER

PROGRAM_LINE# NUMBER

EXACT_MATCHING_SIGNATURE NUMBER

FORCE_MATCHING_SIGNATURE NUMBER

LAST_ACTIVE_TIME DATE

BIND_DATA RAW(2000)

SQL> SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%';

SQL_TEXT

---------------------------------------------------------------------------------------------------------------

SELECT * FROM EMP WHERE DEPTNO=20

SELECT * FROM EMP WHERE DEPTNO=40

SELECT * FROM EMP WHERE DEPTNO=30

SELECT * FROM EMP WHERE DEPTNO=10

SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%'

Elapsed: 00:00:00.03

SQL>

SQL> ALTER SYSTEM SET cursor_sharing=SIMILAR;

System altered.

Elapsed: 00:00:00.12

SQL>

SQL> CONN SCOTT/TIGER

Connected.

SQL> SELECT * FROM EMP WHERE EMPNO=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed: 00:00:00.01

SQL> SELECT * FROM EMP WHERE EMPNO=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

Elapsed: 00:00:00.00

SQL> SELECT * FROM EMP WHERE EMPNO=9002;

no rows selected

Elapsed: 00:00:00.00

SQL> SELECT * FROM EMP WHERE EMPNO=9001;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

9001 SCOTT ANALYST 1500 10

Elapsed: 00:00:00.00

SQL> CONN / AS SYSDBA

Connected.

SQL> SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%';

SQL_TEXT

---------------------------------------------------------------------------------------------------------------

SELECT * FROM EMP WHERE DEPTNO=20

SELECT * FROM EMP WHERE DEPTNO=40

SELECT * FROM EMP WHERE DEPTNO=30

SELECT * FROM EMP WHERE EMPNO=:"SYS_B_0"

SELECT * FROM EMP WHERE DEPTNO=10

SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%'

6 rows selected.

Oracle Database
Table Partitioning
Object Oriented Concepts in SQL
External Tables Concepts
Virtual Columns in Oracle 11g
Global Temporary Table(GTT)
Temporary Tablespace
Merge Statement
Oracle Data Pump
Pragma In Oracle
Function Return Multiple Values and Rows
Pipelined Table Functions
SYS_REFCURSOR and REF CURSOR
PIVOT and UNPIVOT
Collection element_type as CURSOR
Cursor-Based Records
Find the Object Dependencies
Update with RETURNING clause
Create CSV file using PL/SQL
ORA Error
PL/SQL one time only procedure

Oracle Interview Question

Monday 25 November 2013

User & Schema

The Difference between User and Schema in Oracle

USER and SCHEMA are not always identical

CREATE USER vs CREATE SCHEMA

In Conclusion

Views and Materialized

1. Moment Of Execution

2. Space

3. Freshness of Output

4. Base on Rowid

5. Where To Use

REF Cursor & Normal Cursor

Sunday 24 November 2013

OLTP and OLAP

What is the Difference between OLTP and OLAP?

Cursor_Sharing Parameter

Popular Posts

Oracle Developer Community

Pages