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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle 18c: Global Temporary Table support from Standby

mariam.kupaSep 28 2018 — edited Jan 18 2019

From 18c documentation:https://docs.oracle.com/en/database/oracle/oracle-database/18/sbydb/managing-oracle-data-guard-physical-standby-database…

"DDL Operations

Global temporary tables can be created on, and dropped from, Active Data Guard standby databases. The DDL for these operations is transparently redirected to the primary database. The Active Data Guard session then waits until the corresponding changes are shipped and applied to the Active Data Guard standby"

1. Set temp_undo_enabled=true on primary database:

SQL> alter system set temp_undo_enabled=true;

2. Tried to create global temporary table on standby database:

SQL> CREATE GLOBAL TEMPORARY TABLE tab2(c1 number, c2 varchar(10)) ON COMMIT PRESERVE ROWS;

CREATE GLOBAL TEMPORARY TABLE tab2(c1 number, c2 varchar(10)) ON COMMIT PRESERVE ROWS

*

ERROR at line 1:

ORA-16000: database or pluggable database open for read-only access

Something is not working, I am not able to create Global Temporary Table on the standby database.

This post has been answered by Markus.Michalewicz-Oracle on Jan 16 2019
Jump to Answer

Comments

jgarry

When you say Oracle client, do you mean sqlplus?  If so, try:

set autotrace on

show arraysize

set arraysize 500

select ...

There may be other things to do.  Other clients may have similar settings.  Have you thought about an appropriate index?  Do you really need a distinct?

unknown-7404 
I have a query which is taking a long time to return the results using the Oracle client.


When I run this query on our database server (Unix/Solaris) it completes in 80 seconds.


When I run the same query on a Windows client it completes in 47 minutes.

There are NO queries that 'run' on a client. Queries ALWAYS run within the database server.

A client can choose when to FETCH query results. In sql developer (or toad) I can choose to get 10 rows at a time. Until I choose to get the next set of 10 rows NO rows will be returned from the server to the client; That query might NEVER complete.

You may get the same results depending on the client you are using. Post your question in a forum for whatever client you are using.

Hemant K Chitale

Time spent is on

a) the network to transfer the data (output rows) to the client

b) formatting on the client

You can improve these with

SET ARRAYSIZE

SET PAGESIZE

SET LINESIZE

if using sqlplus as the "client".

If using another client software, check if you can set the arraysize in that client.

Hemant K Chitale

Harmandeep Singh

As others said, time on the sqlplus window will be large when you are getting huge amount of data. Does it makes any practical sense to fetch huge amount of data on the sql plus window on client side as you will not scan more than few hundred rows....

From performance view, you should be bothered at that timings and execution plan at the server level.

Thanks,

Harman

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 15 2019
Added on Sep 28 2018
#data-guard, #performance-availability
16 comments
2,520 views