In my case existing schema is APPS and need to create a schema like CMSRC.
Step 1:
SQL> Set long 100000000
SQL> select dbms_metadata.get_ddl('USER','APPS') from dual;
DBMS_METADATA.GET_DDL('USER','APPS')
--------------------------------------------------------------------------------
CREATE USER "APPS" IDENTIFIED BY VALUES '*******************'
DEFAULT TABLESPACE "ODSDATA_TBS"
TEMPORARY TABLESPACE "TEMP"
Step 2:
SQL> select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','APPS') from dual;
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','APPS')
--------------------------------------------------------------------------------
GRANT "RESOURCE" TO "APPS"
GRANT "SELECT_CATALOG_ROLE" TO "APPS"
GRANT "AQ_ADMINISTRATOR_ROLE" TO "APPS" WITH ADMIN OPTION
GRANT "JAVASYSPRIV" TO "APPS"
GRANT "CTXAPP" TO "APPS"
GRANT "EBS_APPS_ROLE" TO "APPS"
Step 3: Create CMSRC schema
CREATE USER CMSRC IDENTIFIED BY cmsrc DEFAULT TABLESPACE ODSDATA_TBS TEMPORARY TABLESPACE TEMP;
Step 4: Granting permissions to CMSRC schema as like APPS schema
GRANT "RESOURCE" TO "CMSRC"
GRANT "SELECT_CATALOG_ROLE" TO "CMSRC"
GRANT "AQ_ADMINISTRATOR_ROLE" TO "CMSRC" WITH ADMIN OPTION
GRANT "JAVASYSPRIV" TO "CMSRC"
GRANT "CTXAPP" TO "CMSRC"
GRANT "EBS_APPS_ROLE" TO "CMSRC"
Hope this post helps :)
Step 1:
SQL> Set long 100000000
SQL> select dbms_metadata.get_ddl('USER','APPS') from dual;
DBMS_METADATA.GET_DDL('USER','APPS')
--------------------------------------------------------------------------------
CREATE USER "APPS" IDENTIFIED BY VALUES '*******************'
DEFAULT TABLESPACE "ODSDATA_TBS"
TEMPORARY TABLESPACE "TEMP"
Step 2:
SQL> select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','APPS') from dual;
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','APPS')
--------------------------------------------------------------------------------
GRANT "RESOURCE" TO "APPS"
GRANT "SELECT_CATALOG_ROLE" TO "APPS"
GRANT "AQ_ADMINISTRATOR_ROLE" TO "APPS" WITH ADMIN OPTION
GRANT "JAVASYSPRIV" TO "APPS"
GRANT "CTXAPP" TO "APPS"
GRANT "EBS_APPS_ROLE" TO "APPS"
Step 3: Create CMSRC schema
CREATE USER CMSRC IDENTIFIED BY cmsrc DEFAULT TABLESPACE ODSDATA_TBS TEMPORARY TABLESPACE TEMP;
Step 4: Granting permissions to CMSRC schema as like APPS schema
GRANT "RESOURCE" TO "CMSRC"
GRANT "SELECT_CATALOG_ROLE" TO "CMSRC"
GRANT "AQ_ADMINISTRATOR_ROLE" TO "CMSRC" WITH ADMIN OPTION
GRANT "JAVASYSPRIV" TO "CMSRC"
GRANT "CTXAPP" TO "CMSRC"
GRANT "EBS_APPS_ROLE" TO "CMSRC"
Hope this post helps :)
No comments:
Post a Comment