Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Block corruption with creating tables/inserting data

658887Sep 9 2008 — edited Sep 9 2008

I appear to have a corrupt block that I encounter when I reach a certain point in creating a table or inserting new data in an Oracle 10gR2 database. The error is

SQL Error: ORA-00604: error occurred at recursive SQL level 2
ORA-01578: ORACLE data block corrupted (file # 1, block # 59522)
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\IMAGES\SYSTEM01.DBF'
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

(I only get the first two lines when inserting new data)

Is there a way to mark this block so as to not use it. There is no existing object using this block -- the issue appears to occur when something tries to use it for the first time.

Edited by: user8026807 on Sep 9, 2008 8:13 AM

Centinul

How are you determining the end date?

user12050217

Thanks. Upto the value of busno column remain same, because a complete bus trip data first entered and then another bus trip data.

Centinul

Are there any other columns in this table that you are not providing? The reason I ask is because it seems as if your solution is dependent on when a particular item is inserted into the table. However, there is no column that indicates which items were inserted first such as a monotonically increasing value (a.k.a. sequence value). The order of the result set cannot be guaranteed without using an ORDER BY clause.

Maybe I am missing something here but I cannot see an easy way to order the set initially to get the grouping you desire.

Thanks!

Boneist

user12050217 wrote:
Thanks. Upto the value of busno column remain same, because a complete bus trip data first entered and then another bus trip data.

Yes, but how are you determining the order in which the rows were inserted into the table?

Think of a table like a pile of clothes on the floor - how can you tell the order with which each item of clothing was added to the pile if there isn't something that's keeping a note of when it was added to the pile? Answer: you can't.

If your table doesn't have a column indicating the order the rows were inserted, then you're going to find it difficult to get the date you're after.

user12050217

No sir, there are any other column in the table, becasue we gets only bus number, no of passengers (tickets issued by bill book) and date of bus running by drivers; so we just feed the complete data of a particular bus. Now our manager has asked me to produce the above query result, so that they can further calculate the extra money for the drivers.

Thanking you,
Nisha

Boneist

You'll need to go back to your managers and tell them that you won't be able to get the data they're after.

However, it's a very strange requirement - "give me these random ranges determined by when they were inserted into the table" - if insert order is important, then I'd say the table needs a new column (timestamp) to hold the date + time the row was added plus a trigger to populate the column.

I'd go back to your manager and ask what range of dates they'd be most interested in - weekly or monthly?

Karthick2003

Nisha,

You must understand the simple fact that oracle does not have any predefined order for a table. So unless you use the ORDER BY clause the order in which the data are returned by oracle cannot be determined.

Hence you must find a column which will determine the order in which the BUSSNO must be retrieved. If you cant find one then you are out of luck.

Boneist

Eg. if they wanted the data weekly, then you'd do something like:

with my_tab as (select 4123 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                select 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 25 passengers from dual union all
                select 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                select 4123 busno, to_date('06/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                select 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                select 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 19 passengers from dual union all
                select 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 22 passengers from dual union all
                select 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                select 4123 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 23 passengers from dual union all
                select 4123 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 27 passengers from dual union all
                select 4123 busno, to_date('11/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 24 passengers from dual union all
                select 6138 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                select 6138 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual)
-- end of mimicking your data; use SQL below:
select busno,
       trunc(start_date, 'iw') start_date,
       trunc(start_date, 'iw') + 7 end_date,
       sum(passengers) total_passengers,
       max(passengers) max_passengers
from   my_tab
group by busno, trunc(start_date, 'iw')
order by trunc(start_date, 'iw'), busno;

     BUSNO START_DATE END_DATE  TOTAL_PASSENGERS MAX_PASSENGERS
---------- ---------- --------- ---------------- --------------
      4123 28/SEP/09  05/OCT/09               20             20
      6138 28/SEP/09  05/OCT/09               70             22
      4123 05/OCT/09  12/OCT/09              123             27
      6138 05/OCT/09  12/OCT/09              109             24

1 person found this helpful

user12050217

I would like to thanks to all for your fast and valuable replies. Here issue is how to order by; so i am assuming that order by rownum can be work; because once entered data, it will never updated and as we insert the rows, i hope it will be inserted at the bottom (free space in the extent). But if it is still require the change the table structure i.e. add a new column, i am ready to add the new column but please guide me in more details.

Thanks to all you once again.
Nisha

Karthick2003

ROWNUM is a pseudocolumn which is generated when a row is outputted. So ordering by ROWNUM does not make any sense.

Your best possible option is to have a timestamp column that will the capture the inserted time and you can just order by that column.

Centinul

user12050217 wrote:
I would like to thanks to all for your fast and valuable replies. Here issue is how to order by; so i am assuming that order by rownum can be work; because once entered data, it will never updated and as we insert the rows, i hope it will be inserted at the bottom (free space in the extent).

As was mentioned previously there is no predefined order. There is no default that says data is returned in the order it was inserted. If there was this could make index access painfully slow.

I believe you are going to have to modify the table structure.

user12050217

SQL> create table newbus as select rownum id,bussno,start_date,passengers from bustour;

Table created.

SQL> select * from newbus;

        ID     BUSSNO START_DATE PASSENGERS
---------- ---------- ---------- ----------
         1       4123 04-10-2009         20
         2       4123 05-10-2009         25
         3       4123 05-10-2009         18
         4       4123 06-10-2009         15
         5       6138 03-10-2009         16
         6       6138 03-10-2009         19
         7       6138 04-10-2009         22
         8       6138 04-10-2009         13
         9       4123 07-10-2009         23
        10       4123 08-10-2009         27
        11       4123 11-10-2009         15

        ID     BUSSNO START_DATE PASSENGERS
---------- ---------- ---------- ----------
        12       6138 05-10-2009         16
        13       6138 05-10-2009         13
        14       6138 05-10-2009         18
        15       6138 05-10-2009         24
        16       6138 07-10-2009         20
        17       6138 08-10-2009         18

17 rows selected.

SQL> select * from newbus order by id;

        ID     BUSSNO START_DATE PASSENGERS
---------- ---------- ---------- ----------
         1       4123 04-10-2009         20
         2       4123 05-10-2009         25
         3       4123 05-10-2009         18
         4       4123 06-10-2009         15
         5       6138 03-10-2009         16
         6       6138 03-10-2009         19
         7       6138 04-10-2009         22
         8       6138 04-10-2009         13
         9       4123 07-10-2009         23
        10       4123 08-10-2009         27
        11       4123 11-10-2009         15

        ID     BUSSNO START_DATE PASSENGERS
---------- ---------- ---------- ----------
        12       6138 05-10-2009         16
        13       6138 05-10-2009         13
        14       6138 05-10-2009         18
        15       6138 05-10-2009         24
        16       6138 07-10-2009         20
        17       6138 08-10-2009         18

17 rows selected.

SQL>

Now i think i have id column for order by clause. We will update our application to insert the id value max(id)+1.
Regards

kendenny

Don't use max(id)+1, use a sequence. If you use max(id)+1 and two users enter data at the same time you'll get duplicate id's.

Boneist

user12050217 wrote:
Now i think i have id column for order by clause. We will update our application to insert the id value max(id)+1.

As well as not using a sequence to generate your id's, relying on id's for ordering can produce incorrect results, as rows can get an id, and then wait for the user to finish their lunch before they click on the "submit" button. If you're interested in the time at which the row was inserted, then, as I've suggested before, use a timestamp column, along with a trigger to update the time to systimestamp.

That way, you'll always get the time at which the row was added into the table.

I still fail to see why your managers require differing time ranges per bus number to work out bonuses; surely a consistent range (a week/4 days/a month/etc) would be better? Have you queried that with them?

user12050217

Our management has decided to give bonus to drivers, on running the bus on its full sitting capacity. So, when bus's trip starts and go to end; for those no. of days (which are not fixed and certain) a certain % of bonus will be calculated as per my query output;including value of totalpassengers. As far as application and sequence concern, only one data entry operator runs the application and enters the data in the table. If still it require to use sequence; i shall keep in mind when updation in application done.

Regards

Boneist

user12050217 wrote:
As far as application and sequence concern, only one data entry operator runs the application and enters the data in the table.

Maybe... for now. What happens if they get someone else in to help out?

And will the operator(s) know that the order in which they key the data is important? What determines the order of how they key in the data?

If still it require to use sequence; i shall keep in mind when updation in application done.

No. If you're going to go with ID's, USE A SEQUENCE to generate them!! Trying to work out the max id and then add 1 to it is only going to get slower the more rows you have in the table. It's a bad idea to re-invent the wheel!

user12050217

Sir,
Earlier it was issue that table do'nt have order by column, so i added it. Now, i am not able to understand the use of sequence / id column to create the required output please. Here id column is the record number and its another issue, when we update the application. Now, table is having order by column, so please guide me how to write the select query on the existing table. Rest will be sorted out in due course please.

I am not getting your mean by "No. If you're going to go with ID's, USE A SEQUENCE to generate them!! "
Kindly elaborate more.
Thank you sir.

Boneist

You create a sequence to auto generate the ids for you.

Then you insert them into the table like:

insert into table_name (id_column, ....) values (sequence_name.nextval, .....);

That way, you don't have to worry about working out what the next id you should pick; it's already done for you.

You could also have a trigger to do the insert if no id is specified.

1 person found this helpful

user12050217

Ok, with this i will do the required changes in the application its sequence / id issue. But i am waiting for select query please.

Regards

Boneist

Answer

Ok, something like this:

with my_tab as (select 1 id, 4123 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                select 2 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 25 passengers from dual union all
                select 3 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                select 4 id, 4123 busno, to_date('06/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                select 5 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                select 6 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 19 passengers from dual union all
                select 7 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 22 passengers from dual union all
                select 8 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                select 9 id, 4123 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 23 passengers from dual union all
                select 10 id, 4123 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 27 passengers from dual union all
                select 11 id, 4123 busno, to_date('11/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                select 12 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                select 13 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                select 14 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                select 15 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 24 passengers from dual union all
                select 16 id, 6138 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                select 17 id, 6138 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual)
-- end of mimicking your data; use SQL below:
select busno,
       min(start_date) start_date,
       max(start_date) end_date,
       sum(passengers) total_passengers,
       max(passengers) max_passengers,
       max_pass_date
from   (select id,
               start_date,
               busno,
               passengers,
               distance,
               max(start_date) keep (dense_rank first order by passengers desc) over (partition by distance, busno) max_pass_date
        from   (select id,
                       start_date,
                       busno,
                       passengers,
                       -- using tabibitosan method, borrowed from Aketi Jyuuzou 
                       dense_rank() over (order by id) -
                          row_number() over (partition by busno order by id) distance
                from   my_tab))
group by busno, distance, max_pass_date
order by min(id)

     BUSNO START_DATE END_DATE   TOTAL_PASSENGERS MAX_PASSENGERS MAX_PASS_DATE
---------- ---------- ---------- ---------------- -------------- -------------
      4123 04/10/2009 06/10/2009               78             25 05/10/2009   
      6138 03/10/2009 04/10/2009               70             22 04/10/2009   
      4123 07/10/2009 11/10/2009               65             27 08/10/2009   
      6138 05/10/2009 08/10/2009              109             24 05/10/2009

Marked as Answer by user12050217 · Sep 27 2020

Centinul

This is what I came up with, I'm not sure how full proof it is to be perfectly honest.

Removed for a couple of reasons:

1. The previously presented solution was better (nice solution Boneist) :)
2. My max passenger date logic was incorrect.

Edited by: Centinul on Oct 13, 2009 11:10 AM

Boneist is there a link on the `Net for this: tabibitosan method, borrowed from Aketi Jyuuzou

Boneist

Sure; you don't think I {noformat}*remembered*{noformat} how to do it, do you?! *{;-)

Here's where I first came across it: 3320506
And here's another one: 3677639

It was the 2nd link that I, um, "borrowed" from! ("copied" doesn't sound quite so nice... *{;-) )

ETA: In fact, I only really "got" what it does by printing out the rownum and the dense_rank values to work out why one minus the other gives a consistent result; I can has teh dumb *{;-)

Edited by: Boneist on 13-Oct-2009 16:40

1 person found this helpful

Centinul

Boneist wrote:
ETA: In fact, I only really "got" what it does by printing out the rownum and the dense_rank values to work out why one minus the other gives a consistent result; I can has teh dumb *{;-)

That's the first thing I did when I was looking at your method :)

Thanks for the links!

user12050217

Thank you sir Boneist. Your query is giving me the exact output which i want.

Regards
Nisha Sharma

Aketi Jyuuzou

I am glad that "sense of tabibitosan" is used ;-)

with my_tab as (
select 1 id, 4123 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
select 2 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 25 passengers from dual union all
select 3 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
select 4 id, 4123 busno, to_date('06/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
select 5 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
select 6 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 19 passengers from dual union all
select 7 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 22 passengers from dual union all
select 8 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
select 9 id, 4123 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 23 passengers from dual union all
select 10 id, 4123 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 27 passengers from dual union all
select 11 id, 4123 busno, to_date('11/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
select 12 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
select 13 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
select 14 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
select 15 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 24 passengers from dual union all
select 16 id, 6138 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
select 17 id, 6138 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual)
select busno,min(start_date),max(start_date),sum(passengers),max(passengers),
max(start_date) Keep(Dense_Rank Last order by passengers) as max_pass_date
  from (select ID,busno,start_date,passengers,
         Row_Number() over(order by id)
        -Row_Number() over(partition by busno order by id) as distance
          from my_tab)
group by busno,distance
order by min(id);

BUSNO  MIN(STAR  MAX(STAR  SUM(PASSENGERS)  MAX(PASSENGERS)  MAX_PASS
-----  --------  --------  ---------------  ---------------  --------
 4123  09-10-04  09-10-06               78               25  09-10-05
 6138  09-10-03  09-10-04               70               22  09-10-04
 4123  09-10-07  09-10-11               65               27  09-10-08
 6138  09-10-05  09-10-08              109               24  09-10-05

1 - 25

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 7 2008

Added on Sep 9 2008

#error, #general-database-discussions

8 comments

569 views

Oracle Database Discussions

Block corruption with creating tables/inserting data

Comments

Post Details