timestamp and synonym
Saradha Bavanandam, February 25, 2003 - 10:52 am UTC
Tom,
CREATE OR REPLACE PROCEDURE command does alter the
LAST_DDL_TIME as well as the TIMESTAMP column eventhough
the specification is not changed.
SQL> create or replace procedure test_timestamp as
2 col1 number;
3 begin
4 col1 := 10;
5 end;
6 /
Procedure created.
SQL> SELECT created,last_ddl_time,timestamp from user_objects where object_name = 'TEST_TIMESTAMP'
/
CREATED LAST_DDL_TIME TIMESTAMP
------------------- ------------------- -------------------
2003-02-25.08.52.32 2003-02-25.08.52.32 2003-02-25:08:52:32
I recompile it again..
SQL> create or replace procedure test_timestamp as
2 col1 number;
3 begin
4 col1 := 10;
5 end;
6 /
Procedure created.
SQL> SELECT created,last_ddl_time,timestamp from user_objects where object_name = 'TEST_TIMESTAMP'
/
CREATED LAST_DDL_TIME TIMESTAMP
------------------- ------------------- -------------------
2003-02-25.08.52.32 2003-02-25.08.54.11 2003-02-25:08:54:11
Our problem is:
A local database procedure which is accessing a remote procedure through a database link is becoming INVALID after the remote procedure had been re-compiled and after that we never able to execute the local procedure at all...
The scenario is.
Assume initially the remote procedure is compiled and also the local procedure compiled and everything is working fine.
And one week-end we re-compile the remote procedure..because
there is some logic change in the procedure.
On Sunday Morning:
REMOTE: SQL> create or replace procedure test_timestamp as
2 col1 number;
3 begin
4 col1 := 10;
5 end;
6 /
Procedure created.
On Monday we noticed that the local procedure "CALL_TIMESTAMP" which is calling the remote "TEST_TIMSTAMP" has become INVALID because of the remote procedure is re-compiled. (which is O.K)
But we could not get the local procedure (CALL_TIMESTAMP)work after that..
In the local database we try to access the remote
procedure...through a public synonym created in the local database for the remote procedure (TEST_TIMESTAMP).
LOCAL:SQL> DESC TEST_TIMESTAMP
02019, 00000, "connection description for remote database not found"
But if I try to qualify like this..gives no problem.
LOCAL:SQL> DESC SCOTT.TEST_TIMESTAMP@REMOTE.ORACLEDB.COM
TEST_TIMESTAMP
The database link and the synonym in the local database are
created like this..
LOCAL:SQL>CREATE PUBLIC DATABASE LINK 'REMOTE.ORACLEDB.COM'
CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'REMOTE.ORACLEDB.COM'
/
LOCAL:SQL>CREATE PUBLIC SYNONYM TEST_TIMESTAMP FOR
SCOTT.TEST_TIMESTAMP@REMOTE.ORACLEDB.COM
/
Synonym Created.
We dropped and re-created the database link as well as the
synonym and still the local procedure "CALL_TIMESTAMP" is
not compiling successfully. It does not give any compilation
error but the STATUS is INVALID.
Just to make sure, we commented the reference to TEST_TIMESTAMP and re-compiled the local procedure and compiled it successfully and the status is VALID. And we put back the TEST_TIMESTAMP reference and re-compiled it and the STATUS has become INVALID with no compilation error.
Finally what we did was,
We created totally a new procedure with a different name
(say TEST_TIMESTAMP_NEW) but with the same source code as
the TEST_TIMESTAMP in the remote database and try to access
the new procedure (TEST_TIMESTAMP_NEW) from the local procedure (CALL_TIMESTAMP) and it is working fine.
We just could not figure out what could be the reason.
Is it something to do with the time delay, the time the REMOTE procedure is re-compiled and the time the local procedure is first executed. In this case the REMOTE procedure got re-compiled on SUNDAY morning and the LOCAL procedure has been first accessed only on Monday.
Please guide us on this...
The remote database is on a HP server and the local database is on a NT server. The database version on both
server is 8.1.7.4
Thanks
Saradha
February 25, 2003 - 7:49 pm UTC
set remote_dependencies_mode to signature
(and use PACKAGES -- never procedures, break that dependency change. no change in spec, great just compile the BODY. it would solve this issues as well)
Saradha Bavanandam, March 04, 2003 - 10:18 am UTC
Tom,
I still do not understant why it is not allowing me to
recompile my local procedure successfully after the remote
procedure is recompiled. I understand the local procedure
is getting invalid after the remote procedure is recompiled. But why I am not able to recompile my local procedure that references the remote procedure. The compilation gives no error but the status remain INVALID.
Does the time delay matters ? I try to execute or recompile my local procedure after more than 12 hours after the remote procedure is compiled.
Thanks
Saradha
March 04, 2003 - 6:29 pm UTC
it shouldn't, you can contact support for that.
compilation and weblogic server
Pushparaj Arulappan, July 30, 2003 - 4:33 pm UTC
Tom,
We have couple of packages and procedures compiled in
our database that are being called from the Java.
These procedures and packages are common that these are
being called by multiple applications.
Whenever we re-compile one of these package, the WEB
application team screams on us. They say, they have to
recylce their weblogic server everytime we recompile
any of these procedures or packages. It seems that the
the connection pool from the wep application keeps the
timestamp of these programs and when it sees it is modified
and it complains.
Could you please give us advice on these. It's only problem
for the WEB application because other application uses VB
and so no problem even we re-compile any number of times.
Is there any settings that we can do, or the wep application
team can do in their weblogic server to take care of this.
July 30, 2003 - 7:21 pm UTC
seems like a weblogic bug does it not? I mean -- why the heck would they do that.
Sorry, don't know what to say -- I cannot modify the code in weblogic.
Does Timestamp change on Auto recompile??
H.S.Anand, July 31, 2003 - 3:05 am UTC
Hi tom,
Does the timestamp of the object change, when it is lying in the database in "invalid" state and is automatically recompiled when a call is placed?
If it does, in such a case, how would one find out when an object was actually modified or state was changed due to an auto-recompile.
Actually in one of the data centers, the librarian asked me this question, because he wanted to know exactly when somebody fired a modified code in the production server??
And I did not have a very convincing answer, b'coz i believe auto recompile also changes the timestamp.
How correct am i? Please advice.
Regards,
Anand
July 31, 2003 - 7:16 am UTC
simple simulation:
ops$tkyte@ORA920> create or replace procedure p
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure p2
2 as
3 begin
4 p;
5 end;
6 /
Procedure created.
<b>P2 is dependent on P</b>
ops$tkyte@ORA920> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA920> select object_name, last_ddl_time from user_objects where object_name in ('P','P2' );
OBJECT_NAME LAST_DDL_TIME
------------------------------ --------------------
P2 31-jul-2003 07:14:55
P 31-jul-2003 07:14:55
<b>both objects are currently valid and compiled "as of" that time</b>
ops$tkyte@ORA920> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> alter procedure p compile;
Procedure altered.
ops$tkyte@ORA920> select object_name, last_ddl_time from user_objects where object_name in ('P','P2' );
OBJECT_NAME LAST_DDL_TIME
------------------------------ --------------------
P2 31-jul-2003 07:14:55
P 31-jul-2003 07:14:58
<b>Now, the compilation of P, which invalidated P2 affects ONLY P's last_ddl_time. P2 hasn't been compiled so its last_ddl_time is unaffected until...</b>
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec p2;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select object_name, last_ddl_time from user_objects where object_name in ('P','P2' );
OBJECT_NAME LAST_DDL_TIME
------------------------------ --------------------
P2 31-jul-2003 07:15:00
P 31-jul-2003 07:14:58
<b>we run it and it compiled itself</b>
So, the last ddl time for an invalid object is the time of its last compile (not when it went invalid).
LAST_DDL_TIME
Ravi, November 06, 2003 - 9:52 am UTC
Tom,
Is there a way to find out what was the last DDL on a table?
Thanks
November 06, 2003 - 5:13 pm UTC
LAST_DDL_TIME in user_objects.
1* select object_name, last_ddl_time from user_objects where object_name = 'T'
ops$tkyte@ORA920LAP> /
OBJECT_NAME LAST_DDL_TIME
------------------------------ --------------------
T 06-nov-2003 10:43:38
ops$tkyte@ORA920LAP> alter table t add yy int;
Table altered.
ops$tkyte@ORA920LAP> select object_name, last_ddl_time from user_objects where object_name = 'T'
2 /
OBJECT_NAME LAST_DDL_TIME
------------------------------ --------------------
T 06-nov-2003 16:44:16
ops$tkyte@ORA920LAP>
Thank you but .. ..
A reader, November 07, 2003 - 9:21 am UTC
Tom,
I was looking for the actual DDL statement itself but not the timestamp. Sorry for the confusion.
Thanks
November 07, 2003 - 10:29 am UTC
you'd have to either
a) enable AUDITING (eg: no code, run some DDL, audit what is done in your database)
b) create event triggers for before alter/create/etc. In 9i you have the function ora_sql_txt to capture the actual ddl, in 8i you can get some, but not all of the information using event attribute functions. See the APPLICATION DEVELOPERS guide (fundementals) for a list of what event triggers you have and the functions available.
How do I exactly know when I ran the create or replace procedure?
Aravind, May 24, 2005 - 10:56 pm UTC
Hi Tom,
I appreciate the good work... keep it up !
I would like to know when I had explicitly executed the "CREATE OR REPLACE PROCEDURE...", for a particular procedure ...
I thought the last_DDL_time would help me to find this. But when I ran EXEC command, the last_ddl_time of the object changes,
because it was put to invalid state by a dependent procedure. Anyway I can explain this better in the example
A.sql and B.sql are 2 simple procedures. A is dependent on B.
/*************************************************************************/
File :- C:DepA.sql
CREATE OR REPLACE PROCEDURE A AS
BEGIN
B;
END;
/
File :- C:DepB.sql
CREATE OR REPLACE PROCEDURE B AS
BEGIN
dbms_output.put_line(' I am happy ');
END;
/
/*************************************************************************/
SQL> @C:DepB
Input truncated to 1 characters
Procedure created.
SQL> @C:DepA
Input truncated to 1 characters
Procedure created.
SQL> SELECT object_name,
2 to_char(created,'dd-Mon-yyyy hh24mi'),
3 to_char(last_ddl_time,'dd-Mon-yyyy hh24mi'),
4 timestamp,
5 status
6 FROM user_objects
7 WHERE object_name IN ('A','B');
OBJECT_NAME CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------ ---------------- ---------------- ------------------- -------
B 25-May-2005 1043 25-May-2005 1043 2005-05-25:10:43:54 VALID (Line 1)
A 25-May-2005 1044 25-May-2005 1044 2005-05-25:10:44:01 VALID (Line 2)
/*************************************************************************/
For both A & B CREATED = LAST_DDL_TIME = TIMESTAMP and that is good.
Now I change the procedure B and remove the semi-colon at the end of
dbms_ouput to make it invalid.
CREATE OR REPLACE PROCEDURE B AS
BEGIN
dbms_output.put_line(' I am happy ')
END;
/
/*************************************************************************/
SQL> @C:DepB
Input truncated to 1 characters
Warning: Procedure created with compilation errors.
SQL> SELECT object_name,
2 to_char(created,'dd-Mon-yyyy hh24mi'),
3 to_char(last_ddl_time,'dd-Mon-yyyy hh24mi'),
4 timestamp,
5 status
6 FROM user_objects
7 WHERE object_name IN ('A','B');
OBJECT_NAME CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------ ---------------- ---------------- ------------------- -------
B 25-May-2005 1043 25-May-2005 1051 2005-05-25:10:51:00 INVALID (Line 3)
A 25-May-2005 1044 25-May-2005 1044 2005-05-25:10:44:01 INVALID (Line 4)
/*************************************************************************/
So far so good.
Now I change the procedure B back to the same old state and make it valid.
CREATE OR REPLACE PROCEDURE B AS
BEGIN
dbms_output.put_line(' I am happy ');
END;
/
/*************************************************************************/
SQL> @C:DepB
Input truncated to 1 characters
Procedure created.
SQL> SELECT object_name,
2 to_char(created,'dd-Mon-yyyy hh24mi'),
3 to_char(last_ddl_time,'dd-Mon-yyyy hh24mi'),
4 timestamp,
5 status
6 FROM user_objects
7 WHERE object_name IN ('A','B');
OBJECT_NAME CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------ ---------------- ---------------- ------------------- -------
B 25-May-2005 1043 25-May-2005 1053 2005-05-25:10:53:07 VALID (Line 5)
A 25-May-2005 1044 25-May-2005 1044 2005-05-25:10:44:01 INVALID (Line 6)
/*************************************************************************/
Till this point A's CREATED = LAST_DDL_TIME = TIMESTAMP(see line 6)
and that is correct.
Now I execute the procedure A.
/*************************************************************************/
SQL> set serveroutput on
SQL> exec A
I am happy
PL/SQL procedure successfully completed.
SQL> SELECT object_name,
2 to_char(created,'dd-Mon-yyyy hh24mi'),
3 to_char(last_ddl_time,'dd-Mon-yyyy hh24mi'),
4 timestamp,
5 status
6 FROM user_objects
7 WHERE object_name IN ('A','B');
OBJECT_NAME CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------ ---------------- ---------------- ------------------- -------
B 25-May-2005 1043 25-May-2005 1053 2005-05-25:10:53:07 VALID (Line 7)
A 25-May-2005 1044 25-May-2005 1055 2005-05-25:10:55:50 VALID (Line 8)
/*************************************************************************/
All of a sudden the LAST_DDL_TIME and TIMESTAMP have been updated and they refer to the time, when I executed procedure A.
There are 2 things,
1. Oracle re-compiling the procedure internally and
2. I explicitly re-define the procedure through "CREATE OR REPLACE PROCEDURE...." command
How do I know, when I had done a explicit DDL.
/*************************************************************************/
Thanks for your response in advance.
Cheers,
Aravind.
May 25, 2005 - 7:25 am UTC
last ddl time is the last compilation time.
if you want to know the last 'create or replace', you'll need auditing OR a custom DDL trigger to capture that event.
How do I exactly know when I ran the create or replace procedure?
Aravindhan, May 25, 2005 - 6:19 pm UTC
Thanks for a quick response Tom...
This is what I wanted to know.
Cheers,
Aravind.
How do I exactly know when I ran the create or replace procedure?
Aravindhan, May 25, 2005 - 6:20 pm UTC
Thanks for a quick response Tom...
This is what I wanted to know.
Cheers,
Aravind.
just what I needed to know - Thanx Tom
Bernhard Schwarz, June 29, 2005 - 10:36 am UTC
Alex, October 19, 2005 - 2:56 pm UTC
Tom,
I am looking for a way to find out who created/compiled objects in the database in addition to when. Is there another view that has this information that I could join all_objects or dba_objects to? Thanks.
October 19, 2005 - 4:31 pm UTC
only if you have auditing enabled would you discover this.
More Info required
Preetish, April 29, 2008 - 8:07 am UTC
can we change the timestamp of an object without dropping and creating again or replacing again.
April 29, 2008 - 8:49 am UTC
what is the goal here, why would you need to do this. What is the underlying problem you are trying to solve here.
How do i know when does the source code of my procedure/function really changes
A reader, July 21, 2008 - 1:02 pm UTC
hi tom,
its a pleasure to read what you wrote. i will go straight to the point now.
In my company, i am not the only person that can access the DB. i am in charge of developing applications for the database.
I need to know, when does the actual coding inside a procedure really changes ..
I realise, for procedures/functions, even if the coding did not change, a create or replace statement will update the timestamp as well.
Is there anyway i know when the coding really changes, just like how it is in the VIEW example you shown where the timestamp changes only if the specifications changes.
Thanks.
P.S i am still holding your expert 1 on 1 Oracle from 8i. Its a classic.
July 22, 2008 - 11:03 am UTC
... I realise, for procedures/functions, even if the coding did not change, a
create or replace statement will update the timestamp as well.
...
not in current releases...
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p
2 as
3 begin
4 null; -- hello
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2> select last_ddl_time from user_objects where object_name = 'P';
LAST_DDL_TIME
--------------------
22-jul-2008 10:48:07
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2)
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> create or replace procedure p
2 as
3 begin
4 null; -- hello
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2> select last_ddl_time from user_objects where object_name = 'P';
LAST_DDL_TIME
--------------------
22-jul-2008 10:48:07
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2)
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> create or replace procedure p
2 as
3 begin
4 null; -- goodbye
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2> select last_ddl_time from user_objects where object_name = 'P';
LAST_DDL_TIME
--------------------
22-jul-2008 10:48:12
it comes back to one thing however - configuration management. The code should be in source code control, all changes logged, commented, versioned.
PLSQL code is code, just like java, C, VB etc...
with regards to when does the source code of my procedure/function really changes on 10g
A reader, July 23, 2008 - 2:21 pm UTC
hi tom,
thanks for the prompt reply.
i have done some testing on my own and yea it works :)
but this are the differences i have note down.
for a create or replace ddl statement
- for 9i views, if specification did not change, only last ddl time change.
- for 9i procedure, if specification did not change, timestamp and last_ddl_time changes too
- for 10g procedures, if specification did not change, neither timestamp nor last_ddl_time will change
i am not sure whether it is right to compare a view with a procedure.
But if you notice, for a 9i view, if its specification did not change with a create or replace statement, the last_ddl_time changes as well.
As you said earlier in the thread
- last_ddl_time is the last time of the last ddl on object, would include CREATE OR REPLACE
So even if the specifications of the view did not change, a ddl was infact issue on it, therefore the last_ddl_time is updated to reflect. This i understand.
However, in 10g procedure
If i issue a create or replace statement which will not change the codings/specifications on it, the last_ddl_time will not reflect the time of the last ddl issue on the procedure
So which is right actually ? to change the last_ddl_time (9i) or remain as it is (10g) when create or replace ddl (that does not change the specifications) is issued
thanks alot tom.
July 24, 2008 - 10:38 am UTC
the only right thing is to use source code control to manage code and grants to restrict who may do what and auditing to be able to review what has taken place.
DDL time stays the same
A reader, February 25, 2009 - 7:40 pm UTC
SQL> SELECT to_char(created,'yyyy-mm-dd hh24:mi:ss') created
2 ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') DDL
3 ,timestamp from user_objects where object_name = 'XXNZTEST';
CREATED DDL TIMESTAMP
------------------- ------------------- -------------------
2009-02-26 00:17:58 2009-02-26 00:20:58 2009-02-26:00:20:58
SQL> CREATE OR REPLACE PACKAGE XXNZTEST AS
2 PROCEDURE run_proc;
3 END XXNZTEST;
4 /
Package created.
SQL> SELECT to_char(created,'yyyy-mm-dd hh24:mi:ss') created
2 ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') DDL
3 ,timestamp from user_objects where object_name = 'XXNZTEST';
CREATED DDL TIMESTAMP
------------------- ------------------- -------------------
2009-02-26 00:17:58 2009-02-26 00:20:58 2009-02-26:00:20:58
SQL> CREATE OR REPLACE PACKAGE XXNZTEST AS
2 PROCEDURE run_proc;
3 END XXNZTEST;
4 /
Package created.
SQL> SELECT to_char(created,'yyyy-mm-dd hh24:mi:ss') created
2 ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') DDL
3 ,timestamp from user_objects where object_name = 'XXNZTEST';
CREATED DDL TIMESTAMP
------------------- ------------------- -------------------
2009-02-26 00:17:58 2009-02-26 00:20:58 2009-02-26:00:20:58
What is explanation for the DDL time? After create or replace it stays the same.
March 03, 2009 - 7:08 am UTC
optimization in 10g - "create or replace package" should really be:
"create or replace only if the code actually changed and we need to replace it package"
ops$tkyte%ORA10GR2> create or replace package test
2 as
3 procedure foo;
4 end;
5 /
Package created.
ops$tkyte%ORA10GR2> SELECT to_char(created,'yyyy-mm-dd hh24:mi:ss') created
2 ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') DDL
3 ,timestamp from user_objects where object_name ='TEST';
CREATED DDL TIMESTAMP
------------------- ------------------- -------------------
2009-03-03 07:02:56 2009-03-03 07:02:56 2009-03-03:07:02:56
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(1);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package test
2 as
3 procedure foo;
4 end;
5 /
Package created.
ops$tkyte%ORA10GR2> SELECT to_char(created,'yyyy-mm-dd hh24:mi:ss') created
2 ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') DDL
3 ,timestamp from user_objects where object_name ='TEST';
CREATED DDL TIMESTAMP
------------------- ------------------- -------------------
2009-03-03 07:02:56 2009-03-03 07:02:56 2009-03-03:07:02:56
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(1);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package test
2 as
3 procedure foo; -- tiny change
4 end;
5 /
Package created.
ops$tkyte%ORA10GR2> SELECT to_char(created,'yyyy-mm-dd hh24:mi:ss') created
2 ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') DDL
3 ,timestamp from user_objects where object_name ='TEST';
CREATED DDL TIMESTAMP
------------------- ------------------- -------------------
2009-03-03 07:02:56 2009-03-03 07:02:58 2009-03-03:07:02:58
When does the DDL timestamp on the package change
Girisha N.S, July 11, 2011 - 10:12 pm UTC
it is usefull but i have one more question reagrdin this topic
When does the DDL timestamp on the package change? Is it only when the specification changes?
July 13, 2011 - 1:32 pm UTC
whenever it is compiled - implicitly or explicitly, for example - explicitly:
ops$tkyte%ORA11GR2> create or replace package my_pkg
2 as
3 procedure p;
4 end;
5 /
Package created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'MY_PKG';
LAST_DDL_TIME
--------------------
13-jul-2011 14:32:04
ops$tkyte%ORA11GR2> pause
ops$tkyte%ORA11GR2> alter package my_pkg compile;
Package altered.
ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'MY_PKG';
LAST_DDL_TIME
--------------------
13-jul-2011 14:32:14
number data to timestamp
Parag, July 15, 2011 - 7:22 am UTC
Hi Tom,
I am using Oracle Enterprise Edition 10.2.0.3.
I have two tables. My question is, I need to convert values of number datatype values (for one particular column from 1st table) to timestamp values (for a column for 2nd table).
For eg.
1310619577 (column in 1st table)
shld be converted to timestamp
i.e. Thu Jul 14 10:29:37 IST 2011 (column in 2nd table)
Can you tell me how can we achieve this.
Thanks,
Parag
July 18, 2011 - 9:33 am UTC
hah, you'll have to tell me what the number "1310619577" represents exactly.
and once you've done that - you'd probably be able to answer the question!
in looking at this, and taking A WILD GUESS, I'll guess that is the number of seconds since midnight jan 1st, 1970 UTC. If so
ops$tkyte%ORA11GR2> SELECT to_date('01011970','ddmmyyyy') + 1/24/60/60 * :theTime,
2 from_tz( cast((to_date('01011970','ddmmyyyy') + 1/24/60/60 * :theTime) AS TIMESTAMP), 'UTC' ) at time zone 'Asia/Calcutta'
3 from dual
4 /
TO_DATE('01011970','
--------------------
FROM_TZ(CAST((TO_DATE('01011970','DDMMYYYY')+1/24/60/60*:THETIME)ASTIMESTAM
---------------------------------------------------------------------------
14-jul-2011 04:59:37
14-JUL-11 10.29.37.000000 AM ASIA/CALCUTTA
IST is ambiguous,
ops$tkyte%ORA11GR2> select * from v$timezone_names where tzabbrev = 'IST';
TZNAME TZABBREV
---------------------------------------------------------------- -----
Asia/Calcutta IST
Asia/Colombo IST
Asia/Dacca IST
Asia/Dhaka IST
Asia/Gaza IST
Asia/Jerusalem IST
Asia/Karachi IST
Asia/Kathmandu IST
Asia/Katmandu IST
Asia/Kolkata IST
Asia/Tel_Aviv IST
Asia/Thimbu IST
Asia/Thimphu IST
Atlantic/Reykjavik IST
Eire IST
Europe/Dublin IST
Iceland IST
Israel IST
18 rows selected.
Number to timestamp
Parag J Patankar, July 18, 2011 - 5:53 am UTC
Hi Tom,
Can you reply to my question ?
thanks & regards
Parag
July 18, 2011 - 11:15 am UTC
can you wait until I get to work next time? In whatever time zone I might happen to be in?
Not that I need to explain, but on July 15th, I spent the day with four different customers from 8am till 3:30pm (worked up quite a head of steam - otherwise known as sweat - walking from place to place in downtown Chicago with all of my luggage between engagements). Then got on a plane home (delicious dinner of chips and a cookie - was good after my single slice of pizza which constituted the only other meal of the day). Then got home at 10pm that night.
Then actually spent the weekend doing weekend things.
Not saying 'overworked', just that I work like everyone else and these "can you reply", "it is so urgent" type of messages - well...
This is not a site to get answers in 30 seconds - sometimes that happens, but I am just one person, I do have a day job.
And next time, when you do ask a question, could you provide a tad more information? I hate having to guess - which I did for this. I think I guessed right - but who knows...
Hi
Parag J Patankar, July 19, 2011 - 12:18 am UTC
Hi Tom,
I am really sorry, if I troubled you or putting you the presure to answer. I asked you again, because I know you are quite a busy person, and in your busy schedule and workload, you missed my question. My intention is not to hurt you or putting a presure.
I am also thankful to you, inspite of irritation you have answered my question.
best regards
parag
Last ddl in 11g
Ram, March 26, 2013 - 4:08 pm UTC
My production DBA has executed a same procedure on both 11g and 10g databases. The procedure was initally created on 2011 under sys user and new version has been loaded today under sys user. Now when I query the dba_objects on 10g database the last_ddl_time and timestamp has been refelected as correct(i.e today's date), But on 11g the last_ddl_time and timestamp is still showing as 2011.
What magic sys user in 11g is doing?
Here are the details for your reference
on 10G
============
created = 03/08/2011 14:09:08
last_ddl_time = 26/03/2013 14:30:07
timestamp=2013-03-26:14:30:07
===========
On 11G
============
created := 03/08/2011 14:10:36
last_ddl_time := 03/08/2011 14:11:03
timestamp := 2011-08-03:14:10:36
March 27, 2013 - 4:58 pm UTC
I'd say two things
a) DO NOT CREATE THINGS AS SYS, JUST STOP IT, DON'T DO IT unless you have a technical reason you cannot do it any other way (and I'd like to hear that.)
b) I'll assume that in 11g, the code was the same - we compare the sources of a procedure and if they are the same we don't overwrite it.
ops$tkyte%ORA11GR2> create or replace procedure p
2 as
3 begin
4 dbms_output.put_line( 'hello world' );
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'P';
LAST_DDL_TIME
--------------------
27-mar-2013 11:43:49
ops$tkyte%ORA11GR2> exec dbms_lock.sleep(3);
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> create or replace procedure p
2 as
3 begin
4 dbms_output.put_line( 'hello world' );
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'P';
LAST_DDL_TIME
--------------------
27-mar-2013 11:43:49
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure p
2 as
3 begin
4 /* changed 1.2.3 */
5 dbms_output.put_line( 'hello world' );
6 end;
7 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'P';
LAST_DDL_TIME
--------------------
27-mar-2013 11:43:52
see how the last ddl time doesn't change until the code was physically modified.
same thing should happen in 10gR2 however.
so, did the code actually change?
and why in the heck would you put it into sys????????????????????????????????????
Timestamp
Mar, April 08, 2013 - 1:35 pm UTC
I compared the procedures( i.e Old and new through beyond compare). The new one's has loaded successfully on 11g but not sure why last_ddl_time and timestamp haven't refelected with new dates.
I got a doubt now looking at the created,last_ddl_time and timestamp values on dba_objects for this procedure. Please see below
created := 03/08/2011 14:10:36
last_ddl_time := 03/08/2011 14:11:03
timestamp :2011-08-03:14:10:36
The created is greater than last_ddl_time. This is OK? . I quried like this
QL> select owner,object_name,object_type,created,last_ddl_time,timestamp,status from dba_objects where trunc(created) > trunc(last_ddl_time);
OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------- ----------- ------------- ------------------- -------
MDSYS SDO_SMPL_GEOMETRY TYPE 18/08/2010 14/10/2002 18 2002-10-14:18:00:44 VALID
MDSYS SDO_REGION TYPE 18/08/2010 01/10/2002 12 2002-10-01:12:41:49 VALID
MDSYS SDO_REGIONSET TYPE 18/08/2010 01/10/2002 12 2002-10-01:12:46:44 VALID
I got only three objects for this under "MDSYS".
We kept this under sys user because the procedure will kill the seesion whenever 'support' team want to kill some seesion.
snippet from proc
if old_version then
cmd := 'ALTER SYSTEM KILL SESSION '''||v_usr_sid||','||v_usr_serial||'''';
else
cmd := 'ALTER SYSTEM DISCONNECT SESSION '''||v_usr_sid||','||v_usr_serial||''' IMMEDIATE';
April 22, 2013 - 2:23 pm UTC
... The created is greater than last_ddl_time.
created := 03/08/2011 14:10:36
last_ddl_time := 03/08/2011 14:11:03
timestamp :2011-08-03:14:10:36
...
no it isn't. created march 8th, 2011 at 2:10pm
last ddled on march 8th, 2011 at 2:11pm.
1 select case when c > l then 'c > l'
2 when c = l then 'c = l'
3 when c < l then 'c <1'
4 else 'i do not know'
5 end txt
6* from (select to_date( '03/08/2011 14:10:36', 'mm/dd/yyyy hh24:mi:ss' ) c, to_date( '03/08/2011 14:11:03', 'mm/dd/yyyy hh24:mi:ss' ) l from dual)
ops$tkyte%ORA11GR2> /
TXT
-------------
c <1
ops$tkyte%ORA11GR2>
the other things you are looking at are internal oracle object stuff - magic - ignore the man behind the curtain. yes, those are fine.
We kept this under sys user because the procedure will kill the seesion
whenever 'support' team want to kill some seesion.that is no reason to use sys.
I can give you a million reasons to NOT use sys - and not a single one to.
you are potentially subject to SQL injection attacks against a procedure owned by sys!!!!
get this out of SYS now, sys is special, sys is magic, the rules of the game do not apply to sys. using sys is beyond dangerous - especially for a routine that does dynamic sql using string concatenation!!!!!!!!
all you need is a schema that has create session, create procedure and alter system granted to it!!!!!!!!!!!!!!!!!!!!!!!!!!
Auditing object changes using TIMESTAMP or LAST_DDL_TIME
Alex Rusinov, December 11, 2013 - 6:56 pm UTC
Hello Tom,
As an auditor I typically obtain a list of database changes from the DBA_OBJECTS view and look for objects with a LAST_DDL_TIME to be in the period in scope of my audit. The DBAs are telling me that I should look at the TIMESTAMP field instead. Based on the conversation in this thread I am not very clear whether either method is consistent for different types of objects. The types that I typically include in my query are ('TABLE','PROCEDURE','RULE','JAVA RESOURCE','VIEW','LIBRARY','FUNCTION','TRIGGER',
'PROGRAM','PACKAGE BODY','PACKAGE'). The DBs in our environment are version 9 through 11 (mostly 11s now).
Thanks in advance!