DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Best Way To Drop Data

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Question Best Way To Drop Data

    What would be the best way to drop data out of our large production table? We have some large tables and we only want to keep 3 months of data in them. We want to have an easy way to drop one month of data every month. We are thinking of partitioning the table by month and then have a unix script that will drop and add a new partition every month. Does anyone have a better suggestion??

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    good idea....have not worked with partioned tables....but Y drop partion instead truncate it and relaod and use round robin to add sucessive months data......

    another way cud be add column that hold date(i edited it....coz it was mistyped as data)....and delete based on date...

    Abhay.
    Last edited by abhaysk; 03-02-2003 at 07:01 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Best Way To Drop Data

    Originally posted by lesstjm
    We are thinking of partitioning the table by month and then have a unix script that will drop and add a new partition every month. Does anyone have a better suggestion??
    I have one. Why involving the outside infrastructure when everything you intend to do is strictly encapsulated in the scope of the database? What I'm saying is forget about unix script and OS scheduler - you don't need it at all. Simply create a database job that will take care of that. It is much much much more robust and elegant in every respect.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Only issue with DBMS_JOBS is, we don't know what the hell happened with the job if it fails for some reason, especailly if the job involves PL/SQL. If just SQL we can always spool it, else need SMTP package(or EM) to notify JOB sucess or not.

    One needs little more effort to stream line the process for Jobs thru DBMS Job scheduler.
    Reddy,Sam

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sreddy
    Only issue with DBMS_JOBS is, we don't know what the hell happened with the job if it fails for some reason, especailly if the job involves PL/SQL.
    Can you be more specific in what you mean by that? Isn't this one of the reasons why EXCEPTIONS were "invented" in PL/SQL? It is realy trivial to record the exact reason (the whole error stack, if you want) why a particulare job failed.

    I would realy be interested in a scenario where any OS scheduler will be more appropriate or more capable compared to DBMS_JOB when the task involves any database activity. Appart from starting/stoping the database, of course.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    I would realy be interested in a scenario where any OS scheduler will be more appropriate or more capable compared to DBMS_JOB when the task involves any database activity. Appart from starting/stoping the database, of course.
    OS Scheduler(Batch Scripts) wud be more easy way to capture the logs...& ofcourse for scalability....inhertiability........

    Say u have 10000 PL/SQL scripts to be executed per day...then creating so many jobs wud be teadious and rather u cud jus write a Generic Batch Script and pass parameter which can be used to execute that PL/SQL script.............

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Go to http://examples.oreilly.com/orbestprac/ and click on the link to download examples.zip. err.pkg is a generic package for exception handling in PL/SQL program units. Errors numbers and messages are optionally logged to either a table or a text file. Would bevery useful if you opt to use DBMS_JOB.

  8. #8
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Say u have 10000 PL/SQL scripts to be executed per day...then creating so many jobs wud be teadious and rather u cud jus write a Generic Batch Script and pass parameter which can be used to execute that PL/SQL script.............
    I may be misunderstanding this, but wouldn't you still need to create 10000 entries in the crontab saying run my generic script at x time using y parameter. Also, with 8i+ native dynamic sql means that creating a similar generic PL/SQL procedure to execute the program unit passed to it is very easy (and it's not much more complicated even if you have to use DBMS_SQL in v7). DBMS_JOB is much more future-proof in that if you choose to switch your OS all the scheduled stuff will work with no modifications.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    OS Scheduler(Batch Scripts) wud be more easy way to capture the logs...& ofcourse for scalability....inhertiability........
    Again - how are batch scripts easier to capture logs? Can you give any example? What can you do with your batch scripts that I can't do with DBMS_JOB?

    And speaking about scalability! I'm wondering how more scalable is to have OS job that have to make a new database connection each time it needs to run as compared to DBMS_JOB where everything is run strictly inside the database?

    I'm not sure what do you mean by "inhertiability" exactly, but I'm sure you'll have much more fun rewriting all your OS batch scripts if your company decides to change the OS you are using currently. Me, on the other hand, will be very bored if that happen, because I won't have to change a single line of my PL/SQL code!

    And I'm sure if we inspect a lot of systems where they run their database related jobs through their OS schedulers we'll find in about 9 of 10 cases there are security problems with their setup. I'm sure in 8 out of 10 cases we would find they have their database UN/PWD hardcoded in their OS jobs! On the other hand, with DBMS_JOB there is no security risk whatsoever, at least not in respect to exposing database accounts credentials.
    Say u have 10000 PL/SQL scripts to be executed per day...
    In that case I would be fired immediately, I guess. Having to run one PL/SQL script every 9 seconds on avarage, for a whole day? Come on!

    Nevertheles, I don't understand what do you mean by the expression "PL/SQL script". There is no such thing in PL/SQL. In PL/SQL there are only anonymous blocks and procedures/functions/packages. I guess you mean SQL*Plus scripts, actualy.

    ...then creating so many jobs wud be teadious and rather u cud jus write a Generic Batch Script and pass parameter which can be used to execute that PL/SQL script.............
    I don't quite follow you here. But I'm sure even a PL/SQL beginner would have no problems in writing a simple Generic PL/SQL Procedure and pass parameter which can be used to execute that particular stored procedure. (as opposed to your "Generic Batch Script and pass parameter which can be used to execute that PL/SQL script")

    The only time when it might be worth scheduling something strictly database related through OS scheduler is perhaps when you are paid by the number of line of code you write to make that job done. Realy, that is the only reason I could understand (but not approve, of course).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hacketta1
    I may be misunderstanding this, but wouldn't you still need to create 10000 entries in the crontab saying run my generic script at x time using y parameter. Also, with 8i+ native dynamic sql means that creating a similar generic PL/SQL procedure to execute the program unit passed to it is very easy (and it's not much more complicated even if you have to use DBMS_SQL in v7). DBMS_JOB is much more future-proof in that if you choose to switch your OS all the scheduled stuff will work with no modifications.
    Not necessarily u need to have N diff scheduling jobs ....
    Instead wat i meant with 10000 diff PL/SQL code to be executed can be grouped and executed in a single Batch Script with some logic involved as to which PL/SQL code to be executed depending on so many factors as per business logic......Instead u may argue that u cud implement such logic in PL/SQL code itself rather Y Batch scripts......

    Well the ans wud be...take a example....
    Say X1.sql, X2.sql, X3.sql & so does 1 set of defined task....
    U need to execute based on some conditions, that u can implement in Batch Scripts....well that diff SQLs are associted to some other reference tables which store reference data say run FORDATE & so....
    & u have generic Package which updates these tables with parameter passed to the PL/SQL & it has to be unoque.........

    Now u may argue that .... all of above stuff cud be implemnted in single PL/SQL code....S ofcource but it wud be complicated to understand for any other person coming into the project...
    Rather i wud say....restrict ur code to wat u want to do....and use batch scripts to control the execution of these PL/SQL codes......

    Or rahter VB Script is as well suited for such tasks.....

    Well if u still think DBMS_JOBS are good then go ahead...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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