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.
ALTER TABLE ALLOCATE EXTENT ? http://download-west.oracle.com/docs...2a.htm#2057141
And because I know you won't believe me:
Code: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
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]
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
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.
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.
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
Chris,
No, that wasn't the thread.
Not that I'm avare of. Sorry.Quote:
Originally posted by Axr2
I vaguely remember Jmodic posting something about this
If you dont' want to put the data to a specific drive or .dbf file then consider having 4 different tablespaces, partition the table and put each of the partitions on different tablespaces...that way you have control over your data and datafiles...
HTH
>If you dont' want to put the data to a specific drive >or .dbf file then consider having 4 different tablespaces, >partition the table and put each of the partitions on >different tablespaces...that way you have control over >your data and datafiles...
Badrinathan, you are asking me to change my "requirements" to begin with! :) Defeats the very purpose..
I guess there is no easy way to accomplish I am trying to do. As I think about it, I can think of a few applications (and uses) if we could accomplish what I am seeking to do..oh well, I'll reserve that for another time. I'm gonna close this thread for now.
Thanks folks for your response.