Query to Find the Sys Privileges for a user 

SELECT
nvl(b.grantee,'No_System_Privileges_Granted'),nvl(b.privilege,'null')
from
dba_users a,dba_sys_privs b
where a.username =b.grantee(+) and grantee=upper('SCOTT')
/

 Query to Find the Roles assigned to a user

SELECT
nvl(b.grantee,'No_Roles_Granted'),nvl(b.granted_role,'null')
from
dba_users a,dba_role_privs b
where a.username =b.grantee(+) and grantee=upper('SCOTT')
/

Select, Update, delete , Insert are the Privileges available on a table. If a user creates a table, he is called as owner of the table. He can grant read Privileges(select) to  any other user in the database.But what is an Read Only table ? how it differs from Select privilege?

If a table is marked as Read Only by DBA , then the table can never be modified by any user with in the database Including the user who has created this table.

The alter table command can now be used to make a table read-only. This allows the DBA to make a table read-only across the database, including the owner of the table.

The following Example illustrates this

Continue Reading

Hint                                                                               When Ignored

Cluster                                                               When Used with a nonclustered table

Hash                                                                   When used with a Nonclustered table

Hash_aj                                                              When no Sub_query Exists

Index                                                                  When the specified Index does not exist

index_combine                                                 When no bitmapped_indexes exist

Merge_aj                                                           When no Sub_query Exists

Push_subq                                                        When NO sub_query Exists

star                                                                     When No Proper index exists on the fact table

use_nl                                                                 When there is no index on any of the tables in Join

The use_hash hint is the most useful for joining a medium size table with a large table. The throughput is high for use_hash hint when compared to Use_NL hint. use_hash hint is mostly used in datawarehousing environments  where the overall query execution should be faster returning all rows rather than first row response time.

The use_has hint requests a hash join against the specified tables. Essentially a hash join is a technique wherey Oracle loads the rows from the driving table (the smallest table,after the where clause) into user global area( RAM) defined by the hash_area_size in the init.ora file.

Oracle then uses a hashing technique to locate the rows in the Larger second table.

The following query is an example of using Use_hash hint

Select /*+ use_hash( e,b) */  e.ename, b.bon, hiredate, from emp e, bonus b where e.ename=b.ename;

As the name “temporary” itself confronts that its a temporary storage used for sorting,computations, processing. Late 90’s “system” tablespace is being used as temporary tablespace for all the users which is highly not recommended since it brings down the performance of the database drastcially. “System” tablespace is the storge where metadata of the database is being stored and processed. Later Oracle has made a provision to specify a tablespace as an ” Temporary tablespace” while creating a user or with an ALTER user statement. But what happens when a user has been created with out any temporary tablespace specification. Indirectly he would be using the system tablespace which is not recommended. To avoid this situation oracle has introduced “Default Temporary tablespace”. A “Default Temporary Tablespace” can be created for an entire database.  Incase if you forgot to specify the temporary tablespace for an user, storage area specified as Default temporary tablespce would be used.

Continue Reading

Access Paths in Oracle

In this Blog we will discuss about various table access methods in oracle.

Access paths are the methods by which Oracle can get at our data
There are three broad categories of access paths
1.The infamous full scan
2.Index accesses
3.Directed access, by hash or ROWID

Table Full scans

Reads every block in a given segment that was used at some point or other (up to High Water Mark)
Can be most efficient access path for “small” tables
Are a very efficient method to read a lot of data, since it employs multi block read. i.e. can read multiple blocks in a single I/O
Can cause significant physical I/O, particularly on larger tables
Continue Reading

What block size should I use when creating a database? 

To begin with, the answer to that question depends entirely on the answer to another: are you using buffered I/O or not?

If you are buffering your I/O (that is, your file system has its own buffering mechanism, as most do by default), then your Oracle block size should match your file system’s buffer size exactly. For ext3, for example, that would mean 4K blocks by default, whilst for Tru64, it would mean 8K blocks -because those are the buffer sizes for the respective file systems. NTFS, by contrast, is a little unusual for mainstream file systems in that it does not have its own buffer, so reads from and writes to a volume formatted with NTFS are done directly -and at that point you can pretty much pick any Oracle block size you like.
Continue Reading

Expert sharing is online knowledge portal developed by professionals to assist students, professionals, hobbyists, experts to pioneer in various technologies like Oracle.

This is growing site supported by various professionals  specialized in their fields to assist others .Our ways of technology sharing includes realistic solutions, Tips, Tricks, Pod casts, Video tutorials, discussions, Articles etc. The technologies include Oracle, and also other advanced technologies related to oracle.This site provides pin point solutions ,examples ,tutorials, articles, How-to’s ,and everyday problems faced by Oracle professionals.And if you are a professional,Student working on any of these areas and want to help out others u can mail to us at