Anyway to force writes to a particular datafile of a Tablespace?

[Axr2]

Folks,
Can you please suggest a creative way that I could force writes to a particular datafile of a tablespace?

To elaborate :
Tablespace BIGASS consists of 4 EMPTY (no data that is) 800M datafiles. say bigass01.dbf,02,03,04.dbf.

I want to simulate writes to 04.dbf without filling up 01,02,03..or taking them (1,2,3.dbf) offline.

This is a test database..and I would be willing to tamper with the dictionary tables if required! Just tell me where and what to plug..:-)

Thanks in advance.

[marist89]

ALTER TABLE ALLOCATE EXTENT ? http://download-west.oracle.com/docs...2a.htm#2057141

[marist89]

And because I know you won't believe me:

SQL> create tablespace foo datafile 'e:\oradata\nt817\foo1.dbf' size 5M;

Tablespace created.

SQL> alter tablespace foo add datafile 'e:\oradata\nt817\foo2.dbf' size 5M;

Tablespace altered.

SQL> create table bar (x number(10)) tablespace foo;

Table created.

SQL>  alter table bar allocate extent (datafile 'e:\oradata\nt817\foo2.dbf');

Table altered.

SQL>  alter table bar allocate extent (datafile 'e:\oradata\nt817\foo1.dbf');

Table altered.

SQL> l
  1  select file_id, count(*) from dba_extents
  2  where owner = 'SYSTEM'
  3  and segment_name = 'BAR'
  4* group by file_id
SQL> /

   FILE_ID   COUNT(*)
---------- ----------
         7          2
         8          1

SQL> /

   FILE_ID FILE_NAME
---------- ----------------------------------------
         1 E:\ORADATA\NT817\SYSTEM01.DBF
         2 E:\ORACLE\ORADATA\NT817\RBS01.DBF
         3 E:\ORADATA\NT817\USERS01.DBF
         4 E:\ORADATA\NT817\TEMP01.DBF
         5 E:\ORADATA\NT817\TOOLS01.DBF
         6 E:\ORADATA\NT817\INDX01.DBF
         7 E:\ORADATA\NT817\FOO1.DBF
         8 E:\ORADATA\NT817\FOO2.DBF

[Axr2]

Jeff,
I'm not sure I understand..or if this addresses my concern.

1 select file_id, count(*) from dba_extents
2 where owner = 'SYSTEM'
3 and segment_name = 'BAR'
4* group by file_id
SQL> /

FILE_ID COUNT(*)
---------- ----------
7 2
8 1

You still have BAR segments in foo1 (file_id 7).

To reiterate - In this context, I do NOT want to touch foo1.dbf AT ALL.

Thanks.

PS:
> And because I know you won't believe me:
Huh..what was that?!!

[Edited by Axr2 on 10-18-2002 at 04:09 PM]

[pando]

if what you want is avoid Oracle use one datafile in a tablespace then you cannot

if what you want is assign manually space to a segment in a specific datafile then use jeff's method

[stecal]

There are some other "issues" involved here - no need to go into them.


Sometimes I crack me up. I don't care if anyone else laughs. It is important that I do.

[Axr2]

Steve,

> if what you want is avoid Oracle use one datafile in a tablespace then you cannot

YES, this is EXACTLY what I had intended to do.

I thought tampering with a certain dictionary table (I forget which one..I vaguely remember Jmodic posting something about this) should achieve the desired result..

Like I said..this is a test instance..and I can get down and dirty with it (if need be) .

Thanks again.

[chrisrlong]

Is this the post your are talking about?
http://www.dbasupport.com/forums/sho...threadid=30132

They appear to be talking about
alter tablespace tablespace-name read only

- Chris

[Axr2]

Chris,
No, that wasn't the thread.

[jmodic]

Originally posted by Axr2
I vaguely remember Jmodic posting something about this

Not that I'm avare of. Sorry.