Are you running in of your Oracle Databases in the Oracle Cloud? Would you like to easily connect to and work with those databases?
I’ll show you how this can be done, all in the browser, using our free Database Tools Service console.
Today’s post serves to fill in some of the blanks from my deep dive, with video. You can WATCH me creating users, connections for those users, private endpoints to reach my database, and a little more SQL worksheet demo.
Creating our new user and its connection
When you create a managed database in OCI, you’ll get by default one or more administrator accounts (SYS, SYSTEM, ADMIN) with a password you’ve provided.
Using that account, I’m going to login to my database from Cloud Shell and create a new database user, ‘YOUTUBE.’
Don’t have a Private Endpoint (PE) yet? That’s OK, we can create them on-the-fly
As you’re defining your connection, you’ll need to provide a PE. This allows our service to reach out to your database, on it’s VCN. Otherwise we’re going to have no network path…
If you don’s already have a PE for that particular VCN, we can add it for you. That looks like this.
Using our New Connection in a SQL Worksheet
Let’s import an Excel to a new table
Ok, let’s play with our new table
We can query the table with SQL, and we can use the built-in dictionary tools to browse and manage the database object.
Let’s open a SQL script from the Object Store
If you keep your handy database scripts in your object store, we can read and update those files directly from the SQL Worksheet!
Let’s change databases!
My BASE vm has a 19c Container Database, with one pluggable database (PDB.) I have connections for app users on the PDB (JEFF & YouTube) and a connection as SYSTEM to the Container Database.
I can run ‘show pdbs’ to see what that looks like from the PDB or my CDB. And then once I’m in the CDB, let’s query the data files for my tablespaces and get a SQL execution plan for that.
I should probably do a full-blown video tutorial, but…
Stay tuned, more video tutorials coming.
And even more exciting, we’re working with the Database teams to make the SQL worksheet available by default for your OCI Database subscriptions! How would a SQL Worksheet button look, say, here?
But I want MORE than a SQL Worksheet!
We’re working on that too, basically, having as much of SQL Developer Web in the OCI console as possible.
What we can offer will depend on if ORDS is available in that database. So what we also want to make available is a simple ‘deploy ORDS (and APEX)’ button to your database consoles via Database Tools. It would be 100% optional, but if you wanted the power of ORDS and APEX, you’d have it, easily & quickly!
But Jeff, this is TOO MUCH POWER!
Our service plays by all the OCI rules and POLICIES. You can create CONSOLE users that can login, and use whatever resources you want them to.
If you decide someone shouldn’t be able to create a connection, you can choose not to grant that ability. I’m logged in as the OCI Service administrator, so I’m ‘god’ and can do whatever I want, for any resource in my tenancy.