• Arun Kumar

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user.

There are two simple ways to achieve this:

Generate SQL for each table

In this method, we will generate GRANT SELECT statement for each table using below query. Here we are trying to GRANT SELECT on each table owned by HR user to SCOTT user:

Select 'GRANT SELECT ON HR.'||Table_Name||' TO SCOTT;' 
From All_Tables Where Owner='HR';

Here is the sample output of the above command:


Now you can execute individual statements from the above output to GRANT SELECT ON all HR tables to SCOTT user.

Using simple LOOP statement

We can take use of PL/SQL and write a simple loop statement as below to automatically execute the GRANT SELECT statements for us. Again we are trying to GRANT SELECT on every table owned by HR user to SCOTT user:

   FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='HR') LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to SCOTT';

Here is the sample output of the above command:



Recent Posts

See All

Temp Tablespace utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp

Estimate flashback destination size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashbac

Datafile utilization check

When you want to shrink a datafile, you must always check the single datafile utilization. In case if you shrink datafile more than the used size, it will fail. Below query gives the datafile utilizat