ThatJeffSmith

Managing Storage Properties for Multiple Tables in Oracle SQL Developer Data Modeler

There were more than a few questions I didn’t get a chance to answer in last week’s webcast with @ODTUG, ‘Data Modeling 101 with Oracle SQL Developer.’ If you’re a member of ODTUG, then you can watch or download the webcast. If you’re NOT a member, you can still sign up to attend KSCOPE13 in New Orleans and watch my session live along with a few hundred others.

So anyways, on to one of those questions I didn’t answer. Storage is a physical property for a table in Oracle SQL Developer Data Modeler. Things like the tablespace, bufferpool, or the PCTFREE property are defined in the physical model – which we associate with your relational model.

You can have one or more physical models for each of your relational models.

You can have one or more physical models for each of your relational models.

I have a 100 or so tables. It would be a real bummer to have to set all of these manually, one-by-one.

Can you set the physical storage properties of multiple tables at once?

Yes, yes, yes. There’s three different ways you can achieve this. But let’s look at the most straight-forward manner, Storage Templates.

Using Storage Templates

If you look at the entire tree underneath the Physical Model, you’ll notice a node labeled ‘Storage Templates.’ In the image below you’ll notice I have seven storage templates with really generic names. These have been generated automatically when I performed a Data Dictionary Import. SQL Developer has noticed that there are 7 distinct sets of storage options for all of the objects contained in the import.

Storage Templates - exactly what they sound like.

Storage Templates – exactly what they sound like.

You can tweak these settings to your liking and they’ll be applied to all the objects assigned to said template. You can add and remove objects on the appropriate page, e.g. ‘Tables’, ‘Indexes’, ‘Materialized Views.’

Assigning objects to a storage template

Assigning objects to a storage template

If you have built a model from scratch, then you’ll not have any storage templates setup ‘out of the box.’ But you could take the time and create a few and then add your objects to each template.

Or.

You could keep it ad-hoc and just use the ‘Propagate’ button.

The ‘propagate’ button allows you to take the current storage properties and apply it to one or more other objects. Very simple, very fast, less ‘icky’ design stuff to worry about. But if you want to manage the different sets of storage properties, you’ll be coming back to the Storage Templates.

Copy your storage properties to one or more other objects with the 'Propagate' button.

Copy your storage properties to one or more other objects with the ‘Propagate’ button.

I mentioned there were 3 ways, so I better show you the final option.

Global Search and Replace in Oracle SQL Developer Data Modeler v3.3

So a pretty exciting new feature in version 3.3 is the Search feature. I can do an advanced search and identify all of the tables in my physcial model that have ‘USERS’ as a tablespace. I can then use the Common Properties Editor to change the tablespace, or any other storage property for all of those objects in one big swoop – handy, if they aren’t already part of a Storage Template :)

SQL Developer Data Modeler version 3.3 has a new and improved Search (and Replace!)

SQL Developer Data Modeler version 3.3 has a new and improved Search (and Replace!)

It shouldn’t be a war to manage your storage. Sounds like a great reality tv show though…