that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
I have deleted lot of records in a table.Would oracle be able to insert in the empty blocks generated from deletion of records without bringing the high water mark down.
I am trying to discern the difference between Shrink and Move and their impact on the High Water Mark of a table.
My understanding is that MOVE in effect rewrites every row of a table ( hence why it can deal with row chaining ) whereas SHRINK basically moves existing rows in a table 'down' the table into any available free space. This is why MOVE takes a table lock whereas SHRINK takes a row lock. What I am trying to discern is - does MOVE and SHRINK effect the high water mark and does both reallocate space and give it back to free space for the tablespace ? I believe MOVE does reduce the HWM and give freed space back to the tablespace. I am not so sure about SHRINK.
I have one tablespace called U01. This tablepspace contains 31 data files. Due to high water mark I was unable to most datafiles. Since my database running onair application they will not provide me downtime to move the tables. Is there anyway to fix the high water mark without getting downtime window? almost 700+g space unused. I need to reuse them asap because running out of space with in asm diskgroup.
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select c.Event, c.code, count(1) as calls, total.total_count, count(1) / total.total_count * 100 as pct_of_total from table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
I have created a trigger for after insert which updates a table when there is a row inserted in that table. The update is on a column which stores the application description along with the sequence number. Now my requirement is that sequence number should be unique only with in an application but not with in the table.Say the row entry can be as follows:
App_Desc Request_ID ----------------------- DEV 100 1 DEV 101 2 STG 100 3 STG 101 4
Here Request_ID is unique But the sequqnce thats created for DEV (100,101) should take an entry of 102 for the next entry for DEV and same applies for STG. So I have to use the same sequqnce for all the application.
1) Invoice Invoice_no number(8), Debtor varchar2(35), Invoice Date Date, Print Date Date, Currency varchar2(3), Total Amount Ex Number(11,2), Total Amount Vat Number(11,2), Total Amount Number(11,2), Status Varchar2(15),
For some reason Oracle Sequence is skipping the first value. Here is an example of script.
drop sequence tseq; drop table xyz; create table xyz (sno integer); create sequence tseq start with 1; insert into xyz values (tseq.nextval); select * from xyz;
Now the runtime of the script in SQL Developer:
drop sequence tseq succeeded. drop table xyz succeeded. create table succeeded. create sequence succeeded. 1 rows inserted SNO ---------------------- 2
Why is it skipping the starting value 1. Is there something new in 11g that is causing it to skip the first value. I have many scripts and they all are having errors due to this issue.
The below trigger is running fine without any error ,but i wasn't able to perform the exact logic by the below trigger.IF, inserting multiple row then the SIVNUM field gets the same sequence number for each row , without incrementing the value for the next row.
Only the sequence value increases on the next set of row ow's inserted
Example (inserting 2 rows ) based on the below trigger:
I have round about 100 sequences in a schema. How can i identify that which sequences are not used for a long time. Or How can i identify the last date when that sequence is used.
In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.
This is the current scenario -
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01 Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02 Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03
When I try to query based on the time stamp, primary should also go up. To be very clear on what I would like to have, please consider the following example.Without using NOCACHE option, I need to have the data in the following order.
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01 Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02 Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03
In other words, sequence number should always increment along with the time.
I have Created sequence in oracle 10G and its working fine. its showing THE NUMBER IS 2
SQL> DECLARE NUM NUMBER; 2 BEGIN 3 select SERIAL_NO.NEXTVAL into num from dual; 4 DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ' ||NUM); 5 END; 6 / THE NUMBER IS 2
PL/SQL procedure successfully completed.When i execute this code in oracle 11G then it does not show output.
SQL> DECLARE NUM NUMBER; 2 BEGIN 3 NUM :=SERIAL_NO.NEXTVAL; 4 DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ' ||NUM); 5 END; 6 /
PL/SQL procedure successfully completed. SQL> Why this is not showing output as THE NUMBER IS .
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
