Anyway to force writes to a particular datafile of a Tablespace?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    Join Date
    Oct 2002
    Posts
    807

    Unhappy

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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 Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    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]

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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.

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    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.

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Oct 2002
    Posts
    807
    Chris,
    No, that wasn't the thread.

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Axr2
    I vaguely remember Jmodic posting something about this
    Not that I'm avare of. Sorry.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width