Learning By Breaking I've spent the last couple of days building and rebuilding my sandbox database.

I can't get it just right.

Last night while running my scripts, none of the foreign key clauses would work. Primary key doesn't exist? WTF?

Do a quick check on the count of indexes...it's about 10% of what it should have been.

Oh yeah, when using REMAP_TABLESPACE, it helps to include the index tablespaces as well. $#*@~!

So I queried all the index tablespaces and put them into my parameter file for impdp.

Only to have my database freeze about 70% of the time. Being just a dba and not a DBA, I figured I'd just add a datafile. OK, so the first time I created it is was 1GB. I didn't bother to resize it.

Then it hung up again. (Of course I'm not bothering to find the root cause of the issue, I'm just brute forcing it). Somewhere in the index creation (reading from the console output anyway).

Killed it again.

Cleaned out the schema again.

Rinse and repeat a couple more times.

Then I decided to drop a datafile, the small one I created up above. Yes, I could have resized it...but I'm just a rookie. Used the OFFLINE DROP, shutdown the database and deleted the datafile.

Reran the impdp.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'USERS02.DBF'

Oops.

OK, let's drop the tablespace and try again.

DROP TABLESPACE users INCLUDING CONTENTS;

ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

OK, let's create a new tablespace

CREATE TABLESPACE USERS2 DATAFILE 'USERS2_01.DBF' SIZE 10G AUTOEXTEND ON;

Now I'll make that one the default

ALTER DATABASE DEFAULT TABLESPACE USERS2;

Now I'll drop the USERS tablespace

DROP TABLESPACE users INCLUDING CONTENTS;

ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces

Killing me...

So I dropped all the users (just a reminder, this is just a sandbox) and tried it again.

SYS@TESTING>DROP TABLESPACE users INCLUDING CONTENTS;

Tablespace dropped.

Finally!

For whatever reason, I'm a glutton for punishment. I don't like the look of "USERS2" so I want to go back to "USERS." Let's go through this again.

ALTER DATABASE DEFAULT TABLESPACE USERS;

Database altered.

DROP TABLESPACE users2 INCLUDING CONTENTS;

Tablespace dropped.

Add a second datafile just for fun:

ALTER TABLESPACE USERS ADD DATAFILE 'USERS_02.DBF' SIZE 10G AUTOEXTEND ON;

Done.

Obviously no one should let me do this sort of thing anywhere near a production database. I know just enough to be really, really dangerous. It is find having to go through this (painful) exercise once in awhile in a closed environment. Helps to understand a little bit about how the database works. I'm not sure what exactly I did learn, other than I am a moron...but it was...fun.

Labels: dba, funny

  ¶ 9/21/2009 06:37:00 PM