Thursday 27 June 2013

How to give read only access to the tables in a schema

Create a user

SQL> create user b identified by b;

User created.

SQL> grant select  table,create session to b;


From target schema idl

SQL>conn idl/idl;
connected
sql>GRANT SELECT ON AUDIRAIL TO b ;

From source schema

sql>conn b/b;
connected

SQL>  select * from idl.audirail;



select * from tab where tname = 'AUDIRAIL';

You can write a simple procedure to achieve this :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name 
FROM dba_tables 
 where owner='idl'
  and table_name in ('audirail', 'audirail2') ;
BEGIN 
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON '||audirail|| TO GPS;
execute immediate sql_txt;
END LOOP;
END;
/



FIX-EXP-00011 Easily


I had to export only tables from a schema

My export script:

exp sys/oracle file=home/oracle/PT_ow1.dmp log=/home/oracle/PT_ow1.log tables=manju1,manju2 rows=yes indexes=yes

Error
EXP-00011

Tried Datapump :But some different error


Solution:Point your schema name to that particular tablename

example
tables=schemaname.tablename ,schemaname.tablename

My modified export script:


exp sys/oracle file=home/oracle/PT_ow1.dmp log=/home/oracle/PT_ow1.log tables=pt_ow.manju1,pt_ow.manju2 rows=yes indexes=yes


It worked"