Oakies Blog Aggregator

You’ve got a huge table right? Massive! Immense! And then something bad happens. You get asked to remove one of the columns from that table.

“No problem” you think. “I won’t run the ‘drop column’ command because that will visit every block and take forever!”

So you settle on the perfect tool for such a scenario – simply mark the column as unused so that it is no longer available to application code and the developers that write that code.

But there’s a catch that not many people know about. SET UNUSED is meant to be just a trivial database dictionary operation. We don’t touch the data, we only manipulate the column definition and thus an execution of SET UNUSED should be instantaneous. This is indeed the case most of the time, but as the example below shows – the way the column was added to the table, can have a bearing on what it costs to have that column removed.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--
-- large-ish table to monitor the impact
--
SQL> create table t1
  2   as
  3   select d.*
  4   from dba_objects d,
  5   ( select 1 from dual connect by level <= 100);

Table created.

SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
   8713700

Elapsed: 00:00:01.92

--
-- Take an existing column to UNUSED ... instantaneous!
--
SQL> alter table t1 set unused column created;

Table altered.

Elapsed: 00:00:00.06


--
-- Add a new column, and then make it UNUSED ... instantaneous!
--
SQL> alter table t1 add new_col1 timestamp;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col1;

Table altered.

Elapsed: 00:00:00.00


--
-- Add a new columns with a not null default, and then make it UNUSED ... careful!
--
SQL> alter table t1 add new_col2 timestamp default sysdate not null;

Table altered.

Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col2;

Table altered.

Elapsed: 00:01:35.39

You will not get the same issue in 12c.

SQL> select banner from v$version;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1  nologging
  2   as
  3   select d.*
  4   from dba_objects d,
  5   ( select 1 from dual connect by level <= 100);

Table created.

SQL>
SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
   7951500

Elapsed: 00:00:02.20
SQL> alter table t1 set unused column created;

Table altered.

Elapsed: 00:00:00.08
SQL>
SQL> alter table t1 add new_col1 timestamp;

Table altered.

Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col1;

Table altered.

Elapsed: 00:00:00.00
SQL>
SQL> alter table t1 add new_col2 timestamp default sysdate not null;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col2;

Table altered.

Elapsed: 00:00:00.01
SQL>

Its quite common that the first time you work with a feature in a cloud interface, it can appear so foreign until you’ve done it a few times.  If it’s a task that you may not do often or only once, it can be downright painful.  It doesn’t matter who the cloud provider is or the application, we all have felt the pain of this type of situation and why some of us even started blogging…:)  Until its familiar, it may not feel comfortable and you may not even like how it works.

As a newer employee at Microsoft, I’m often learning right along with my customers.  Lucky for me, I learn quick, but I feel their pain and if something slows me down, then I know it will for the customer, too.  One of the tasks that I noticed tripped us up in configuring SQL Database on Azure was configuring the firewall for SSMS, (SQL Server Management Studio) or Visual Studio, (VS) access.  We all know that having access for SSMS is important for many DBAs to have a single pane of glass for database management and for me, well, I like to do everything in Visual Studio-  I’m just strange that way.

The Set up

To configure access for SSMS to access SQL Database, log into your Azure portal, then click on the database you wish to configure access for, (sorry folks, I need this play environment for a while, so I’ve removed some info from the screenshots):

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 1400w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 2100w" sizes="(max-width: 650px) 100vw, 650px" data-recalc-dims="1" />

Click on Set Server Firewall at the top, which will bring you to the following screen:

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 1447w" sizes="(max-width: 650px) 100vw, 650px" data-recalc-dims="1" />

The one thing I really like about Azure is that it displays your IP address in the console, assuming this may be the information you require.  If you are attempting to add this to access a local installation of VS or SSMS, simply enter in an identifying name for your rule and then add the IP address or a range of IP Addresses in the Start IP and End IP field, then click Save.

If you need to retrieve the IP address for another host or workstation, you can do this by opening the command prompt and type in ipconfig.  From there, look at the IPv4 address:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 1400w" sizes="(max-width: 650px) 100vw, 650px" data-recalc-dims="1" />

Now know that if you work from numerous WiFi, MiFi and other network connections, your IP Address will change.  If your IP address changes, you’ll need to add a new rule or if it’s a temporary location, just remove the rule and create a new one for your new access point to keep things uncluttered.

Connect Time

Once this is performed, it’s time to connect to your Azure SQL Database.  If you’re asking me how you find that information, let’s go back to our main page for the database:

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/08/Conf_firewall... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/08/Conf_firewall... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/08/Conf_firewall... 1400w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/08/Conf_firewall... 2100w" sizes="(max-width: 650px) 100vw, 650px" data-recalc-dims="1" />

Clock on the link for  Show database connection strings and it will take you to the following, displaying the JDBC.net connection info in the second tab:

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 768w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 1400w" sizes="(max-width: 650px) 100vw, 650px" data-recalc-dims="1" />

Well, look there, all you need is the first thing displayed after Server=.  Note that it’s a “,” and not a “:” between the server name and the port, as you’ll need to do the same in SSMS or VS when you enter the connection.  Your user name has an addition too:  Username@domain.com@sql database name.

After you enter this, use the password you configured your database with.  If you forgot, the main page in the portal has a “reset password” link for you to update it.

You’ll then connect with something that looks like the following:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/08/conf_firewall... 1196w" sizes="(max-width: 519px) 100vw, 519px" data-recalc-dims="1" />

I can then switch to a user database and manage Azure SQL Databases just as I have if they were on-prem.  Enjoy!

Tags:  azure, Firewall

Del.icio.us

Facebook

TweetThis

Digg

StumbleUpon

Here are two blog posts on the Databases at CERN blog:

1. Oracle Cloud Infrastructure API Keys and OCID
2. Oracle Cloud: start/stop automatically the Autonomous Databases

The idea is to control the Autonomous Databases from the command line, with no installation, just an easy function downloaded from the documentation and customized with environment variables, in order to automate the start and stop of the services. The first post shows how to get all those OCIDs and the second one how to use this oci-curl() function to stop all started ADW or ATP services.

In summary, define the following variables with your values:

privateKeyPath=~/.oci/oci_api_key.pem
keyFingerprint="05:56:ee:89:19:e7:16:03:9b:00:c3:91:cf:a6:9d:e9"
authUserId=ocid1.user.oc1..aaaaaaaaflzrbfegsz2dynqh7nsea2bxm5...
tenancyId=ocid1.tenancy.oc1..aaaaaaaac6guna6l6lpy2s6cm3kguijf...
compartmentId=ocid1.tenancy.oc1..aaaaaaaac6guna6l6lpy2s6...
endpoint=database.us-ashburn-1.oraclecloud.com
apiVersion=20160918

Load the oci-curl() function:

source <( curl https://docs.cloud.oracle.com/iaas/Content/Resources/Assets/signing_sample_bash.txt | grep -vE "(local tenancyId|local authUserId=|local keyFingerprint|local privateKeyPath=)" | sed -e '1s/^.*#/#/' )

List your autonomous services to check that all is correctly set:

for service in autonomousDatabases autonomousDataWarehouses ; do oci-curl $endpoint get "/$apiVersion/$service?compartmentId=$compartmentId" | jq -r '.[] | [ .dbName , .lifecycleState , .id ]  | @tsv'; done

And the result is:

All details in the on the Databases at CERN blog:

1. Oracle Cloud Infrastructure API Keys and OCID
2. Oracle Cloud: start/stop automatically the Autonomous Databases

The usage of this oci-curl() function was inspired by Yasin Baskan blog post:

https://blogs.oracle.com/datawarehousing/managing-autonomous-data-warehouse-using-oci-curl

In the good old days, database-agnostic applications were written using drivers that implemented the Microsoft Open Database Connectivity (ODBC) API, especially on Windows. Much like JDBC, ODBC provided developers with a single, interoperable, C-based programming language interface that made it possible for applications to access data from a variety of database management systems. When developing an […]