Re: getting data out of system tablespace

In article <X8uB3.7$8h7.1639_at_news.uswest.net>, "Larry Pettit" <larry.pettit_at_ps.net> wrote:
>I received an import file from another system that I needed to load. I
>created another user id, assigned that user id to it's own tablespace
> user_data) and even did the import load under that user id, but it still
>put it into the system tablespace. How can I move it into another
>tablespace?
>

The table you imported must have been stored in the source database's SYSTEM tablespace, and it inherited that unfortunate characteristic when you loaded it into the target database. You should be able to get it out of there by exporting it (unless you still have the old .dmp you loaded); creating the table, indexes and anything else that needs to be stored in the proper tablespace, dropping the version in SYSTEM and importing with IGNORE=Y. How to create it in the proper tablespace? First choice is to use a tool that generates the DDL you need, edit it to change the tablespace and run it.  Alternatively you could use the CREATE TABLE command with the AS SELECT * FROM old_table option and specify a WHERE clause that excludes all the rows. But if the table has indexes you'll have to chase them down and create them too, or the import will put them wherever they were in the source database (SYSTEM I would guess, from what you've described). Some tools will include all the dependent objects in the generated DDL.

Paul de Anguera | "You can't write a chord ugly enough to say
Reply to:       | what you want to say sometimes, so you have to
deanguer@       | rely on a giraffe filled with whipped cream."
quidnunc.net    | - Frank Zappa