PL/SQL :: Columns Last_ddl_time And Timestamp In Dba_objects
Apr 17, 2013
i created a procedure called aaa. then i ran create or replace procedure aaa with the same code of the original procedure.i was surprised to see that the fields of last_ddl_time and timestamp haven't change. Of course, when i did this experiment with changing one line in the original package - the fields of last_ddl_time and timestamp did change.The question is if it's suppose to be this way and if so, is there any way to make this fields change even if the "new" code is exectly the same.
I am querying dba_objects view to see invalid objects in my database.It's showing status for some of the materialized views as invalid even though these materialized views are being refreshed on daily basis and being used in application.
I know that to make their status valid I can complile them but I don't understand if they are invalid, how come refresh never failed. Also, their status is valid in dba_snapshots.
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
I am working on a table what has duplicate id with different data set. The problem is this table does not have any time stamp. and this table is created long ago. now I need to remove those records with duplicate id. I want to keep the oldest one. How can retrieve the time stamp of those old data.
From ETL to Oracle, I have stored the timestamp in varchar2 as '30-MAY-11 06.30.00.000000 PM'. Now I need to convert this varchar2(timestamp format) to date. I used:
select TRUNC('30-MAY-11 06.30.00.000000 PM','dd/mm/yyyy hh24:mi') from dualBut, it doest work. get date format?
procedure p1 ( i_time_min number -- minutes to be substracted from timestamp ) is v_end_timeinstamp timestamp(6); begin
[code]....
The problem with above procedure is passing parameter is in minutes and i need to substract the same from sys_extract_utc(current_timestamp) and store result in v_end_timeinstamp in timestamp format only... substracting directly will reduce the days and not the minutes.
In my task I am trying to pass a timestamp datatype as one of the input parameter to a procedure in the package.But I am not sure how to give data for it while executing and testing it from anonymous block.
CREATE OR REPLACE PACKAGE body ac IS PROCEDURE ac_ex_wbdb_keycats( In_Sale_Location_Id IN NUMBER, In_Start_Datatime IN TIMESTAMP, In_Stop_Datatime IN TIMESTAMP, v_refcur OUT sys_refcursor)
Create Or Replace Function Fin_Prd(V_Dte In Date) Return Number Is V_Fin_Str Number; Begin
[Code].....
The above function was running well. Today i have made some change as under
Create Or Replace Function Fin_Prd(V_Dte In Date,V_Rtn_Flg In Number Default 0) Return Number Is V_Fin_Str Number;
[Code]....
Above function is created and working well when i use it in query in sql prompt or Toad. But problem is this that all function which used this are invalid and when i run report whose query use FIN_PRD then error is "Ora-04062. Timestamp of Fin_Prd has been changed".
how to find out the timestamps of a OS command executed previously. "history" command doesn't show the timestamps. I checked "man history" but i couldn't able to find the proper option.
I have a simple query which will return either A or B depending on the projected oven out date and time. If the projected oven out date and time is between 6am and 6pm, A should be returned. Otherwise B if time is between 6pm and 6am of the next day. My problem is that I do not know how to display A or B depending on the projected oven out date and time. I am using the query below to get the projected oven out date and time.
SELECT to_char((ti.txndate + pm.baketime/24),'MM/DD/YYYY HH:MI:SS PM') FCSTDOvenOut FROM CONTAINER c
will Import into a table I am getting the below error message Error Message
Record 1: Rejected - Error on table MTN_BUNDLES_EXPIRY_MIG, column EXPIRY_DATE_T.
ORA-01840: input value not long enough for date format The data client provide in .XLs file 2013-08-31 17:14:56My Table Structure is
CREATE TABLE tmp_mtnuga_3g_expiry_mig ( MSISDN_V VARCHAR2 (50), expiry_Date_t TIMESTAMP(6) WITH TIME ZONE status_date_t TIMESTAMP(6) WITH TIME ZONE GOT_STARTER_PACK_V NUMBER(10) );
I am using 2 option to import into a table First option using Toad ---> Import Table -- option here i am getting error likeThe format is not matched.Second option using SQL Loader-->
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
how to subract the timestamp values.
SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;
Version: 11.2.0.3 Platform : RHEL 5.8 (But I am looking for platform independant solution)
I want to append the timestamp to spooled log file name in SQL*Plus.The spooled log filename should look like
WMS_APP_23-March-2013.logI tried the following 3 methods found in the google. But none of them worked !
I tried this
col sysdt noprint new_value sysdt_var SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL; spool run_filename_&sysdt_var.Logas suggested in
[URL]
and this
spool filename with timestamp col sysdt noprint new_value sysdt SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL; spool run_filename_&sysdt..Logas suggested in
[URL]
and this
column tm new_value file_time noprint select to_char(sysdate, 'YYYYMMDD') tm from dual ; prompt &file_time spool logfile_id&file_time..logas suggested in
Creating a spool file with date/time appended to file name
I get data from one table into a gridview and one of the columns is a date. I get it with date and time stamp but I want solely the date in my gridview.
I am wanting to write a procedure which takes in a TIMESTAMP datatype as a parameter. I only really want the time, not the date part.I am struggling to understand however what format the inserted TIMESTAMP would take. E.g
I am trying to produce results in pivot format. I want to know how many entries per day per month. In other words how many entries on a Monday for each month, how many on a Tuesday for each month and so on.
The main problem I have is that the date column in the database uses UNIX time stamp. I have managed to get the results I want for a particular month. This code selects the entries for last month.
What I need is for another column at the start of the results to give the month so I end up with 12 rows, 1 for each month.
I did try to group by PENTERED(which is the unix time stamp column) select pentered, max(decode(pdayno, 1, cnt, null)) Sunday, max(decode(pdayno, 2, cnt, null)) Monday, max(decode(pdayno, 3, cnt, null)) Tuesday, max(decode(pdayno, 4, cnt, null)) Wednesday, [code]......
This gave me thousands of rows as each UNIX time stamp is unique. Is there a way of grouping on UNIX time stamp.
I have one table with a columntype "timestamp" say xyz is the name of the column.Suppose today is 15th Sept So I want to fetch all those records whose value is between 14th Sept 00:00:00 and 14th Sept 23:59:59.
I'm having trouble using interval data types in a procedure. I need to pass a number of minutes as a parameter, and then use them for arithmetic on a timestamp with time zone. This works no problem:
set serveroutput on create or replace procedure tstz(mins varchar) as begin dbms_output.put_line(systimestamp - interval '10' minute); end; [code]...
I've tried a few variations of data type and type casting for the parameter, but I can't make it work.
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time FROM LOG_TIMESTAMPS where ProdID = 1680988889 group by ProdRequestID ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
