Tag: tablespaces

The phantom tablespace

(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL? OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick I’ve created a…

Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database.  From the docs: Here is an example of that in…

Sharing a tablespace between 2 databases

I was reading an interesting discussion today about multiple databases each containing large amounts of read-only data.  If that read-only data is common, then it would make sense to have a single copy of that data and have both databases share it. Well, as…

Datapump TTS quick tip

I was trying to transport a large number of tablespace datafiles from one database to another, and impdp kept crashing with “PL/SQL: numeric or value error: character string buffer too small.” It turns out there is a limit on what you can put on…

dropping tablespaces and queues – not happy companions

grrr… SQL> drop tablespace MY_TSPACE including contents; drop tablespace MY_TSPACE including contents * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: Inappropriate utilities used to perform DDL on AQ table MY_SCHEMA.MY_QUEUE_TABLE You would think that if you ask to…

Inappropriate behaviour

You pick up little funny things in the day to day with Oracle. Like this one when you try to drop a tablespace with a queue table in it: drop tablespace MY_TSPACE including contents * ERROR at line 1: ORA-00604: error occurred at recursive…