OraFAQ Forum: SQL & PL/SQL » affected partition

Home » SQL & PL/SQL » SQL & PL/SQL » affected partition (oracle 11.2.0.3 sun solaris 10.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

affected partition [message #598874]

Fri, 18 October 2013 11:01

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Dear All,

How can i get the partition name which was recently loaded. When my load start it truncate the partition and load the data.once the data is loaded there is another load which will read from earlier loaded partition table. i want to know on the fly which partition was loaded recently.

Any clue

Report message to a moderator

Re: affected partition [message #598875 is a reply to message #598874]

Fri, 18 October 2013 11:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can i get the partition name which was recently loaded
look at the code that loaded the data.

Report message to a moderator

Re: affected partition [message #598876 is a reply to message #598875]

Fri, 18 October 2013 11:22

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

code can load any number of partition and old partition as well. is there any way out from dictionary table information

Report message to a moderator

Re: affected partition [message #598877 is a reply to message #598876]

Fri, 18 October 2013 11:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>code can load any number of partition and old partition as well.
Above is correct.
>Is there any way out from dictionary table information
the dictionary does not know or care about this detail.
The only sure way is if the table contains a DATE column for INSERT_DATE and it gets populated by a trigger

Report message to a moderator

Re: affected partition [message #598882 is a reply to message #598874]

Fri, 18 October 2013 11:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

guddu_12 wrote on Fri, 18 October 2013 21:31

How can i get the partition name which was recently loaded. When my load start it truncate the partition and load the data.

Wasn't this already answered by me here http://www.orafaq.com/forum/mv/msg/189859/597938/#msg_597938

Report message to a moderator

Re: affected partition [message #598883 is a reply to message #598874]

Fri, 18 October 2013 12:04

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

guddu_12 wrote on Fri, 18 October 2013 12:01

Any clue

Depends on how far back load was done. Something like this might give you the answer, if you get lucky:

SCOTT@orcl > select scn_to_timestamp(max(ora_rowscn)) from emp;
select scn_to_timestamp(max(ora_rowscn)) from emp
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


SCOTT@orcl > update emp set sal = sal where ename = 'KING';

1 row updated.

SCOTT@orcl > commit;

Commit complete.

SCOTT@orcl > select scn_to_timestamp(max(ora_rowscn)) from emp;

SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
---------------------------------------------------------------------------
18-OCT-13 12.59.08.000000000 PM

SCOTT@orcl >

SY.

Report message to a moderator

Re: affected partition [message #598885 is a reply to message #598883]

Fri, 18 October 2013 12:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Depends on how far back load was done. Something like this might give you the answer, if you get lucky:
It depends upon if concurrent OLTP INSERT are occurring.
It depends upon what problem you are actually trying to solve.
Sometimes when you start out asking the wrong question, it does not matter how good an answer is available to the wrong question.

Report message to a moderator

Re: affected partition [message #598886 is a reply to message #598883]

Fri, 18 October 2013 12:09

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Solomon Yakobson wrote on Fri, 18 October 2013 13:04

if you get lucky

If you really into finding the time check out Tanel Poder's When was a table last changed?.

SY.

Report message to a moderator

Re: affected partition [message #598887 is a reply to message #598885]

Fri, 18 October 2013 12:10

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Isn't the question about knowing partition name for the data loaded. The partition for statement should give that. Or am I missing something?

Report message to a moderator

Re: affected partition [message #598888 is a reply to message #598885]

Fri, 18 October 2013 12:18

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

BlackSwan wrote on Fri, 18 October 2013 13:07

It depends upon if concurrent OLTP INSERT are occurring.

Not sure what you mean. The only difference ORA_ROWSCN will have is ROWDENEDENCIES/NO ROWDEPENDENCIES (default). With NO ROWDEPENDENCIES all rows within same block will return SCN of last time block changed. But it is irrelevent here, since we are looking not for row last change time but rather for table data last change time

BlackSwan wrote on Fri, 18 October 2013 13:07

It depends upon what problem you are actually trying to solve.

Well, your suggestion to add timestamp column would take care of finding last change time in the future and OP might take your advice. However, it doesn't help OP now, does it?

SY.

Report message to a moderator

Re: affected partition [message #598889 is a reply to message #598887]

Fri, 18 October 2013 12:19

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Lalit Kumar B wrote on Fri, 18 October 2013 13:10

Isn't the question about knowing partition name for the data loaded. The partition for statement should give that. Or am I missing something?

Read the question again: i want to know on the fly which partition was loaded recently.

SY.

Report message to a moderator

Previous Topic:	Strange characters in Excel file
Next Topic:	ORA-00904: "EID": invalid identifier

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 09:22:04 CDT 2024