Partition Change Layout
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Partition Change Layout

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Question Partition Change Layout

    I have a huge table that is partitioned by month going all the way back to July 2000. As the partitions are backup they are made read only because the data never changes.

    I have to add a column to the table. How does this affect the read only tablespaces associated with this table? Do have have to make those partitions read write again and then back them up again??

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    what backup/restore methodology do you use?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    You will not be able to add a column to a table that has readonly tablespaces, as oracle will not be able to go back and add the pointer to that column to the old rows. You'll have to make them r/w, make the change, then r/o and backed up again.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jhmartin
    You will not be able to add a column to a table that has readonly tablespaces, as oracle will not be able to go back and add the pointer to that column to the old rows. You'll have to make them r/w, make the change, then r/o and backed up again.
    I'm thinking that was more of a guess than actual fact.

    Code:
    system@lx920> create tablespace ro datafile '/u01/app/oracle/product/920/oradata/lx920/ro01.dbf'
      2  size 10m autoextend on next 10m maxsize 2047m
      3  extent management local;
    
    Tablespace created.
    
    Elapsed: 00:00:01.27
    system@lx920> create tablespace rw datafile '/u01/app/oracle/product/920/oradata/lx920/rw01.dbf'
      2  size 10m autoextend on next 10m maxsize 2047m
      3  extent management local;
    
    Tablespace created.
    
    Elapsed: 00:00:00.63
    system@lx920> create table t (id number(10), name varchar2(20))
      2  partition by range (id)
      3  (partition p1 values less than (10) tablespace ro,
      4  partition p2 values less than (20) tablespace ro,
      5  partition p3 values less than (30) tablespace rw,
      6  partition p4 values less than (maxvalue) tablespace rw);
    
    Table created.
    
    Elapsed: 00:00:00.49
    system@lx920> declare
      2     i integer;
      3  begin
      4     for i in 1..50 loop
      5        insert into t values (i, to_char(i));
      6     end loop;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.22
    system@lx920> commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.00
    system@lx920> alter tablespace ro read only;
    
    Tablespace altered.
    
    Elapsed: 00:00:00.07
    system@lx920> delete from t where id = 1;
    delete from t where id = 1
                *
    ERROR at line 1:
    ORA-00372: file 9 cannot be modified at this time
    ORA-01110: data file 9: '/u01/app/oracle/product/920/oradata/lx920/ro01.dbf'
    
    
    Elapsed: 00:00:00.04
    system@lx920> alter table t add (the_dt date);
    
    Table altered.
    
    Elapsed: 00:00:00.16
    
    system@lx920> desc t
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     ID                                     NUMBER(10)
     NAME                                   VARCHAR2(20)
     THE_DT                                 DATE
    
    system@lx920> update t set the_dt = sysdate;
    update t set the_dt = sysdate
           *
    ERROR at line 1:
    ORA-00372: file 9 cannot be modified at this time
    ORA-01110: data file 9:
    '/u01/app/oracle/product/920/oradata/lx920/ro01.dbf'
    
    
    Elapsed: 00:00:00.05
    system@lx920> update t set the_dt = sysdate where id > 30;
    
    20 rows updated.
    
    Elapsed: 00:00:00.01
    system@lx920> commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.01
    system@lx920> alter table t drop partition p1;
    
    Table altered.
    
    Elapsed: 00:00:00.22
    system@lx920> l
      1  select segment_name, partition_name, tablespace_name
      2  from dba_segments
      3* where segment_name = 'T'
    system@lx920> /
    
    SEGMENT_NAME         PARTITION_NAME                 TABLESPACE_NAME
    -------------------- ------------------------------ ------------------------------
    T                    P2                             RO
    T                    P3                             RW
    T                    P4                             RW
    
    Elapsed: 00:00:00.05
    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."

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