Lesson 39 - Important system viewes

Important Views that each developer must know

 

USER_ERRORS  -  To list the current errors on the stored objects owned by the current user

USER_JOBS  -  - To list the jobs owned by the current user

USER_OBJECTS  - To list all objects owned by the current user

USER_ROLE_PRIVS  - To list the roles granted to the current user.

USER_SYNONYMS  - To list the private synonyms (synonyms owned by the current user)

USER_TABLES  - To listthe relational tables owned by the current user

USER_TAB_COLS  - To list the columns of the tables, views, and clusters owned by the current user

USER_TAB_MODIFICATIONS  - To list modifications to all tables owned by the current user that have been modified since the last time statistics were gathered on the tables.

USER_TAB_PRIVS  - To list the object grants for which the current user is the object owner, grantor, or grantee.

USER_TAB_PRIVS_MADE  - To list the object grants for which the current user is the object owner

USER_TAB_PRIVS_RECD  - To list the object grants for which the current user is the grantee.

USER_SYS_PRIVS  - To list system privileges granted to the current user

USER_TRIGGERS  - To list the triggers owned by the current user

USER_VIEWS  - To list the views owned by the current user

USER_CONSTRAINTS  - To list all constraint definitions on tables owned by the current user

USER_CONS_COLUMNS  - To list columns that are owned by the current user and that are specified in constraint definitions.

USER_DIMENSIONS  - To list dimension objects in the user's schema

USER_ENCRYPTED_COLUMNS –  maintains encryption algorithm information for all encrypted columns in all tables in the user's schema

USER_FREE_SPACE  - To list the free extents in the tablespaces accessible to the current user.

USER_INDEXES  - To list indexes owned by the current user

USER_IND_COLUMNS  - To list the columns of the indexes owned by the current user and columns of indexes on tables owned by the current user

USER_RESOURCE_LIMITS  - displays the resource limits for the current user.

USER_SEQUENCES  - To list all sequences owned by the current user

USER_SOURCE  - To list the text source of the stored objects owned by the current user

USER_LOBS  - displays the user's CLOBs and BLOBs contained in the user's tables

Topics

Post	Topics
1	Introduction Database
2	SQL*PLUS
3	How to execute sqlcommands
4	SQL* Plus commands
5	SQL command categories
6	Objects and schema in Oracle
7	Data Types in Oracle
8	Operators in Oracle
9	Database Tables
10	SELECT with WHERE clause
11	SELECT with ORDER BY Clause
12	Aggregate functions or Group by functions
13	SELECT with GROUP BY clause
14	SELECT with HAVING clause
15	UPDATE Statement
16	DELETE Statement
17	TRUNCATE TABLE  Statement
18	Built-in Functions - Arithmetic
19	Built-in Functions - Character types
20	Constraints
21	Sequences
22	SYNONYMS and PUBLIC SYNONYMS
23	VIEW
24	Union, Union all, Intersect and Minus
25	Date Functions
26	SAVEPOINT
27	Rollback
28	Indexes
29	ROLLUP option in SELECT
30	Pseudocolumn - ROWID
31	CUBE in SELECT
32	Virtual Columns
33	Global temporary tables (GTT)
34	GRANT and REVOKE
35	COMMENT Statement
36	Pseudocolumn - ORA_ROWSCN
37	Pseudocolumn - ROWNUM
Topics to come….
	Bind Variables
	Create Directory Statement
	Cursors
	Data Dictionary
	Data Pump
	Database Links
	Special functions
	DBMS JOBS
	Decode
	Exceptions
	Explain Plan
	External Table
	Flashback
	FOR loop
	HINT
	IF statement
	Index Organised tables
	Interview Questions on PL/SQL
	Interview Questions on SQL
	JAVA in Oracle
	Joins
	Materialised Views
	Merge
	Nested Tables
	OOPS in Oracle
	Packages
	PL/SQL
	Pseudocolumns
	Roles
	Set Transactions
	SQL Loader
	SQL queires using functions
	SQL Trace
	SQL Tuning
	Stored function
	Stored procedure
	Subquries
	Temporary tables
	Triggers
	User defined Data types
	and more……

Lesson 37 : Pseudocolumn - ROWNUM

Pseudocolumn  - ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

Example :

SQL> select rownum,name from employee;

    ROWNUM NAME

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

         1 John

         2 Devy

         3 Paul

         4 Raj

         5 Chetan

         6 Jayne

         7 Senthil

         8 Jeya

 

8 rows selected.

 

SQL> select rownum,name from employee order by name;

 

    ROWNUM NAME

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

         5 Chetan

         2 Devy

         6 Jayne

         8 Jeya

         1 John

         3 Paul

         4 Raj

         7 Senthil

 

8 rows selected.

 

SQL> select rownum,name from employee where rownum < 5;

 

    ROWNUM NAME

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

         1 John

         2 Devy

         3 Paul

         4 Raj

 

SQL> select rownum,name from employee where rownum = 5;

 

no rows selected

 

Lesson 36 : Pseudocolumn - ORA_ROWSCN

Pseudocolumn    -   ORA_ROWSCN

 Useful to find timestamp of last update of particular row in a table.

 

For each row in any table, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row.

This pseudocolumn is useful for determining approximately when a row was last updated. But, It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking.

Note :  We cannot use this pseudocolumn in a query to a view. However, We can use it to refer to the underlying table when creating a view. We can also use this pseudocolumn in the WHERE clause of an UPDATE or DELETE statement.

ORA_ROWSCN is not supported for Flashback Query. Instead, use the version query pseudocolumns, which are provided explicitly for Flashback Query.

Restriction: This pseudocolumn is not supported for external tables.

Example :

SQL> select ORA_ROWSCN,NAME FROM employee;

ORA_ROWSCN NAME

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

1.2499E+13 John

1.2499E+13 Devy

1.2499E+13 Paul

1.2499E+13 Raj

1.2499E+13 Chetan

1.2499E+13 Jayne

1.2499E+13 Senthil

1.2499E+13 Jeya

8 rows selected.

SQL> select * from employee;

NAME          DEPT_ID LOCATION       SALARY

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

John               10 Dallas          50000

Devy              10 Dallas          75000

Paul               20 Texas           85000

Raj                 20 Texas           65000

Chetan             30 Boston          95000

Jayne              30 Boston          55000

Senthil            30 Chennai         45000

Jeya               30 Chennai         25000

8 rows selected.

SQL> update employee set Salary=75000 where name='John';

1 row updated.

SQL> select sysdate from dual;

SYSDATE

--------------------
28-DEC-2012:09:36:52

SQL> commit;

Commit complete.

SQL> select NAME,ORA_ROWSCN,SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employee where name='John';

NAME       ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)

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

John       1.2516E+13 28-DEC-12 09.36.54.000000000 AM

 

Lesson 35 - COMMENT statement

COMMENT statement is used to add a comment(description) about a table, view, materialized view, or column into the data dictionary.

Syntax for Table level comment:

COMMENT ON <table name > IS 'string' ;

Example :

SQL> comment on employee is ' This table is for holding employee details' ;

SQL> comment on dept is ' This table is created by Russwelt on 01-Dec-2012';

Syntax for column level comment :

COMMENT ON <tablename.column name > IS 'string' ;

Column level comments are to add description about column

Examples :

SQL> comment on emp.hiredate is 'This is to store joining date of employee';

SQL> comment on emp.mgr is 'This is to store manager name of the employee';

We can view the comments on a particular table or column by querying the following data dictionary views.

USER_TAB_COMMENTS - For table level comments

USER_COL_COMMENTS - For column level comments

To drop comments :

We have to give empty string to remove existing commnets in tables or columns

Examples :

SQL> comment on employee is '';

SQL> comment on emp.hiredate is '';

 

Lesson 34 : GRANT and REVOKE

Grant and Revoke

Grant  statement  is used  to  grant privileges on tables, views, sequences, synonyms, procedures to other users or roles.

Revoke is used to take back the granted privileges   on tables, views, sequences, synonyms, procedures to other users or roles.

Here are the set  of  object level privileges available,

SELECT -    To query the table, ie) to run a select statement .

INSERT   -  To  insert new rows to the table, ie) to use  insert statement.

UPDATE  -  To update rows in the tabl using update statement.

DELETE   -   To delete rows from the table using delete statement.

REFERENCES   - To create a constraint that refers to the table.

ALTER   -   To   make changes in table definition using alter table statement.

INDEX   -  To create an index on  tables with the create index statement.

EXECUTE  -  To execute the function/procedure.

ALL [PRIVILEGES]  - To grant all the privileges for the object that you received from another schema/user.

Syntax :

GRANT <privileges>  ON  <object name>  TO  <user/schema or Role or PUBLIC >    [WITH GRANT OPTION]

Where,

WITH GRANT OPTION  is to enable the grantee to grant the object privileges to other users and roles.

PUBLIC  -  Specify PUBLIC to grant the privileges to all users.

Example :

SQL> grant select,update on emp to scott;

Grant succeeded.

SQL> grant  index on emp to appadmin;

Grant succeeded.

SQL> grant  execute  on tax_calc_proc to scott;

Grant succeeded.

SQL> grant  ALL on emp to appadmin WITH GRANT OPTION ;

Grant succeeded.

To give SELECT priviege to all users in the database

SQL> grant  SELECT on emp to PUBLIC  ;

Grant succeeded.

REVOKE

- To take back the granted privileages  from other user or role or public

Syntax :

REVOKE <privileges>  ON  <object name>  FROM  <user/schema or Role or PUBLIC >

Examples :

SQL> revoke select,update on emp from scott;

Revoke succeeded.

SQL> revoke  index on emp from appadmin;

Revoke succeeded.

SQL> revoke  execute  on tax_calc_proc from scott;

Revoke succeeded.

SQL> revoke  ALL on emp from appadmin WITH revoke OPTION ;

Revoke succeeded.

SQL> revoke  SELECT on emp from PUBLIC  ;

Revoke succeeded.

 Important tables on view of privileges,

 USER_TAB_PRIVS_MADE  -  Privileges are granted by you to other users.

USER_TAB_PRIVS_RECD -  Privileges are granted to you by other users

USER_ROLE_PRIVS - Privileges are granted to roles

USER_COL_PRIVS_MADE -  Column level privileges are granted by you to other users

USER_COL_PRIVS_RECD - Column level privileges are granted to you by other users/