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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 04 2005 - 20:00:38 CDT
