Oracle Database Commands and Practical Examples

Friday 16 April 2021

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:

OCI -Shell command

OCI -Shell command

Oracle Web link : OCI CLI Commands

type: oci cli commands

OCI CLI command example with jason output:

varunyadav@cloudshell:~ (ap-mumbai-1)$ oci db autonomous-database get --autonomous-database-id ocid1.autonomousdatabase.oc1.ap-mumbai-1.abrg6ljrhpgapdvvqp4azumv5geam6dpw3k7plqbij6ix643jwldsp4353tq

{

"data": {

"apex-details": {

"apex-version": "20.2.0.00.20",

"ords-version": "20.4.4.068.1249"

"are-primary-whitelisted-ips-used": null,

"autonomous-container-database-id": null,

"available-upgrade-versions": [],

"backup-config": {

"manual-backup-bucket-name": null,

"manual-backup-type": "NONE"

"compartment-id": "ocid1.tenancy.oc1..aaaaaaaa3lrct7pisw4bc3szl6jubecnxyqfgyfrrc7aapifurbmgw2tjolq",

"connection-strings": {

"all-connection-strings": {

"HIGH": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_high.adb.oraclecloud.com",

"LOW": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_low.adb.oraclecloud.com",

"MEDIUM": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_medium.adb.oraclecloud.com",

"TP": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_tp.adb.oraclecloud.com",

"TPURGENT": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_tpurgent.adb.oraclecloud.com"

"dedicated": null,

"high": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_high.adb.oraclecloud.com",

"low": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_low.adb.oraclecloud.com",

"medium": "adb.ap-mumbai-1.oraclecloud.com:1522/s5yv3egc6903wng_orcladb_medium.adb.oraclecloud.com"

"connection-urls": {

"apex-url": "https://S5YV3EGC6903WNG-ORCLADB.adb.ap-mumbai-1.oraclecloudapps.com/ords/apex",

"machine-learning-user-management-url": "https://adb.ap-mumbai-1.oraclecloud.com/omlusers/tenants/OCID1.TENANCY.OC1..AAAAAAAA3LRCT7PISW4BC3SZL6JUBECNXYQFGYFRRC7AAPIFURBMGW2TJOLQ/databases/ORCLADB/index.html",

"sql-dev-web-url": "https://S5YV3EGC6903WNG-ORCLADB.adb.ap-mumbai-1.oraclecloudapps.com/ords/sql-developer"

"cpu-core-count": 1,

"data-safe-status": "NOT_REGISTERED",

"data-storage-size-in-gbs": 20,

"data-storage-size-in-tbs": 1,

"db-name": "ORCLADB",

"db-version": "19c",

"db-workload": "OLTP",

"defined-tags": {

"Oracle-Tags": {

"CreatedBy": "varunyadav.dba@gmail.com",

"CreatedOn": "2021-03-22T12:46:35.319Z"

}

"display-name": "ORCLADB",

"failed-data-recovery-in-seconds": null,

"freeform-tags": {},

"id": "ocid1.autonomousdatabase.oc1.ap-mumbai-1.abrg6ljrhpgapdvvqp4azumv5geam6dpw3k7plqbij6ix643jwldsp4353tq",

"infrastructure-type": null,

"is-access-control-enabled": null,

"is-auto-scaling-enabled": false,

"is-data-guard-enabled": false,

"is-dedicated": false,

"is-free-tier": true,

"is-preview": false,

"is-refreshable-clone": null,

"key-store-id": null,

"key-store-wallet-name": null,

"license-model": "LICENSE_INCLUDED",

"lifecycle-details": null,

"lifecycle-state": "AVAILABLE",

"nsg-ids": null,

"open-mode": "READ_WRITE",

"operations-insights-status": "NOT_ENABLED",

"permission-level": "UNRESTRICTED",

"private-endpoint": null,

"private-endpoint-ip": null,

"private-endpoint-label": null,

"refreshable-mode": null,

"refreshable-status": null,

"role": null,

"service-console-url": "https://adb.ap-mumbai-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAA3LRCT7PISW4BC3SZL6JUBECNXYQFGYFRRC7AAPIFURBMGW2TJOLQ&database_name=ORCLADB&service_type=ATP",

"source-id": null,

"standby-db": null,

"standby-whitelisted-ips": null,

"subnet-id": null,

"system-tags": {

"orcl-cloud": {

"free-tier-retained": "true"

}

"time-created": "2021-03-22T12:46:35.322000+00:00",

"time-deletion-of-free-autonomous-database": null,

"time-maintenance-begin": "2021-04-17T02:00:00+00:00",

"time-maintenance-end": "2021-04-17T06:00:00+00:00",