SELECT * FROM DBA_TAB_PARTITIONS WHERE OWNER='&OWNER'; 

**************Object related Information **********************************

select owner,object_type,count(*) from dba_objects where owner='test' group by owner,object_type;

select table_name from dba_tables where owner='test' and temporary!='Y' order by 1 asc;

select index_name from dba_indexes  where owner='test'  order by 1 asc;

select trigger_name from dba_triggers where owner='test'; 

select owner,constraint_type,count(*) from dba_constraints 

where owner='test' and constraint_name not like '%_SYS_%'  group by owner,constraint_type; 

*********Disable All Referencing Constraints and Triggers Before Importing Data*******

--Disable All Triggers
select 'alter trigger '||owner||'.'||trigger_name||' disable ;' from dba_triggers where owner IN ('test')  and status='ENABLED' and trigger_name !='T_CALC';

 select status,count(*) from dba_triggers where owner='test' group by status;

--Disable All Foreign Key Constraints
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints  where owner IN ('test') and constraint_type = 'R' and status='ENABLED';

 select status,count(*) from dba_constraints where owner IN ('test') and constraint_type = 'R'
group by status;

*************Truncate all Data  tables **************

SELECT 'TRUNCATE TABLE '  || owner || '.' ||   table_name || ';' FROM DBA_TABLES WHERE owner='test_dbo'; 

---------------------------------User Create and Privallage----------------------------------------------------

CREATE USER DPBI_DBO identified by test#V
  DEFAULT TABLESPACE "test_DAT_128K"
  TEMPORARY TABLESPACE "TEMP"
  PROFILE "TEST_APP_PROXY_PROFILE";

--  GRANT "DBA" TO "TEST_DBO";
    Create role SQLT_USER_ROLE;
    GRANT "SQLT_USER_ROLE" TO "TEST_DBO";

    ALTER USER "TEST_DBO" DEFAULT ROLE ALL;

  GRANT ALTER ANY MATERIALIZED VIEW TO "TEST_DBO";
  GRANT CREATE MATERIALIZED VIEW TO "TEST_DBO";
  GRANT UNLIMITED TABLESPACE TO "TEST_DBO";

  GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO "TEST_DBO";
  --GRANT SELECT ON "TEST"."T_APPS" TO "TEST_DBO" WITH GRANT OPTION;

GRANT connect,resource, create session to TEST_APP: 

create role TEST_RO_ROLE;

create role TEST_RW_ROLE;

BEGIN

FOR t IN (SELECT object_name FROM dba_objects WHERE owner='DPBI_DBO' AND object_type= 'TABLE' AND object_name not like '%IOT%' AND temporary !='Y' and object_name not like '%awsdms%')

LOOP

EXECUTE IMMEDIATE 'GRANT SELECT ON ETERR.'||t.object_name||' TO DPBI_RO_ROLE';

END LOOP;

END;

/ 

Read-Write Only Privileges:
=====================================

 BEGIN
FOR t IN (SELECT object_name FROM dba_objects WHERE owner='DPBI_DBO' AND object_type= 'TABLE' AND object_name not like '%IOT%' AND temporary !='Y' and object_name not like '%awsdms%')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON REFUND.'||t.object_name||' TO DPBI_RW_ROLE';
END LOOP;
END;
/

Grant TEST_RW_ROLE to TEST_APP: 

Grant TEST_RW_ROLE to TEST_APP: