I am not getting added hours from results.
SELECT audittimestamp + interval (SELECT EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.
i want a select statement to retrieve the last name added into the "ename" column .i mean :if i insert "jack" into "ename" column , i want to write a select statement to retrieve that last name while i do not know it is jack or any other name ?
View 2 Replies View RelatedAfter compiling a simple valid SP in Toad for Oracle 8.5.1:
CREATE OR REPLACE PROCEDURE proc_test
AS
DECLARE v_name VARCHAR2(20) := 'Ed Edson';
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi, my name is ' || v_name);
END;
The SP is not added to the schema. Also the three status fields (status, created and Last Update in the Procedure Editor remain <Unknown>. It is not a rights or privilege issue, because the creation works perfectly in SQL*Plus!
If one of the redolog member corrupted and overcome this problem, I had removed the corrupted redolog member. Later I had added a new member to this group.
I would like to know, is the newly added log member will get sync with existing log member? How the newly added log member get sync with existing log member?
When I am importing, I get these errors
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (XXXXXXXXXXXXXXXX) violated
Column 1 2
Column 2 OFFLINE
[code]....
I added a datafile in undo tablespace (its an ASM database). I doubt that since I added the datafile to undo tablespace, I am getting this error.
Mistakenly I added lot of datafiles with autextend on option.. I realized later and then tried to resize the datafiles to a minumum space but got the below error
ORA-03214: File Size specified is smaller than minimum required.
How to resolve this problem to reclain the space back?
One of my Customer had followed the document applied pertaining to mitigate against vulnerability CVE-2012-3132.
New Document Mitigation steps for CVE-2012-3132 [ID 1482694.1]
This was applied to a database and it stopped the 'password' command from succeeding in SQLPLUS. Once the trigger was disabled, it worked fine.
ORA-00604: error occurred at recursive SQL level 1
ORA-06531: Reference to uninitialized collection
ORA-06512: at "SYS.NAME_SECURITY", line 165
ORA-06512: at line 2
Why is this behavior seen? By the way, he was able to change the password using TOAD. Only sqlplus is the issue.
i recently installed an apex plugin but after deinstalling the plugin some of the functionality was still there.I suspected the plugin uploaded a js library and a css file.I found the functions apex_javascript.add_library and apex_css.add in the plugin.Where does apex save the files that are uploaded using those functions?I want do remove then.
Oracle DB: 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionAPEX: 4.1.1.00.23
How i can show the new added rows to tabular form after submit? what I want is to show my customer new added rows that recently added. but according to how I sort the tabular form, the new added rows distributed in multi pagination in tabular form. I'm not good in English, excuse me for my mistakes. I use apex 4.2.0 On Oracle 11g r2 on windows Server 2008
View 2 Replies View Relatedmucking on an Oracle 11.2 database, simple range partitioning issue. Seems using a "complex" formula inside the AT clause annoys it? Or am I doing something wrong?
I create the table with RANGE partition just fine:
CREATE TABLE my_part_tab
( id number,
sdate date
)
PARTITION BY RANGE ( sdate )
( PARTITION P2013Q1 VALUES LESS THAN ( TO_DATE('01-jan-2013','dd-mon-yyyy') ),
PARTITION P2013Q2 VALUES LESS THAN ( TO_DATE('01-jul-2013','dd-mon-yyyy') ),
PARTITION P2013Q3 VALUES LESS THAN ( TO_DATE('01-oct-2013','dd-mon-yyyy') ),
[code]...
Table created.(and yes, I'm aware of INTERVAL option that'll do this next part "automagically", however, INTERVAL and REFERENCE partitioning are incompatible, and the child table is using REFERENCE partition). to make things easier on DBA for future, I'm trying to create a script that makes added a year's worth of partitions less manual.So far, I have the following working:
ACCEPT lYear PROMPT "Add Paritions for which calendar year?"
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX );
[code]...
But no luck ...Same issue with other variations:
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( (ADD_MONTHS(TO_DATE('01-jan-&lYear','dd-mon-yyyy'),12)) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( (TO_DATE('01-jan-'||TO_CHAR(TO_NUMBER('&lYear')+1,'fm9999'),'dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
I am facing some challenge while running update query on newly added column in existing table.
Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1
No of records in table : 12572770
Below are the step i followed.
1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.
SQL> desc testtablename
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUMNNAME1 VARCHAR2(8)
COLUMNNAME2 CHAR(1)
COLUMNNAME3 CHAR(1)
COLUMNNAME4 VARCHAR2(8)
COLUMNNAME5 VARCHAR2(11)
[Code]...
Table altered.
SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;
INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME 1 COLUMNNAME24
Problem faced & My analysis
1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.
Below are the queries completed successfully
Update Testtablename
Set Columnname29 = '1'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
[Code]...
Below are the queries hanging in database
Update Testtablename
Set Columnname29 = Columnname1
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname29 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below is character set in database
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
[Code]....
I want to write a function that gets:
1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)
I got a table called EVENTS that got 2 fields:
1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)
and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:
input.event_start_time is between EVENTS.event_date
and EVENTS.event_end_date
and
input.event_end_time is between EVENTS.event_date
and EVENTS.event_end_date
but to compare only the hours here! (HH24:MI)
because the date (dd/mm/yyyy) is checked before..
I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.
I have a table which will the following type of data
"COL1""COL2"
1001"27-DEC-2010 02:00:00"
1002"27-DEC-2010 07:00:00"
1003"27-DEC-2010 09:00:00"
1004"27-DEC-2010 02:00:00"
1005"27-DEC-2010 12:00:00"
Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,
COL1 COL2
0 2010122701
1001 2010122702
1004 2010122702
0 2010122703
0 2010122704
0 2010122705
0 2010122706
1002 2010122707
I have a field in Customers table called shipeddate....
I wnat to check the number of hours an item which has a shipeddate is in the store room to the current datetime...
But the business hrs of the store room are from 8am-5pm..
So when a shipped date is 4pm on MOnday
and i am checking on 9 am Tuesday the number of hrs shud be 1(4-5 of Monday)+1(8-9 of tuesday) =2hrss..
How can i achieve this...
i am using one query but not getting correct minutes.
here is my query:
v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);
here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.
here i should get value 370.
but i am getting value 350.
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours
The following query take 63.499 sec
SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,
[Code].....
I want to convert below MS-SQL query in oracle 10g.
for eg:
Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable
Output:
9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes
I have a table with a date field. This field storage dates with hours like this:
01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...
I have to generate a report with the day and the frequency :
__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3
The block of the hour must be 1 hour (8-9,9-10 and so on)
how generate the hours range.
My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).
I configure logical standby online .when I execute dbms_logstdby.buid,first
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
it was blcoked by other sesson,then i kill the holding session,but no work.then i cancel this step and execute it again . the error is
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
BEGIN DBMS_LOGSTDBY.BUILD; END;
*
ERROR at line 1:
ORA-01354: Supplemental log data must be added to run this command
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3669
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3755
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 370
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 157
[code]....
Recently we have upgraded from 11.1 to 11.2 . But after upgrade SQL statements that are running fine in 11.1 was running for hours in 11.2. Statistics are collected 100%...
View 2 Replies View RelatedI am fetching data which are older than 2 hours from now. TRANSACTION_TIME is varchar2 field in "MM/DD/RRRR HH:MI:SS PM" format.
SELECT * FROM TRANSACTION_DETAILS
WHERE TO_DATE(TRANSACTION_TIME,'MM/DD/RRRR HH:MI:SS PM') <(SYSDATE - 2/24);
Is there anything wrong in the query.
format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate
vcActivityName = Process Request
usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)
i need to calculate time elasped for that type of activity following are the rules:
(If Process Request is the activity)
Working Days: Monday through Saturday
Hours of Operation: 9AM 5PM
only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
6+ 8 + 1 = 15 hours + minutes.
format of date time: 2010-09-17 14:50:51.150
vcActivityName = Process Request
Don't worry about process request..
I want to validate dynamic hours value using decode in sql...
If value is greater than 24PM it should display some statement...
if value is less than 0AM it should display some statment...
I have the following code which will produce a list of dates -
SELECT to_date('01-Jan-2013','DD-Mon-YYYY') + rownum - 1
FROM all_objectsWHERE rownum <= to_date('31-Jan-2013','DD-Mon-YYYY') - to_date('01-Jan-2013','DD-Mon-YYYY') + 1 01/01/201302/01/201303/01/201304/01/2013.....30/01/201331/01/2013
But what I'm looking for is a way to also include all possible hours with the dates, so my output would be like -
01/01/2013 0001/01/2013 0101/01/2013 0201/01/2013 03...31/01/2013 2131/01/2013 2231/01/2013 23
Check the following
SQL> ALTER SESSION SET TIME_ZONE = '-01:00';
Session altered.
SQL> SELECT SysTimeStamp
2 FROM dual;
SYSTIMESTAMP
[code]...
My doubt is that why Extract(HOURFROMCURRENT_TIMESTAMP) is showing 11 rather than 10?
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
I have 2 schemas in my application.
1. Application schema
2. EOD(End of day) schema.
End of day schema is populated from Application schema whenever user runs EOD process. The tables are pulled like this.
1. Master tables : Always deleted and reinserted at each EOD process
2. Log tables for each transaction table: Delta between the last EOD and current EOD data is pulled and are used for populating transaction tables
3. Transaction tables: These are populated from log tables pulled from previous step. The logic is like this
Now based on these tables about 30 reports are generated in EOD schema. Please note that each transaction table will have a EOD_ID and any report generated uses the where condition <transaction table>.EOD_ID = <current EOD_ID>
My log table contract_log and transaction table is contract in EOD schema.
contract_log table has data like this
contract_number contract_date customer_idqtyrateeffective_date
11/Jan/2010CUST-0110101/Jan/2010
1NULLNULLNULL112/Jan/2010
EOD on 1st Jan 2010 constructs contract table as
contract_numbercontract_datecustomer_idqtyrateeod_id
11/Jan/2010CUST-011010EOD-1
(Since the change of rate 11 is not visible on 1st Jan 2010 because it is effective on 2nd Jan 2010)
EOD on 2nd Jan 2010 constructs contract table as
contract_numbercontract_datecustomer_idqtyrateeod_id
11/Jan/2010CUST-011011EOD-2
(Since the change of rate 11 is visible on 2nd Jan 2010)
This logic is working fine. But we run more than 20-30 EODs the processing time increased to 10-15 hours.
It took some time to figure out the issue as a single query when run from toad or pl sql developer runs in few seconds but as a part of the whole package it takes 2-3 hours(each query).
The problem found was that oracle execution plan gets corrupted when the process starts. So what we did was to analyze the tables
after they are pulled. This perfectly solved our problem. Currently the whole process is taking only about 12-13 minutes where about 3 minutes is lost on analyze tables and indexes. I know this is a temporary solution as I need to get out of online analyze of tables and indexes.
My code for table and index regeneration is as below
PROCEDURE sp_gather_table_index_stats(pc_table_name VARCHAR2) IS
CURSOR cur_ind IS
SELECT index_name
FROM user_indexes
WHERE table_name = pc_table_name;
BEGIN
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_table_stats(user,' || '''' ||
pc_table_name || '''' || '); end;';
FOR cur_ind_rows IN cur_ind LOOP
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_index_stats(user,' || '''' ||
cur_ind_rows.index_name || '''' || '); end;';
END LOOP;
END;
I currently have a problem where I have two date fields with time stamps. The only bit i am currently interested in in these fields is the time factor. When i display them in their field they have a format of HH24:MI .
I have a start time and end time as well as a duration and duration type. What I am trying to do is the following: when the user inputs the start time, along with the duration say 1 for example and the duration type of say HRS for example I would like to have the end_datetime default to 1 HR from the current start time. This is the code I use on a when validate item trigger to acheive this:
case :blk.duration_type
when 'HRS' then
:blk.end_datetime := :blk.start_datetime + ((1/24)* :blk.duration);
when 'MINS' then
:blk.end_datetime := :blk.start_datetime + ((1/24/60)* :blk.duration);
However, every time it triggers the value put into end_datetime is 0:00 is it something to do with the datatypes im using .
I have a field " Tran_date " with datatype Date . It contains date as well as time . How can I get the time . I tried with it as below :
SELECT TO_CHAR(TRAN_DATE,'DD-MON-YYYY HH24:MI:SS') FROM ABC
WHERE COMP=1 AND
BRANCH=1 AND
LOC_CODE='12' AND
TRAN_DATE='12-JAN-2012' ;
VALUE IS COMING LIKE THIS :
12-JAN-2012 00:00:00
ALTHOUGH THERE IS TIME . Its not zero .
I have a query to add two numbers and get results in hours:minutes format.Example I want to add 12.20 and 6.15 and get result in hours and minutes like 18.35 (hours & minutes).if minutes that is after precision exceed more than 60 it should treat as 1 hour.like i want to add
12.35 (number 1) before precision its hour and after its minutes
06.25 (number 2)
04.25 (number 3)
-----
23.25 (23 hours and 25 minutes)
-----
I want to find the hours and minutes between two char field data type.Example I have two char columns one is "start_time" and another one is "end_time".The start time and end time is the machine reading of CNC MACHINE in manufacturing.I want to develope the package to capture the actual machine running time.But I have the start and end time reading in character field.The machine reading format is hour:minutes:seconds only.It will looks like 1234:45:23(the machine ran 1234 hours and 45 minutes and 23 seconds).See the below table to understand my requirements.
start_time end_time result in hours & minutes
---------- -------- -------------------------
345 347 2 hrs
347 350 3 hrs
350 357.20 7 hrs and 20 minutes
If I subtract end_time - start_time I will get the result in char type only not in hours and minutes format.Another example
start_time end_time result in hours & minutes
---------- -------- -------------------------
357.21 360.40 3.19(If subtract end_time - start_time)