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.
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.
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.’
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.
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 🙂
It shouldn’t be a war to manage your storage. Sounds like a great reality tv show though…
3 Comments
These don’t appear to be working for indexes. Are there competing properties I need to be aware of? When we generate DDL from the physical model only one template is used. We have checked the properties in the “generate” dialog!
it’s great I love it :o)
This is fantastic news. We currently use Erwin and we have never been able to find a way to do this. Worse, there are so many clicks involved to getting to the storage properties, it is a real pain to do one table at a time. I also love the idea of the Storage Templates so that level of detail can be centrally kept without the application developers all needing to worry about Initrans, etc. I’m really looking forward to evaluating SQL Dev Data Modeler further as I believe it may offer many advantages over our current tool (search being a huge one).