I did a little demo of sharing a tablespace between two databases a few days back – you can see the details here or by just scrolling down if you’re on the home page.
To avoid clouding the demonstration I omitted something in the details, but I’ll share that now, because it could be critical depending on how you currently use transportable tablespaces.
Let me do the most basic of examples now, transporting a tablespace from one database to another:
First, we make our tablespace read only, and Datapump export out the metadata
I’ve always been worried about taking a script that is fine to run in my non-production environments (in particular a DROP script) and accidentally running it in a Production environment, shortly followed by the typing up of a fresh resume to look for a new job once the mistake is discovered
This question came in on AskTom, yielding a very interesting result when it comes to DDL triggers. To set the scene, I’ll first create a table called T which is just a copy of SCOTT.EMP
Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table
SQL> create table parent ( p int, constraint PAR_PK primary key (p) ); Table created. SQL> create table child ( c int, 2 p int 3 ); Table created. SQL> alter table child add constraint fk1 foreign key ( p ) references parent ( p ); Table altered.
That is all as we would expect, and similarly, if I inadvertently try to add the same foreign key constraint, I’ll get an error
When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below
If you are on any version of the database past 10.2.0.4, then savvy DBA’s may have noticed the following message popping up occasionally in their trace files
Warning: log write time 540ms, size 444KB
In itself, that is quite a nice little addition – an informational message letting you know that perhaps your log writer performance is worth closer investigation. MOS Note 601316.1 talks a little more about this message.
So let’s say you have seen this warning, and you are interested in picking up more information. Well… you could start scanning trace files from time to time, and parsing out the content etc, or do some analysis perhaps using Active Session History, but given that these warnings are (by default) triggered at above 500ms, there’s a chance you might miss them via ASH.
Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer.
Create a tablespace to hold your history, and create a flashback archive using that space allocation.
Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year .
Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table
Going back to the invention of the graphical user interface (GUI) in the 1970s, there has been tension between the advocates of the magical pointy-clickety GUI and the clickety-clackety command-line interface (CLI).
Part of it is stylistic… GUI’s are easier, faster, more productive.
Part of it is ego… CLI’s require more expertise and are endlessly customizable.
Given the evolutionary pressures on technology, the CLI should have gone extinct decades ago, as more and more expertise is packed into better and better GUI’s. And in fact, that has largely happened, but the persistence of the CLI can be explained by four persistent justifications…
I’m a Leaf on the Wind, Watch How I Soar.
This is one of my favorite lines from the movie Serenity.