Oracle Database Commands and Practical Examples: April 2021

Friday, 16 April 2021

OCI - Create object storage buckets

Create object storage buckets in OCI console

Welcome to Oracle Cloud Shell.

Your Cloud Shell machine comes with 5GB of storage for your home directory. Your Cloud Shell (machine and home directory) .

Type `help` for more info.

varunyadav@cloudshell:~ (ap-mumbai-1)$ oci os ns get

{

"data": "bmdsoh48o0kf"

}

varunyadav@cloudshell:~ (ap-mumbai-1)$

Select Create Bucket option. 20GB is the limit on free edition

Create Bucket and fill details below.

Bucket option delete as well below screenshots

Upload file fill details below.

File details

We try to create bucket using CLI commands but do not have permission on free edition.

OCI - Autonomous connection 19c database using 12c oracle client and database

Autonomous connection 19c database using 12c oracle client and database

Example of connecting autonomous 19c database using oracle database 12.2 and also oracle client 12.1 form OS window we can do same step in linux OS as well.
Web Blog URL guidance : download Instance wallet
Copy existing sqlnet.ora and tnsnames.ora entry from oracle database and transfer rest files on network /admin location.

Add entry below in sqlnet.ora file

sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))

SSL_SERVER_DN_MATCH=yes

Add entry below in tnsnames.ora file.

Example: Service name below.

Connect Autonomous database 19c using existing 12.2 window database.

Apply same step on oracle client12c

Copy instance wallet zip file on /network/admin location

OCI - Sql developer connection using instance wallet 19c

OCI Cloud - Sql developer connection using instance wallet

Download instance wallet

Open sql Developer. Here we have installed version 20 above.

Provide credential and select connection type : cloud wallet

Select configure file option below:

Select connections' options below based on priority work

Execute query after getting connections

OCi - Instance wallet download

OCI Cloud - E wallet download

Web blog for reference : oracle oci connection
Login oci and choose DB connection below:

Select instance wallet

Create new password

Check the wallet zip files details below

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",