Today's Messages (on)  | Unanswered Messages (off)

Forum: Forms
 Topic: Need example of Hierarchical tree example for Developer 6I
Re: Need example of Hierarchical tree example for Developer 6I [message #678526 is a reply to message #645301] Wed, 11 December 2019 05:05
shahnawazsf
Messages: 2
Registered: July 2019
Junior Member
Thanks but i want to make n level tree like child can have another child. how can i add CHILD AS DEPARTMENT AND OTHER CHILD
Forum: SQL & PL/SQL
 Topic: Materialized Views Cannot set with refresh Fast "Error"
Re: Materialized Views Cannot set with refresh Fast "Error" [message #678527 is a reply to message #678513] Wed, 11 December 2019 05:58
Bill B
Messages: 1919
Registered: December 2004
Senior Member
did you have the rowid in the MVIEW and was it indexed?
 Topic: Using query to Fetch the records along with user defined column value (4 merged)
Re: Using query to Fetch the records along with user defined column value (4 merged) [message #678530 is a reply to message #678479] Wed, 11 December 2019 07:19
Bill B
Messages: 1919
Registered: December 2004
Senior Member
SELECT (Owner || '.') || A.Table_name                            Tablename,
       A.Column_name                                             Columnname,
       CASE WHEN (b.Schema_name IS NULL) THEN 'N' ELSE 'Y' END     Sensitive
  FROM Sys.Dba_tab_columns  A
       LEFT OUTER JOIN Sys.Dba_tsdp_policy_protection_tbl B
           ON     (A.Column_name = B.Column_name)
              AND (A.Table_name = B.Table_name)
              AND (A.Owner = B.Schema_name)
              AND A.Owner = 'my_schema';

[Updated on: Wed, 11 December 2019 07:20]

Report message to a moderator

Forum: Server Administration
 Topic: Database upgrades - with No downtime
Re: Database upgrades - with No downtime [message #678531 is a reply to message #678215] Wed, 11 December 2019 07:47
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What option did you then choose to achieve your goal?

 Topic: Schema Undo
Schema Undo [message #678528] Wed, 11 December 2019 06:14
deepakdot
Messages: 29
Registered: July 2015
Junior Member
Hello,

we have a requirement, In Production we upgrade a schema with DDL and DML, if anything goes wrong, we should undo all the changes. DDL changes will be few which we can prepare a undo scripts. But we deliver a lots of DML statements. For DML Undo, we have this two options now.

option-1 : Prepare UNDO scripts for all the Upgrade DML we deliver (There might be really huge number of scripts)
option-2 : we will just prepare a backup tables for those tables we deliver the data. in case undo is required, we can just copy back from backup tables to the actual table.


we are going to adopt option-2 as this will be simple and less effort. Is this option-2 is acceptable in Production ? is there a best practice how we perform Undo in productions? Any other better options ?

Deepak


Re: Schema Undo [message #678529 is a reply to message #678528] Wed, 11 December 2019 06:25
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
If you have Enterprise Edition licences, Database Flashback might be the easiest, quickest, and most reliable method to take the whole database back to before the upgrade. Or if it is just one schema you could use RMAN to restore and recover the tables from a backup, also straightforward and reliable. In SE2, your options are more limited.
Re: Schema Undo [message #678532 is a reply to message #678528] Wed, 11 December 2019 08:30
JPBoileau
Messages: 81
Registered: September 2017
Member
I do "undo" (we call it "unapply") at my current job.

Every script that gets run during a push will have a matching unapply. It has worked very well for us.

Flashback is not good for us because some of our systems are online during the push and data is getting updated.

JP
Forum: Performance Tuning
 Topic: Select query too slow
Re: Select query too slow [message #678523 is a reply to message #678521] Wed, 11 December 2019 01:31
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
BlackSwan wrote on Tue, 10 December 2019 15:59
Why does SQL that runs in 0.01 seconds need to be faster? How fast is fast enough?

PLEASE use
 
tags in the future.
There is no information about the run time of the statement: the trace does not include the statement, and the exec plan shows only the long outdated "Time" estimate which has been meaningless since release 8i, when CPU costing was introduced.
Re: Select query too slow [message #678524 is a reply to message #678519] Wed, 11 December 2019 01:32
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
On which system did you generate that plan? The old or the new? You need both for the comparison.
Re: Select query too slow [message #678533 is a reply to message #678524] Wed, 11 December 2019 09:00
rajesh_b
Messages: 5
Registered: December 2019
Junior Member
[size=5]I have executed it on the new  system


PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation               | Name                     | Starts| E-Rows | A-Rows |   A-Time   | Buffers |  OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------- -
|   0 | SELECT STATEMENT        |                          |      1|        |      1 |00:04:36.04 |      42M|       |       |         |
|*  1 |  INDEX RANGE SCAN       | NDX_ORGLANG_ACTFLG       |      1|      1 |      1 |00:00:00.01 |       3 |       |       |         |
|   2 |  SORT UNIQUE            |                          |      1|      1 |      1 |00:04:36.04 |      42M|  2048 |  2048 | 2048 (0)|
|   3 |   NESTED LOOPS          |                          |      1|      1 |      2 |00:01:34.97 |      42M|       |       |         |
|   4 |    NESTED LOOPS         |                          |      1|     13 |      2 |00:01:34.97 |      42M|       |       |         |
|   5 |     NESTED LOOPS        |                          |      1|      1 |      2 |00:01:34.97 |      42M|       |       |        |   6 |      NESTED LOOPS       |                          |      1|      1 |      2 |00:01:34.97 |      42M|       |       |        |   7 |       NESTED LOOPS ANTI |                          |      1|      1 |   6840K|00:03:59.84 |      34M|       |       |         |*  8 |        TABLE ACCESS BY INDEX ROWID  |DX_TRNS_REMITTANCE|  1|      1 |   6840K|00:00:54.69 |    6825K|       |       |         |
|*  9 |         INDEX SKIP SCAN             | IDX_TT_BATCHING|    1|      1 |   6840K|00:00:17.30 |   59114 |       |       |         |
|* 10 |          TABLE ACCESS BY INDEX ROWID| DX_MST_SERVICE |  107|      1 |     26 |00:00:00.01 |     214 |       |       |         |
|* 11 |           INDEX RANGE SCAN          | PK_DX_MST_SERVICE|107|      1 |    107 |00:00:00.01 |     107 |       |       |         |
|* 12 |        INDEX SKIP SCAN              | IDX_REJECTED_ENTRY|6840K|   1 |      0 |00:02:55.41 |      27M|       |       |         |
|* 13 |       TABLE ACCESS BY INDEX ROWID   | DX_TRNS_OUTGOING_DETAILS|6840K|1 |   2 |00:00:38.64 |    8032K|       |       |         |
|* 14 |        INDEX UNIQUE SCAN            | IDX_UK_OUTGOING_REF|6840K     |1  3476K|00:00:21.55 |    5521K|       |       |         |
|* 15 |      INDEX RANGE SCAN               | IDX_FILE_ATYPE     |  2|    2 |  2     |00:00:00.01 |       4 |       |       |         |
|* 16 |     INDEX RANGE SCAN                | IDX_FILE_CORR      |  2|   13 |  2     |00:00:00.01 |       4 |       |       |         |
|* 17 |    TABLE ACCESS BY INDEX ROWID      | DX_MST_FILE        |  2|   16 |      2 |00:00:00.01 |       1 |       |       |         |
 ------------------------------------------------------------------------------------------------------------------------------------- 
[/size][/size]

[Updated on: Wed, 11 December 2019 09:10]

Report message to a moderator

Re: Select query too slow [message #678534 is a reply to message #678533] Wed, 11 December 2019 09:09
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
Consistently failing to use [code] tags is extremely rude.
Re: Select query too slow [message #678535 is a reply to message #678534] Wed, 11 December 2019 09:10
rajesh_b
Messages: 5
Registered: December 2019
Junior Member
Really Sorry for the same, I have updated
Re: Select query too slow [message #678536 is a reply to message #678534] Wed, 11 December 2019 09:11
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
In the first reply, click on the 2nd link and read what it says about code tags.
Re: Select query too slow [message #678537 is a reply to message #678536] Wed, 11 December 2019 09:13
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Somethings gone horrible wrong with your formatting. Suggest you fix it.
Re: Select query too slow [message #678538 is a reply to message #678533] Wed, 11 December 2019 09:13
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
That shows the query taking 4min 36sec, whereas in your first post you said 40 minutes. Well, never mind that. You also need to do the same thing on the old server, so that they can be compared.

[Updated on: Wed, 11 December 2019 09:14]

Report message to a moderator

Forum: Marketplace
 Topic: Oracle / PeopleSoft Authorized Training
icon10.gif  Oracle / PeopleSoft Authorized Training [message #678539] Wed, 11 December 2019 13:07
TAMTraining
Messages: 1
Registered: December 2019
Location: Miami, FL
Junior Member
If anyone is in need of training with Oracle / PeopleSoft Applications, please do not hesitate to reach out to us. We are Oracle's 2018 Training Partner of the Year and have over 20 years of training experience. We have training facilities throughout the United State and we can also offer virtual classes.

Please visit our site at www.tamtraining.com or send us an email to info@tamtraining.com

Mention that you saw us on ORAFAQ and receive 10%-20% discount.




Current Time: Wed Dec 11 22:36:09 CST 2019