OCI - Create Schema using Web sql developer

 OCI -  Create Schema using Web sql developer 

• Here we are Creating Schema using Web sql developer we can create another way also like connecting with oracle client .

Commands to create user  and privilages examples

• CREATE USER test IDENTIFIED BY Orcladb$123456;

• GRANT CREATE SESSION TO test;

• GRANT DWROLE TO test;

• GRANT UNLIMITED TABLESPACE TO test;

• GRANT EXECUTE ON DBMS_LOCK TO test;

Check role privileges below:

• SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='DWROLE';

Check profile below:

• SELECT PROFILE FROM DBA_USERS WHERE USERNAME='TEST';

• SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_TYPE='PASSWORD';

Create profile name test_profile  below:

• CREATE PROFILE test_profile limit FAILED_LOGIN_ATTEMPTS 10 PASSWORD_REUSE_MAX 10 PASSWORD_LOCK_TIME 21 PASSWORD_LIFE_TIME UNLIMITED;

• SELECT * FROM DBA_PROFILES WHERE PROFILE='SOE_PROFILE' AND RESOURCE_TYPE='PASSWORD';

Make test user profile 

• ALTER USER test PROFILE test_profile;

• SELECT PROFILE FROM DBA_USERS WHERE USERNAME='TEST';

Pl/Sql package for web url mapping of new user test

BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
P_ENABLED => TRUE,
P_SCHEMA => 'TEST',
P_URL_MAPPING_TYPE => 'BASE_PATH',
P_URL_MAPPING_PATTERN => 'test',
P_AUTO_REST_AUTH => TRUE );
COMMIT;
END;
/ 

Copy Web url :

https://s5yv3egc6903wng-orcladb.adb.ap-mumbai-1.oraclecloudapps.com/ords/admin/_sdw/?nav=worksheet

• Replace admin with newly created schema test

https://s5yv3egc6903wng-orcladb.adb.ap-mumbai-1.oraclecloudapps.com/ords/test/sign-in/?r=_sdw%2F%3Fnav%3Dworksheet

Check the connection below: