Ora-01555 Snapshot Too Old

[ixion]

why? A table is a table is a table. You'd have to check the query plans, but assuming you picked a decent partitioning key, you're all set.

A Partitioned table is not a table....

A table that is partitioned is a different storage mechanism. Requiring appropriate infrastructure to maintain it. That is unless you have a fixed mechanism and fixed partitioning. Implementing rolling date partitioning takes a bit of code to make work correctly. Something that is probably out of the original Posters technical skill to implement considering the question asked.

not true, just exchange partition with the big table. segments stay the same, just the data dictionary is updated.

I think you've missed the point of the question I was answering. Please re-read, I'm sure He'd run into space issues due to re-organizing a single large unpartitioned table into a table that is partitioned into smaller more manageable units.

definitely requires testing, although seems like a lot of work just to delete some data.

Well at least we both agree testing is a good idea.

[ixion]

Hi All,

I need to delete large number of rows as my disk is having very less space. While deleting I'm getting following error -

The OP has a disk space issue. The ora-1555 is a symptom of the problem. Not the problem.

What is the goal here?

1. Reclaim space within the table?
Use the delete loops posted above from gandolf...

2. Reclaim space for the tablespace ?
Use the Ctas approach from above. (see posts #7 & #21 )

3. Fix the problem ?
Add more disk and increase the tablespace.
Implement partitioning and data archiving strategy.

[marist89]

A table that is partitioned is a different storage mechanism. Requiring appropriate infrastructure to maintain it. That is unless you have a fixed mechanism and fixed partitioning. Implementing rolling date partitioning takes a bit of code to make work correctly. Something that is probably out of the original Posters technical skill to implement considering the question asked.

I 100% agree, except before you said there was a design change involved.

I think you've missed the point of the question I was answering. Please re-read, I'm sure He'd run into space issues due to re-organizing a single large unpartitioned table into a table that is partitioned into smaller more manageable units.

I don't work in the world of assumptions. The point I was making was to convert a non-partitioned table to a partitioned table you don't need any more space than you currently have since the segments stay the same.

[ixion]

I don't work in the world of assumptions. The point I was making was to convert a non-partitioned table to a partitioned table you don't need any more space than you currently have since the segments stay the same.

Your assuming the segments are staying the same. If you are partitioning lets say for the sake of argument, on a date range. And each partition is bounded by say a month. Then the original table could not be swapped into such a partitioning scheme since the table would contain data that is out of bounds for the particular range...

Now if you have say 20 tables identical in structure except for the data contents and name. Say each is named tbl_YYYYMMDD Where each table contains data for the representative naming standard. Then swapping these into a partitioned table is trivial and is indeed identical segment wise.


Maybe we are saying the same thing, but differently.
Ken

[marist89]

Your assuming the segments are staying the same.

no I'm not,that's the way it works.

Then swapping these into a partitioned table is trivial and is indeed identical segment wise.

exactly what I said.

To convert a non-partitioned table to a partitioned table with only one partition bounded by maxvalue, it is a simple command that only updates the data dictionary.

[ixion]

To convert a non-partitioned table to a partitioned table with only one partition bounded by maxvalue, it is a simple command that only updates the data dictionary.

Yes in this 1 special case the segments stay the same. But its kind of pointless to partition in this manner. You're better off with the non partitioned table. Please explain how this is going to allow this person to drop the older data partition if there is only one partition with all of the data? (see post #23)

Just because you can do this, doesn't mean you should. Well except maybe you might split the partition off to the older stuff and the newer stuff. And then drop the older stuff... But that takes space due to allocation of new segments and then row movement.