Re: Drop Partitioning

Aw nuts...
 Im thinking instead of doing:
drop table base.invoice cascade constraints; I should have done:  alter table base.invoice drop partition <partition_name>;   can someone confirm?
 On 8/4/05, Bryan Wells <bunjibry_at_gmail.com> wrote:
>
> Here is what I did...
> I was able to get it to work. well kinda. I removed all the physical
> datafiles to create a little more disk space, however i still see the
> partitioned tablespaces in the instance, eventhough they are offline. I cant
> get them to drop???
> Please keep in mind before heckling me, im fairly new at this and
> learning the best way I can. Ass to the fire!
> add disk space to following tablespaces so that there is 2 times the
> amount available:
>
> - CONSOL_INVOICE_FY00 <add 2386M>
> - CONSOL_INVOICE_FY01 <add 2346M>
>
> CONSOL_INVOICE_FY02 <add 2426M>
> CONSOL_INVOICE_FY03 <add 2406M>
> CONSOL_INVOICE_FY04 <add 2266M>
> CONSOL_INVOICE_FY05 <add 2666M>
> CONSOL_INVOICE_FY05 <add 2048M>
> merge all partitions down to one single partition
>
> - alter table base.invoice merge partitions BASE_INVOICE_FY00,
> BASE_INVOICE_FY01 into partition BASE_INVOICE_FY01_02;
> /
> - alter table base.invoice merge partitions BASE_INVOICE_FY02,
> BASE_INVOICE_FY03 into partition BASE_INVOICE_FY02_03;
> /
> - alter table base.invoice merge partitions BASE_INVOICE_FY04,
> BASE_INVOICE_FY05 into partition BASE_INVOICE_FY04_05;
> /
> - alter table base.invoice merge partitions BASE_INVOICE_FY00_01,
> BASE_INVOICE_FY02_03 into partition BASE_INVOICE_FY00_03
> /
> - alter table base.invoice merge partitions BASE_INVOICE_FY00_03,
> BASE_INVOICE_FY04_05 into partition BASE_INVOICE_FY00_05
> /
>
> create new table <base.invoice_new> with DDL of old table <base.invoice>
> only without partitioning
> alter table base.invoice exchange partition BASE_INVOICE_FY00_05 with
> base.invoice_new;
> alter index (new indexes) rebuild compute statistics on <base.invoice_new>
> drop table base.invoice cascade constraints;
> determine which tablespace holds new table
>
> - select owner, table_name, tablespace_name
> from dba_tables
> where table_name like 'INVOICE%'
> /
>
> create new data and index tablespaces
>
> - CREATE TABLESPACE "BASE_INVOICES"
> NOLOGGING
> DATAFILE 'E:\ORACLE\ORADATA\GDWP\BASE_INVOICES_01.DBA' SIZE
> 3800M REUSE AUTOEXTEND
> ON NEXT 10240K MAXSIZE 4096M EXTENT MANAGEMENT LOCAL
> UNIFORM SIZE 10240K SEGMENT SPACE MANAGEMENT MANUAL
> - ALTER USER "BASE"
> QUOTA UNLIMITED
> ON "BASE_INVOICES"
>
>
> - CREATE TABLESPACE "BASE_INVOICES_IX"
> LOGGING
> DATAFILE 'E:\ORACLE\ORADATA\GDWP\BASE_INVOICES_IX_01.dbf'
> SIZE 1024M REUSE AUTOEXTEND
> ON NEXT 10240K MAXSIZE 4096M EXTENT MANAGEMENT LOCAL
> SEGMENT SPACE MANAGEMENT MANUAL
>
> rename <base.invoice_new> to <base.invoice> ==> need to login as
> table/schema owner
> alter table base.invoice nologging
> alter table base.invoice move tablespace base_invoices
> alter table base.invoice logging
> drop old partitioned tablespace
> resize tablespace to be @ 50% capacity
> verify and/or apply indexes where needed
> alter index (new indexes) rebuild compute statistics
> backup controlfile to trace
> however i still see partitions in the instance, eventhough they are
> offline. I cant get them to drop
> On 8/4/05, Sriram Kumar <k.sriramkumar_at_gmail.com> wrote:
> >
> > Hi,
> >
> > I think you can use the dbms_redefinition package. i used sometime
> > back and was successful in doing it.
> >
> > Regards
> >
> > Sriram Kumar
> >
> > On 8/3/05, Bryan Wells <bunjibry_at_gmail.com> wrote:
> > > But I have 5 partitions. I guess the experiment will tell me if i can
> > > EXCHANGE all five into one new table eh?
> > >
> > > Thanks for the quick response
> > >
> > > On 8/3/05, Hallas, John, Tech Dev <John.Hallas_at_gb.vodafone.co.uk >
> > wrote:
> > > >
> > > >
> > > >
> > > > I think it is much simpler than that. Experiment first though
> > > >
> > > >
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ALTER TABLE <table_name>
> > > > EXCHANGE PARTITION <partition_name>
> > > > WITH TABLE <new_table_name>
> > > > <including | excluding> INDEXES
> > > > <with | without> VALIDATION
> > > > EXCEPTIONS INTO <schema.table_name>;
> > > >
> > > >
> > > > ALTER TABLE sales
> > > > EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
> > > > INCLUDING INDEXES
> > > > WITHOUT VALIDATION
> > > > EXCEPTIONS INTO uwclass.problems;
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ________________________________
> > >
> > > >
> > > > From: oracle-l-bounce_at_freelists.org [mailto:
> > > oracle-l-bounce_at_freelists.org] On Behalf Of Bryan Wells
> > > > Sent: 03 August 2005 10:57
> > > > To: Oracle-L
> > > > Subject: Drop Partitioning
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > We have a test box that we want to remove partitioning from. does
> > anyone
> > > have the steps to retain table data while dropping the date range
> > > partitioning. My first thought was the following. please correct me
> > where
> > > I go astray:
> > > >
> > > > export the table data
> > > > create similar table in the same tablespace with the appropriate
> > > constraints/indexes
> > > > insert into <new> as select * from <old>
> > > > alter table <old> drop partition
> > > > drop table <old> cascade
> > > > alter table <new> rename to <old>
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Thank you in advance for your help and expertise...
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Bryan Wells
> > > bunjibry_at_gmail.com
> > > Oracle DBA hopeful
> >
>
>
>
> --
> Bryan Wells
> bunjibry_at_gmail.com
> Oracle DBA hopeful
>

-- 
Bryan Wells
bunjibry_at_gmail.com
Oracle DBA hopeful

--
http://www.freelists.org/webpage/oracle-l