Modify the Primary Key Order

I have created a composite primary key (a,b,c) in the table. It has references in other table also.

Now I want to modify the primary key order - (b,c,a). How to go about it?

You can try this sequence:

1) disable primary key related foreign constraint on child table;

2) disable primary key constaint on present table

3) drop index

4) recreate index

5) enable all disabled constraint

The enclosed scrip will do it:

CODE
FILE: recreate_pk_ddl.sql

set pagesize 999 heading off verify off termout off
set feedback off sqlcase upper newpage 3 rowsize 400
SELECT constraint_name column1,5 column2, 0 column3,
'alter table '||table_name||' add constraint '||table_name||'_pk primary key ('
FROM user_constraints where
constraint_type = 'P'
UNION
SELECT constraint_name,10,position,
decode(position,1,' ',',')||column_name
from user_cons_columns
where constraint_name in (select constraint_name from user_constraints
where constraint_type = 'P')
UNION
SELECT constraint_name, 20,99,' )' from user_constraints
union
select constraint_name, 30,99,' using index' from user_constraints
union
select constraint_name, 40,99,' pctfree 10' from user_constraints
union
select constraint_name, 50,99,' tablespace indexes ' from user_constraints
union
select constraint_name, 60,99,' storage( ' from user_constraints
union
select constraint_name, 70,99,' initial ' from user_constraints
union
select constraint_name, 80,99,' next ' from user_constraints
union
select constraint_name, 90,99,' pctincrease 0 );' from user_constraints
union
select constraint_name, 100,99, ' ' from user_constraints
order by 1,2,3;

Modifying A Column Of A Table

I have a table lets say demo, and one of the columns of this table is time_in_sec is of type number(38).

This table has around 20,000 rows of data. Now I want to modify the time_in_sec column of demo table to number (7,3) so that I can store values like 234.987

All the existing datas in this column are numbers up to maximum of 3 digit whenever I am trying to modify the column I get errors like ORA-01440: column to be modified must be empty to decrease precision or scale one solution to this was drop this column and add new column but I do not want to loose existing data.

How to achieve the changes I want ie changing the column type from number (38) to number (7,3)

CODE :

Create Table Demo_Time_In_Sec As Select Rowid Row_Id, Time_In_Sec From Demo;
Update Demo Set Time_In_Sec = Null;
Commit;
Alter Table Demo Modify (Time_In_Sec Number(7,3));
Update Demo U Set Time_In_Sec = (
Select Time_In_Sec From Demo_Time_In_Sec T
Where U.Rowid = T.Row_Id);
Commit;
Drop Table Demo_Time_In_Sec;

Select Entries from Whole Week

Get all entries from "the whole week" (begin: Monday end: Sunday) from e.g the day sysdate - 222 (today: 20.10.2004) . This means all entries with a date between 08.03.2004 and 14.03.2004 (including the borders !)

create or replace
procedure whole_week (week date) is

total number(6);
day varchar2(20);

begin
total:=0;

select to_char(sysdate,'day') into day from dual;

if day='montag' then
select sum(total) into total from s_ord where date_ordered between week-1 and week+5;

else if day='dienstag' then
select sum(total) into total from s_ord where date_ordered between week-2 and week+4;

else if day='mittwoch' then
select sum(total) into total from s_ord where date_ordered between week-3 and week+3;

else if day='donnerstag' then
select sum(total) into total from s_ord where date_ordered between week-4 and week+2;

else if day='freitag' then
select sum(total) into total from s_ord where date_ordered between week-5 and week+1;

else if day='samstag' then
select sum(total) into total from s_ord where date_ordered between week-6 and week;

else if day='sonntag' then
select sum(total) into total from s_ord where date_ordered between week and week+6;

else
dbms_output.put_line('kein tag');

end if;
end if;
end if;
end if;
end if;
end if;
end if;
dbms_output.put_line(total);

end;
/

begin
whole_week(sysdate+1);
end;
/

instead of montag you have to write monday and so on...i've hat a german version so i've got this names for the days.
montag is monday
dienstag is tuesday
mittwoch is wednesday
donnerstag is thursday
freitag is friday
samstag is saturday
sonntag is sunday