Jul 10, 2018 9:04:54 AM by Deepak Vohra
Oracle Database Exadata Express Cloud Service is a managed service for Oracle Database 12c R2 Enterprise Edition running on Oracle Exadata. Exadata Express is a lightweight version of the full-featured Oracle Exadata, suitable for small- to medium-scale data. Exadata Express provides functionality for application development, data analytics, and database optimization. Exadata Express opens a pluggable database by default with the provision to create multiple schemas. Some of the salient application development features are JSON document storage, schema-less documents storage, and access to the Exadata Express service with Simple Oracle Document Access API, Oracle REST Data Services (ORDS), Oracle Application Express 5 and 5.1, and SQL Developer 4. In three articles we shall learn to use Oracle Database Exadata Express Cloud Service with Toad for Oracle. The three articles have the following sections.
Setting the Environment
Creating an Exadata Instance
Setting Admin Password
Enabling Client Access
Creating a Schema
Downloading Client Credentials
Setting Permissions on Credentials Files
Creating a SQL Plus Connection
Creating a Connection
Finding Data Duplicates
Using Schema Browser
Exporting dataset to HTML
Running a Query
Exporting Result set to Excel
Creating a Report
Modifying a Report
Deleting Exadata Instance
Download and install Toad for Oracle. Create an Oracle Cloud account. Download Oracle Instant Client version 18.104.22.168.0 Basic Package zip file instantclient-basic-windows.x64-22.214.171.124.0.zip and SQL*Plus Package zip file instantclient-sqlplus-windows.x64-126.96.36.199.0.zip from http://www.oracle.com/technetwork/topics/winx64soft-089540.html. Create a directory C:instantclient_12_2 and extract the contents of the zip files to the directory. The subdirectories are not to be kept when the zip files are extracted. Only the files and the vc14 directory are to be extracted to C:instantclient_12_2 such that all the extracted files and the vc14 directory are at the top level within the C:instantclient_12_2 directory, as shown in Figure 1. The sqlplus.exe application and the other SQL*Plus files are also directly in the C:instantclient_12_2 directory.
Figure 1. Directory Structure
We also need to modify the PATH environment variable to add the C:instantclient_12_2 directory path.
Login to the Oracle Cloud Dashboard with the URL of the format https://myservices.us.oraclecloud.com/mycloud/<user>/faces/dashboard.jspx that is sent to a user when a new account is created. Click on Create Instance as shown in Figure 2.
Figure 2. Create Instance
In the Create Instance dialog click on Create for the Exadata Express service as shown in Figure 3.
Figure 3. Exadata Express>Create
In the Instance Details page specify an Instance Name (oraexadata), select the default Plan Exadata Express Cloud Service, and select a Database Shape as shown in Figure 4. The other fields to be filled are Email, User Name, First Name and Last Name.
Figure 4. Instance Details
Click on Create to create an instance as shown in Figure 5.
Figure 5. Instance Details>Create
In Confirmation click on Create as shown in Figure 6.
Figure 6. Confirmation>Create
The oraexadata instance starts to get created, as indicated by the message in Figure 7.
Figure 7. Instance oraexadata starts to get created
An Exadata Express service instance gets created and listed on the Dashboard, as shown in Figure 8.
Figure 8. Exadata Express Instance in Dashboard
The Service Instances section on the instance Overview page lists the detail about the oraexadata instance. Click on the Oracle Service Console link as shown in Figure 9 to open the service console.
Figure 9. Oracle Service Console link
The Oracle Service Console page gets displayed, as shown in Figure 10.
Figure 10. Oracle Service Console
The default admin user for the Exadata Express is PDB_ADMIN. To set the password for the admin user click on Manage in the Service Console as shown in Figure 11.
Figure 11. Manage
In Manage click on the Admin Password link as shown in Figure 12.
Figure 12. Admin Password
In the Admin Password dialog specify a New Password and a Confirm Password and click on Set Password as shown in Figure 13.
Figure 13. Admin Password
A Success dialog indicates that the password has been set as shown in Figure 14. Click on Close.
Figure 14. PDB_ADMIN Password Set
By default client access to the database is not enabled. To enable access click on Client Access as shown in Figure 15.
Figure 15. Client Access
In the Client Access dialog click on Enable Client Access as shown in Figure 16.
Figure 16. Client Access>Enable Client Access
The client access gets enabled as indicated by the Enabled label in Figure 17.
Figure 17. Client Access Enabled
One of the benefits of using Exadata Express is the provision to create multiple schemas. To create a schema click on Database Schema in Manage as shown in Figure 18.
Figure 18. Manage>Database Schema
In Create Database Schema specify Schema name (ORCL) and specify Password and Confirm Password for the schema. Click on Create Database Schema as shown in Figure 19.
Figure 19. Create Database Schema
The database schema ORCL gets created as indicated in the Success message dialog shown in Figure 20. Click on Close.
Figure 20. Schema ORCL created
To download client credentials click on Client Credentials as shown in Figure 21.
Figure 21. Client Credentials
In Client Credentials specify Password and Confirm password and click on Download as shown in Figure 22.
Figure 22. Client Credentials>Download
A client_credentials.zip file gets downloaded. Create a directory structure C:instantclient_12_2networkadmin and extract the files in the client_credentials.zip to the C:instantclient_12_2networkadmin directory as shown in Figure 23.
Figure 23. Credentials Files
The credentials files include the tnsnames.ora and sqlnet.ora configuration files. None of the files is to be modified. The tnsnames.ora file should not even be opened and reformatted, as the file is generated in a specific syntax which, if modified, makes the file unusable. Add an environment variable TNS_ADMIN with value as directory path C:instantclient_12_2networkadmin.
We need to modify permissions on the credentials files to make the files accessible for the OS user that is logged into the local machine. The tnsnames.ora and sqlnet.ora files’ permissions are not to be set. As an example, to modify the permissions of the ewallet file right-click on the file and select Properties. In the ewallet Properties dialog select the Security tab and select the Users group and click on Edit as shown in Figure 24.
Figure 24. ewallet Properties>Security>Edit
In the Permissions for ewallet select Allow in Permissions for Users and click on Apply as shown in Figure 25.
Figure 25. Setting Permissions for ewallet
The tnsnames.ora file consists of a net service entry dbaccess for the Oracle Database running on Exadata Express. Run the following command to connect to Oracle Database using SQL*Plus. If the C:instantclient_12_2 directory has been added to PATH, the command may be run from any directory; but if the directory has not been added to PATH, change directory (cd) to the C:instantclient_12_2 directory to run the command.
Specify a password for PDB_ADMIN user at the command prompt. A connection gets established with Oracle Database 12c.
SQL*Plus: Release 188.8.131.52.0 Production on Sun Apr 1 19:19:05 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Oracle Database 12c Enterprise Edition Release 184.108.40.206.0 - 64bit Production
The pluggable database is already running, which may be verified with the startup command.
ORA-65019: pluggable database YHTPGDZTLFV already open
The output from the preceding SQL*Plus commands is shown in Figure 26.
Figure 26. SQL*Plus Commands output
In this first of three articles on using Oracle Database Exadata Express Cloud Service with Toad for Oracle we have created an Exadata Express instance. In subsequent articles we shall connect to the database with Toad for Oracle and use some of its features.
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.