Feed aggregator

Taking Notes – 2

Jonathan Lewis - 12 hours 27 min ago

[Originally written August 2015, but not previously published]

If I’m taking notes in a presentation that you’re giving there are essentially four possible reasons:

  • You’ve said something interesting that I didn’t know and I’m going to check it and think about the consequences
  • You’ve said something that I knew but you’ve said it in a way that made me think of some possible consequences that I need to check
  • You’ve said something that I think is wrong or out of date and I need to check it
  • You’ve said something that has given me a brilliant idea for solving a problem I’ve had to work around in the past and I need to work out the details

Any which way, if I’m taking notes it means I’ve probably just added a few more hours of work to my todo list.

Footnote

“Checking” can include:

  • having a chat
  • reading the manuals
  • finding a recent Oracle white-paper
  • searching MoS
  • building some models

Philosophy

Jonathan Lewis - 12 hours 33 min ago

Here’s a note I’ve just re-discovered – at the time I was probably planning to extend it into a longer article but I’ve decided to publish the condensed form straight away.

In a question to the Oak Table a couple of years ago (May 2015) Cary Millsap asked the following:

If you had an opportunity to tell a wide audience of system owners, users, managers, project leaders, system architects, DBAs, and developers “The most important things you should know about Oracle” what would you tell them?

I imagine that since then Cary has probably discussed the pros and cons of some of the resulting thoughts in one of his excellent presentations on how to do the right things, but this was my quick response:

If I had to address them all at once it would be time to go more philosophical than technical.

The single most important point: Oracle is a very large, complex, and flexible product. It doesn’t matter where you are approaching it from you will not have enough information on your own to make best use of it. You have to talk to your peer group to get alternative ideas, and you have to talk to the people at least one step either side of you on the technology chain (dev to dba, dba to sysadmin, Architect to dev, dba to auditor etc.) to understand options and consequences. Create 4 or 5 scenarios of how your system should behave and then get other people – and not just your peer group – to identify their advantages and threats.

ORA-01502 with hardcoded index hints

Tom Kyte - 14 hours 30 min ago
We have EBS 12.1.3, which has several indexes, that we disable for bulk loading at night and rebuild them once done. While they are disabled some queries get ORA-01502 because of hard coded index hint on these unusable indexes, although we have skip...
Categories: DBA Blogs

SUBPARTITION ELIMINATION

Tom Kyte - 14 hours 30 min ago
Table with 10 years worth of data Has two key date columns. Queries are always by one of the dates. <b>But never both</b>. So how to get partition elimination to work for both when really can only partition by 1 column. **Thought was - since ...
Categories: DBA Blogs

Issue with DBMS_CRYPTO Encryption method for larger dataset

Tom Kyte - 14 hours 30 min ago
Table Structure <code> CREATE TABLE TEST_PAYLOAD ( CNTNT_PAYLOAD_OID NUMBER(20) NOT NULL, CNTNT_PAYLOAD CLOB, BLOB_CNTNT_PAYLOAD BLOB, ENCRYPT_BLOB BLOB, DECRYPT_BLOB BLOB, CLOB_CNT_PAYLO...
Categories: DBA Blogs

data encryption package

Tom Kyte - 14 hours 30 min ago
Tom: Would you please provide some examples for how to use dbms_obfuscation_toolkit ? -Thx -Reji
Categories: DBA Blogs

Primary and foreign key in temporal tables

Tom Kyte - 14 hours 30 min ago
I have product and supplier table. Both tables have start and end date columns (temporal validity) Relation between product and supplier table is managed via a link table product_supplier_lnk table. I need to know how to manage primary and foreign...
Categories: DBA Blogs

When case counting statement query issue

Tom Kyte - 14 hours 30 min ago
hello Oracle Team I have this test query right now and I have this table. <code>id | cost_center |hardware beni | aaaa |PC123 beni | aaaa |PC234 ari | bbbb |PC456 cari ...
Categories: DBA Blogs

Assumptions

Jonathan Lewis - 14 hours 38 min ago

As the years roll on I’ve found it harder and harder to supply quick answers to “simple” questions on the Oracle-L list server and OTN/ODC forum because things are constantly changing and an answer that may have been right the last time I checked could now be wrong. A simple example of the consequences of change showed up recently on the OTN/ODC forum where one reply to a question started:

Just why do you need distinct in a subquery??? That’s the first thing that appears really shocking to me. If it’s a simple in (select …) adding a distinct to the subquery would just impose a sort unique(as you can see in the explain plan), which may be quite costly.

Three question-marks is already tip-toeing its way to the Pratchett limit – but “really shocking” ? It’s bad enough that the comment goes operatic, but going operatic in order to introduce an error pushes the thing into tragedy (or possibly comedy – or maybe both). To make the self-inflicted injury worse, there were two execution plans supplied in the original post anyway of which only one showed any attempt to achieve uniqueness.

Bottom line – when you’re about to correct someone for doing something that is “obviously” wrong, be a little bit kind about it and then be kind to yourself and do a quick sanity check that your attempt at correction is itself correct. A good guideline would be to ask yourself: “How do I know what I know – and am I about to make myself look like an idiot.”

Check It

Question: Does a  “distinct” in a subquery impose a sort (or hash) unique ?

Answer: No – a uniqueness operation may appear, but it’s not guaranteed to appear.

Here’s a quick example which does not result in any attempt at imposing uniqueness (running 11.2.0.4):


drop table t2 purge;
drop table t1 purge;
create table t1 as select * from all_objects where rownum  <= 100;
create table t2 as select * from all_objects where rownum <= 100;

create index t1_i1 on t1(owner);
create index t2_i2 on t2(object_type);

set autotrace traceonly explain

select  * 
from    t1 
where   owner = 'OUTLN' 
and     object_name in (
                select distinct object_name 
                from   t2 
                where  object_type = 'TABLE'
        )
;


Execution Plan
----------------------------------------------------------
Plan hash value: 3169044451

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     3 |   558 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI              |       |     3 |   558 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     3 |   474 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |     3 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2    |    12 |   336 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2_I2 |    12 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - access("OWNER"='OUTLN')
   5 - access("OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement (level=2)


There’s no sign of a sort unique or hash unique. The optimizer has decided that the IN subquery can be transformed into an EXISTS subquery, which can then be transformed into a semi-join.

I can think of three other execution plan strategies that might have appeared depending on the data, indexing, and statstics:

a) Transform the IN subquery to an EXISTS subquery then operate as a filter subquery (with no uniqueness imposed):


--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |   158 |     5   (0)| 00:00:01 |
|*  1 |  FILTER                      |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     3 |   474 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |     3 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    28 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2_I2 |    12 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

b) Simple unnest with sort/hash unique and join


---------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |   175 |     9 |
|*  1 |  HASH JOIN                     |          |     1 |   175 |     9 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T1       |     4 |   632 |     1 |
|*  3 |    INDEX RANGE SCAN            | T1_I1    |     2 |       |     1 |
|   4 |   VIEW                         | VW_NSO_1 |     4 |    68 |     7 |
|   5 |    SORT UNIQUE                 |          |     4 |   112 |     7 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2       |     4 |   112 |     1 |
|*  7 |      INDEX RANGE SCAN          | T2_I2    |     2 |       |     1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - access("OWNER"='OUTLN')
   7 - access("OBJECT_TYPE"='TABLE')

For this data set I actually had to take the optimizer_features_enable back to ‘8.1.7’ to get this plan – but you can see that there’s a SORT UNIQUE at operation 5, but that would have been there whether or not the DISTINCT keyword had appeared in the SQL. Effectively the query has been transformed to:

select  t1.*
from    (
                select  distinct t2.object_name object_name
                from    t2
                where   t2.object_type='TABLE'
        )
        vw_nso_1,
        t1
where   t1.owner = 'OUTLN'
and     t1.object_name = vw_nso_1.object_name
/

c) Unnest, then “place group by” so that the distinct is applied after the join

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |     3 |   474 |     5  (20)| 00:00:01 |
|   1 |  VIEW                          | VM_NWVW_1 |     3 |   474 |     5  (20)| 00:00:01 |
|   2 |   HASH UNIQUE                  |           |     3 |   594 |     5  (20)| 00:00:01 |
|*  3 |    HASH JOIN                   |           |     3 |   594 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1        |     3 |   510 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_I1     |     3 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2        |    12 |   336 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | T2_I2     |    12 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
   5 - access("T1"."OWNER"='OUTLN')
   7 - access("T2"."OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement (level=2)

Again, the plan would be the same whether or not the original subquery had a redundant DISTINCT. (Again, with this data set, I had to be a bit devious to get this lan).

The things you think you know may have been true 10 years ago – but maybe they’re not true any longer, or maybe they’re still true on your version of the database but not every  version of the database. So I often end up looking at a question, thinking the poster’s claim can’t be right, and then working out and modelling the circumstances that might make the poster’s observations appear (and learning something new).

Remember: “I’ve never seen it before” doesn’t mean “It doesn’t happen”.

 

ODA 12.2.1.2.0 – VLAN Management

Yann Neuhaus - 14 hours 47 min ago

Virtual Local Area Network (VLAN) have become since several years a standard in enterprise class networks. Most enterprises are now segregating their network, especially for security reasons, between server and user or prod and test or applications and backup and aso. In the new release of ODA we finally get the support for VLAN on the bare metal platform. This article will briefly demonstrate how these are managed using ODAADMCLI.

First of all we have to remember that VLAN is not brand new on ODA. While using ODA HA (X5-2 or X6-2) in virtual mode, which means with OVM, it was already possible to manage VLANs. However this was a bit different than the new feature introduced in ODA 12.2.1.2.0.

First of all the HA platform in virtual mode is running using OAKCLI and not ODACLI with the DCS agent. In background the real difference is that the HA in virtualized mode is using the Linux Bridge Control (more details here). To make it simple, you have a kind of virtual switches (bridges) on which each connected VM, including the ODA BASE, can be connected and get an address on this particular network.

On the bare metal platform the principle is totally different as it is directly based on the VLAN implementation in Linux (802.1q standard) which allows activating VLAN on an interface and tagging the packets with the right VLANID.

The first place where VLAN can be configured on the ODA is during the first network plumbing phase, right after booting or re-imaging the ODA, using the configure-firstnet command.

30-ILOM-Configure-FirstNet-VLAN

As shown above the command will ask you if you want to use VLAN or not. Answering YES will then request you to provide the VLANID for the primary network of the ODA and will generate a network interface btbond1.<VLANID>.

How does it looks like in background??

The first layer as always on ODA is a bonding of 2 physical interfaces (here em2 and em3 as I’m using the copper interfaces):

[root@oak network-scripts]# cat ifcfg-em2
#File created by Oracle development
DEVICE=em2
ONBOOT=yes
BOOTPROTO=none
USERCTL=no
TYPE=ETHERNET
ETHTOOL_OFFLOAD_OPTS="lro off"
IPV6INIT=no
NM_CONTROLLED=no
PEERDNS=no
MASTER=btbond1
SLAVE=yes

Looking to the btbond1 interface we will see that it is configure in active-backup mode BUT without any IP address.

[root@oak network-scripts]# cat ifcfg-btbond1
#File created by Oracle development
DEVICE=btbond1
ONBOOT=yes
BOOTPROTO=none
USERCTL=no
TYPE=BOND
BONDING_OPTS="mode=active-backup miimon=100 primary=em2"
IPV6INIT=no
NM_CONTROLLED=no
PEERDNS=no

On top of the bonding configuration, we have then a virtual interface per VLAN. Indeed only one at the beginning as the configure-firstnet generates only the “primary” network of the ODA.

[root@oak network-scripts]# cat ifcfg-btbond1.54
#ODA_VLAN_CONFIG ===
#ODA_VLAN_CONFIG Name=vlan54
#ODA_VLAN_CONFIG VlanId=54
#ODA_VLAN_CONFIG VlanInterface=btbond1
#ODA_VLAN_CONFIG Type=VlanType
#ODA_VLAN_CONFIG VlanSetupType=public
#ODA_VLAN_CONFIG VlanIpAddr=192.168.54.10
#ODA_VLAN_CONFIG VlanNetmask=255.255.255.0
#ODA_VLAN_CONFIG VlanGateway=192.168.54.1
#ODA_VLAN_CONFIG NodeNum=0
#=== DO NOT EDIT ANYTHING ABOVE THIS LINE ===
DEVICE=btbond1.54
BOOTPROTO=none
ONBOOT=yes
VLAN=yes
NM_CONTROLLED=no
IPADDR=192.168.54.10
NETMASK=255.255.255.0
GATEWAY=192.168.54.1

Do not look for the VLANID in the configuration file (of, except in the comments  ;-) ). It is defined by the device/file name.

Once you have your first VLAN you can easily configure additional ones using the command line. Remember that on the DCS stack ODAs you have 2 different CLIs: ODACLI and ODAADMCLI. The VLAN management is done using ODAADMCLI.

So lets have a look to the help:

[root@oda-dbi01 ~]
# odaadmcli -h
Usage: odaadmcli <command> <object> [<options>]
 commands: show|manage|stordiag|power|expand
 objects : disk|diskgroup|controller|server|processor|memory|iraid|
 power|cooling|network|storage|fs|raidsyncstatus|env_hw|vlan

Usage: odaadmcli show - Shows disk, diskgroup, controller, server, processor,
 memory, iraid, power, cooling, network,
 storage, fs, raidsyncstatus, env_hw
 odaadmcli manage - Manages the OAK repository, diagcollect etc.,
 odaadmcli stordiag - Run storage diagnostic tool on this Node
 odaadmcli power - Power on|off|status disk
 odaadmcli expand - Expand storage

Hmmm, looks strange as there is no command CREATE 8-O
I can SHOW the VLANs but it looks like I can’t CREATE them… Let’s have a look to the online documentation (here)

Screen Shot 2018-02-20 at 09.16.48

It looks like the CREATE VLAN command finally exist. A good advice to remember here is that even the inline help of ODACLI and ODAADMCLI are quite good, it is still a good practice to have a look to the online documentation and especially the chapters 14 and 15.

The good news here is that the help for the CREATE command does exist ;-)

[root@oda-dbi01 ~]# odaadmcli create vlan -h
odaadmcli create vlan <vlan_name> -vlanid <vlanid> -if <interface> -node <node_num> -setuptype <type> -ip <ip address> -netmask <netmask> -gateway <gateway>, Where:
	 vlan                  -  VLAN name (unique per Node)
	 vlanid                -  Identifies the ID[valid range: 2 to 4094] to which the VLAN belongs to (unique per Node)
	 interface             -  Interface on which the VLAN is to be created [Valid interfaces are btbond1]
	 node                  -  Node number < 0 >
	 setuptype             -  the type of the VLAN setup for [Valid value are: management, database, dataguard, backup, private and other]
	 ip                    -  IP address for the VLAN
	 netmask               -  Netmask address for the VLAN
	 gateway               -  Gateway address for the VLAN

I guess it’s now time to try to create a new VLAN.

[root@oda-dbi01 ~]# odaadmcli create vlan vlan55-backup -vlanid 55 -if btbond1 -node 0 -setuptype backup -ip 192.168.55.10 -netmask 255.255.255.0 -gateway 192.168.55.1

Created Vlan : vlan55-backup

Note that even on a single node ODA (S or M) you must provide the node number. Otherwise you will get the following error message

ERROR : -node is not available

Let check our newly created VLAN:

[root@oda-dbi01 network-scripts]# odaadmcli show vlan
	NAME                     ID    INTERFACE   CONFIG_TYPE IP_ADDRESS      NETMASK         GATEWAY         NODENUM
	vlan55-backup            55    btbond1     backup      192.168.55.10   255.255.255.0   192.168.54.1    0

Of course in /etc/sysconfig/network-scripts we will find the corresponding IFCFG file:

[root@oda-dbi01 network-scripts]# ls -lrt
total 248
-rw-r--r--. 1 root root 29853 Apr 12  2016 network-functions-ipv6
-rw-r--r--. 1 root root 14731 Apr 12  2016 network-functions
...
...
...
-rw-r--r--. 1 root root   264 Feb 19 11:40 ifcfg-lo
-rw-r--r--  3 root root   194 Feb 19 12:04 ifcfg-em3
-rw-r--r--  3 root root   194 Feb 19 12:04 ifcfg-em2
-rw-r--r--  3 root root   169 Feb 19 12:04 ifcfg-em1
drwxr-xr-x  2 root root  4096 Feb 19 12:04 backupifcfgFiles
-rw-r--r--. 3 root root   259 Feb 19 12:17 ifcfg-btbond1
-rw-r--r--  1 root root   538 Feb 19 14:43 ifcfg-btbond1.54
-rw-r--r--  1 root root   538 Feb 19 15:31 ifcfg-btbond1.55

Should a VLAN not be required anymore, deleting it works straight forward using DELETE VLAN:

[root@oda-dbi01 network-scripts]# odaadmcli delete vlan vlan55-backup -node 0

Deleted Vlan : vlan55-backup

Easy isn’t it? 8-)

Last but not least, for those who deployed their ODA without VLAN do not worry you won’t need to re-image it. Even if the ODA has been deployed without VLAN during the configure-firstnet you can still create VLAN afterwards.

Enjoy!

 

Cet article ODA 12.2.1.2.0 – VLAN Management est apparu en premier sur Blog dbi services.

European Indexing Internals Seminar Events: Please Help Me Select (Station To Station)

Richard Foote - Mon, 2018-02-19 19:35
I’m currently determining which European countries to initially target for a series of my 2 day Oracle Indexing Internals and Best Practices seminars tentatively scheduled for the late May / early June time frame. The cost for the 2 day seminar is to be confirmed but will be approximately 1200 Euros (plus any local taxes), […]
Categories: DBA Blogs

How Do You Create Data Guard Configuration?

Michael Dinh - Mon, 2018-02-19 17:30

I have taken for granted to create Data Guard Configuration the same way most of the time that I don’t know what goes wrong with done differently.

oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 19 23:41:49 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

23:41:49 SYS @ hawk1:>show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawk
pdb_file_name_convert                string

23:42:04 SYS @ hawk1:>alter system set dg_broker_start=true sid='*' scope=memory;

System altered.

23:42:40 SYS @ hawk1:>


+++ CREATE CONFIGURATION USING UPPER CASE WITHOUT QUOTES

oracle@racnode-dc1-1:hawk1:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.

--- NO QUOTES USE AND ALL UPPERCASE - EASIEST METHOD
--- Broker convert database to match that of db_unique_name

DGMGRL> CREATE CONFIGURATION DG_CONFIG AS PRIMARY DATABASE IS HAWK CONNECT IDENTIFIER IS HAWK;
Configuration "dg_config" created with primary database "hawk"

DGMGRL> show configuration

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

--- CONFIGURATION IS UPPERCASE 
--- Does it look better in uppercase?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'hawk' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "hawk"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

+++ ISSUES OCCUR WHEN USING UPPERCASE WITH QUOTES FOR DATABASE
+++ Not sure if this will work as I have not tested end to end. Why create it this way to begin with?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'HAWK' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "HAWK"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  HAWK - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk
Object "hawk" was not found

DGMGRL> show database HAWK
Object "hawk" was not found

DGMGRL> show database 'HAWK';

Database - HAWK

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL>

REFERENCE:

CREATE CONFIGURATION

CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS database-name CONNECT IDENTIFIER IS connect-identifier;

database-name
The name that will be used by the broker to refer to the primary database. 
It must match (case-insensitive) the value of the primary database DB_UNIQUE_NAME initialization parameter.

Select query on two calculated fields, one depends on other calculated field

Tom Kyte - Mon, 2018-02-19 08:46
SELECT A, B, A+B AS C, CASE C (I don't want to use again A+B here its lengthy query) ------ ----- ------ END As D FROM TABLE So here C is a calculated field, I want use result of C to calculate other field D. How can I do this without ha...
Categories: DBA Blogs

Optimization of view using union on 2 tables of different schema

Tom Kyte - Mon, 2018-02-19 08:46
Hi, I need to get data of table from 2 different schema . So i have created a view which will have union of 2 tables of 2 different schema . E.g. create or replace view acct_details as select column1,column2..column n from Table1 Un...
Categories: DBA Blogs

IF ELSE statements within COLUMN/HAVING elements within SELECT queries

Tom Kyte - Mon, 2018-02-19 08:46
Hi there, I'm trying to convert quite a few SQL queries from MS Access to PL SQL. Annoyingly a lot of them have VBA in them. Any tips on the best option to replicate this VBA in the select statement? I could use a variable but then I'm not sure...
Categories: DBA Blogs

Can Oracle automatically gather session specific stats for a GTT?

Tom Kyte - Mon, 2018-02-19 08:46
Hi Team, First of all, Thanks a lot for all the great work you are doing! It would be great if you can help me with a query. While reviewing AWRSQL report of a SQL I noticed that session specific stats were used for that execution. This SQL is ...
Categories: DBA Blogs

Options to load binary file and insert the data in a BLOB column in one pass

Tom Kyte - Mon, 2018-02-19 08:46
Nowadays when we have a BLOB column to be loaded with the content of a given file, we do : INSERT INTO table(columnPK, column2...columnBLOB) values (PKidtobeinserted, ...., empty_blob()) RETURNING PKidvalue; and later we call a procedure called...
Categories: DBA Blogs

table partition exchange

Tom Kyte - Mon, 2018-02-19 08:46
Hi Team, we have situation where we have two partition table (range partition ) ,example table A1 ,A1_history . application team want to archive all the partition except last 3 partitions , from Current production table to archival table . ...
Categories: DBA Blogs

Delay in row commit or Uncommited rows by Application

Tom Kyte - Mon, 2018-02-19 08:46
Hi Tom, Application(.Net) component is calling SP and creates an entry into few tables and sometimes there is delay in committing these transactions by .Net component or there is transaction left uncommitted in Oracle DB. we are aware of this issu...
Categories: DBA Blogs

Rebuilding all the unusable index

Tom Kyte - Mon, 2018-02-19 08:46
Hi Tom I really need to identify all the failed or unusable indexes in all schema in database and also to rebuild it. Kindly provide a query for both. im using SQL DEVELOPER TOOL in ORACLE 12c.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator