Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

why are named constraints missing after data only refresh?

FBP_FLUORDec 30 2020

i have refreshed our dev database (PEMS) from production using a script that has always worked (12c and earlier) in our new 19c database (first time i have run it on 19c). the procedure is to disable constraints and import across the network link, then re-enable the constraints. However i have found that after the refresh, the only constraints that still exist on the target dev db are the type "C" or check constraints that have names that start with SYS_. I can use Toad to recreate the constraints of non type C in dev but i am wondering:
why are the constraints missing after import?
Is there a way to avoid this circumstance when refreshing data only? Here is my data only refresh shell script:

sqlplus -L / as sysdba @disable_pems_const.sql
sqlplus -L / as sysdba @disable_pems_triggers.sql
impdp / parfile=refresh_data_pems.par
sqlplus -L / as sysdba @enable_pems_const.sql
sqlplus -L / as sysdba @enable_pems_triggers.sql
sqlplus -L / as sysdba @drop_pems_seq.sql
impdp / parfile=dp_pems_sequences.par

Comments

Bawer
how long does it take in sql developer without hint ???
blama
Hi,

the exact times are:
Without Hint, Program(JDBC) as well as SQL Developer: 160s (also for 2nd and 3rd run)
With Hint, Program(JDBC): 160s (also for 2nd and 3rd run)
With Hint, SQL Developer: 7s (1st run), 0.5s (2nd and 3rd run)
Bawer
blama wrote:
the exact times are:
Without Hint, Program(JDBC) as well as SQL Developer: 160s (also for 2nd and 3rd run)
this means you didn't pass the hint (or has been ignored)
post your java code.
blama
Hi Bawer,

that's what I'm thinking. Unfortunately I can't post it, as it is library code (not my lib). But in the debug-output I can see the SQL-String sent to the DB (which does include the hint).

But I find the 2nd option you mention more likely anyway: Even if I put the hint into a VIEW and select from the view, the time-difference is there (it's even there if I use Table Functions/Pipelined table and select from the function).
So I'd think it is more likely that something else is happening (e.g. Oracle is configured in a way that it does not use hints when called from JDBC or similar. Or the library sets some session options in order to prevent the usage of hints). But I don't know if there is even the possibility of doing so.
Does the Oracle JDBC driver have the option to set these options?
Does the Oracle DB have the option to set sth. like "ALTER SESSION SET dontUseHints = 'Y';"
JustinCave
There is no session-level parameter that would tell Oracle to disregard hints. I suppose it is possible that if you were to set some ancient optimizer_features_enable setting that pre-dated a particular hint that the hint might no longer be valid but I've never had occasion to try that. Setting the OPTIMIZER_MODE to RULE might also have some impact. Neither of these things seem particularly likely.

Do you actually get the same SQL_ID when you run the query from JDBC? If you are getting a different SQL_ID, I would tend to suspect that it is much more likely that you have an outline and/or profile attached to the "good" SQL_ID that isn't getting used for the "bad" SQL_ID (assuming that there really isn't some subtle difference other than whitespace between the queries).

Justin
blama
Hi Justin,

thanks for your answer. How can I find out about the SQL_ID? Especially when running the JDBC Query?
As I'm just starting to build the application I'm still on 11.2 XE, so I might not have all features of the full version.

Best regards,
Blama
JustinCave
Assuming you can find the session that executed the query in V$SESSION, SQL_ID will give you the currently executing SQL_ID and PREV_SQL_ID will give you the previously executed SQL_ID. You can also query the V$SQL view to find the SQL statement(s) in question.

Justin
1 - 7

Post Details

Added on Dec 30 2020
#database-key-features, #export-import-sql-loader-external-tables, #import, #refresh
2 comments
545 views
2 mentions