Ora-01555 Snapshot Too Old - Page 4
DBAsupport.com Forums - Powered by vBulletin
Page 4 of 4 FirstFirst ... 234
Results 31 to 36 of 36

Thread: Ora-01555 Snapshot Too Old

  1. #31
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by marist89
    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.

    Quote Originally Posted by marist89
    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.


    Quote Originally Posted by marist89
    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.

  2. #32
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by minal_yawale
    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.
    Last edited by ixion; 07-12-2006 at 03:40 PM.

  3. #33
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by ixion
    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.

    Quote Originally Posted by ixion
    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.
    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. #34
    Join Date
    Jun 2006
    Posts
    259

    Wink

    Quote Originally Posted by marist89
    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

  5. #35
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by ixion
    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.
    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."

  6. #36
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by marist89
    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.
    Last edited by ixion; 07-13-2006 at 05:00 PM.

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