Feed aggregator

Reset your Datasources

Darwin IT - Mon, 2016-07-11 05:09
In most SOASuite and Oracle ServiceBus projects the Database Adapter is used. And often it is used to invoke PL/Sql functions and procedures. Actually, it's my favorite interaction method with the database, since the re-entrancy of the database adapter wizard is best there. To do a re-entrant update of a select or DML operation when for instance a column is added, is simply put often quite problematic.

But the thing is with Pl/Sql that when you update a pl/sql package the package state is altered and when calling it from another session you'll get a 'ORA-04068: existing state of packages has been discarded' error. And this is will occur for every connection in the pool of your datasource.

The solution is to reset the datasource. This can be done easily in the WebLogic Administration Console (http://adminserver:port/console). But nowadays you can do it as easily in Enterprise Manager Fusion Middleware Control. This can be quite convenient for developers since often you have EM already open because of your SOASuite tests.

To do this open the Weblogic Domain menu and select the option 'JDBC DataSources':
 Select the DataSource you want to reset, for the example I choose the 'EDNDataSource', but probably you'd not do this for one, but for a custom DataSource:

 Click the Control tab, select the DataSource in the table and click Reset:

Reset will drive WebLogic to recreate all the connections in the DataSource. This prevents popping up the message multiple times.

The functionality in Configuring, Monitoring and Controlling the Datasource  is similar as in the WebLogic Admin console. Only the layout is a little different.

Question on multiple DML in FORALL

Tom Kyte - Mon, 2016-07-11 04:46
Hi, I have below question on FORALL 1) <code> forall 1 .. l_var.count delete tab1 where id=l_var(i); forall 1 .. l_var.count delete tab2 where id=l_var(i); forall 1 .. l_var.count delete tab3 where id=l_var(i); </code> 2)...
Categories: DBA Blogs

Error when import large data from 11.2.0.3 into 11.2.0.4 Oracle Exadata

Tom Kyte - Mon, 2016-07-11 04:46
Hi, We hit this and no clue how it suddenly happen. Issue cronology as below (using Toad version 10.2, 10.5 and 11). 1) Export out from Exadata DB version 11.2.0.3 table A with 4million records - ended successfully. 2) Import into Exadata DB ...
Categories: DBA Blogs

Adding subpartitions

Tom Kyte - Mon, 2016-07-11 04:46
Hello Sir I have a table partitioned on RANGE (daily) (interval partitioning) - there are about 300 partitions . Now I want to add a sub-partition to each of these based on a value in a different column. Should I come up with a script with 300 A...
Categories: DBA Blogs

Distinct Values from Multidimensional Collection

Tom Kyte - Mon, 2016-07-11 04:46
Hi Tom, Is there a way to retrieve Unique records from a multidimensional collection? DISTINCT and MULTISET operators seem to work on collections only when they have 1 field. I have the below <b>TYPE TYP_TABLE_REC IS RECORD ( SCHEMA_NA...
Categories: DBA Blogs

Best Way to Design a Table

Tom Kyte - Mon, 2016-07-11 04:46
I've to design Professional Tax table for Payroll(INDIA) , For every state it has its own prof tax according to their gross salary Consider One state Say MAHARASHTRA IF PR-GROSS < 2500.00 TAX= 30.00 IF PR-GROSS < 3500.00 TAX= 60.00 IF PR...
Categories: DBA Blogs

oracle SQL — Replace comma separated string with another set of values

Tom Kyte - Mon, 2016-07-11 04:46
I have the following table: <code>B_ID I_ID R_ID W00001 1234 1235,1237 B00001 1235 1236,1235 T00001 1236 1235,1235,1235 X00001 1237 1234,1236,1238 M00001 1238 1238</code> I need output like below using sql <code>B_ID I_ID New...
Categories: DBA Blogs

Clob and minus - alternatives for Diff Deltas

Tom Kyte - Mon, 2016-07-11 04:46
Hello Great Asktom Team, We can't do a minus query with a clob column. So what are the alternatives. I have ETL process which finds deltas of table based on minus. Can't do that with a column having data type clob So how to do it in pure...
Categories: DBA Blogs

Table creation script

Tom Kyte - Mon, 2016-07-11 04:46
Hai Tom sir...I have doubt. 1)How can i create this table.see below. RED GREEN YELLOW AAA 1 2 3 BBB 4 5 6 CCC 7 8 9 Please create this one.. and more question only... 2)How can in write a quer...
Categories: DBA Blogs

RFS not starting for standby

Tom Kyte - Mon, 2016-07-11 04:46
HI, what could be the reason for RFS not starting at standby. anything specific I have to look for. DR:- SQL> select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby; SQL> INST_ID PROCESS STATUS ...
Categories: DBA Blogs

Loading SQL*Plus HELP into the Database

Hemant K Chitale - Sun, 2016-07-10 22:39
Oracle provides scripts to load the HELP command for SQL*Plus.

See $ORACLE_HOME/sqlplus/admin/help

The schema to use is SYSTEM, not SYS.

I demonstrate
(a) How to load SQLPlus Help  into the database
(b) How to customise the Help (e.g. add new commands)

[oracle@ora11204 help]$ cd $ORACLE_HOME/sqlplus/admin/help
[oracle@ora11204 help]$ ls -l
total 84
-rwxrwxrwx. 1 oracle oracle 265 Feb 17 2003 helpbld.sql
-rwxrwxrwx. 1 oracle oracle 366 Jan 4 2011 helpdrop.sql
-rwxrwxrwx. 1 oracle oracle 71817 Aug 17 2012 helpus.sql
-rwxrwxrwx. 1 oracle oracle 2154 Jan 4 2011 hlpbld.sql
[oracle@ora11204 help]$ sqlplus -S system/oracle @helpbld.sql `pwd` helpus.sql
...
...
...
View created.


58 rows created.


Commit complete.


PL/SQL procedure successfully completed.

[oracle@ora11204 help]$


The 'pwd`  (note the back-quote character, not the single quote character) is a way of specifying the current directory in Unix and Linux shells.   This specifies where the help datafile is located.  helpus.sql is the help data in English (US-English).

The scripts create a table called "HELP" in the SYSTEM schema.  SQL*Plus's "HELP" command then uses this table.

Examples :

SQL> connect hemant/hemant
Connected.
SQL> help

HELP
----

Accesses this command line help system. Enter HELP INDEX or ? INDEX
for a list of topics.

You can view SQL*Plus resources at
http://www.oracle.com/technology/documentation/

HELP|? [topic]


SQL>
SQL> help set

SET
---

Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

APPI[NFO]{OFF|ON|text} NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n} NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} NUMF[ORMAT] format
AUTOP[RINT] {OFF|ON} NUM[WIDTH] {10|n}
AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text}
[EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED]|EA[CH]|OFF}
BLO[CKTERMINATOR] {.|c|ON|OFF} RECSEPCHAR {_|c}
CMDS[EP] {;|c|OFF|ON} SERVEROUT[PUT] {ON|OFF}
COLSEP {_|text} [SIZE {n | UNLIMITED}]
CON[CAT] {.|c|ON|OFF} [FOR[MAT] {WRA[PPED] |
COPYC[OMMIT] {0|n} WOR[D_WRAPPED] |
COPYTYPECHECK {ON|OFF} TRU[NCATED]}]
DEF[INE] {&|c|ON|OFF} SHIFT[INOUT] {VIS[IBLE] |
DESCRIBE [DEPTH {1|n|ALL}] INV[ISIBLE]}
[LINENUM {OFF|ON}] [INDENT {OFF|ON}] SHOW[MODE] {OFF|ON}
ECHO {OFF|ON} SQLBL[ANKLINES] {OFF|ON}
EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] |
EMB[EDDED] {OFF|ON} LO[WER] | UP[PER]}
ERRORL[OGGING] {ON|OFF} SQLCO[NTINUE] {> | text}
[TABLE [schema.]tablename] SQLN[UMBER] {ON|OFF}
[TRUNCATE] [IDENTIFIER identifier] SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
ESC[APE] {\|c|OFF|ON} SQLPRE[FIX] {#|c}
ESCCHAR {@|?|%|$|OFF} SQLP[ROMPT] {SQL>|text}
EXITC[OMMIT] {ON|OFF} SQLT[ERMINATOR] {;|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF} SUF[FIX] {SQL|text}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} TAB {ON|OFF}
FLU[SH] {ON|OFF} TERM[OUT] {ON|OFF}
HEA[DING] {ON|OFF} TI[ME] {OFF|ON}
HEADS[EP] {||c|ON|OFF} TIMI[NG] {OFF|ON}
INSTANCE [instance_path|LOCAL] TRIM[OUT] {ON|OFF}
LIN[ESIZE] {80|n} TRIMS[POOL] {OFF|ON}
LOBOF[FSET] {1|n} UND[ERLINE] {-|c|ON|OFF}
LOGSOURCE [pathname] VER[IFY] {ON|OFF}
LONG {80|n} WRA[P] {ON|OFF}
LONGC[HUNKSIZE] {80|n} XQUERY {BASEURI text|
MARK[UP] HTML [OFF|ON] ORDERING{UNORDERED|
[HEAD text] [BODY text] [TABLE text] ORDERED|DEFAULT}|
[ENTMAP {ON|OFF}] NODE{BYVALUE|BYREFERENCE|
[SPOOL {OFF|ON}] DEFAULT}|
[PRE[FORMAT] {OFF|ON}] CONTEXT text}


SQL>
SQL> help show

SHOW
----

Shows the value of a SQL*Plus system variable, or the current
SQL*Plus environment. SHOW SGA requires a DBA privileged login.

SHO[W] option

where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
| VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
RECYC[LEBIN] [original_name]
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SPPARAMETERS [parameter_name]
SQLCODE
TTI[TLE]
USER


SQL>
SQL> help connect

CONNECT
-------

Connects a given username to the Oracle Database. When you run a
CONNECT command, the site profile, glogin.sql, and the user profile,
login.sql, are processed in that order. CONNECT does not reprompt
for username or password if the initial connection does not succeed.

CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA|SYSASM}] [edition=value]]

where logon has the following syntax:
username[/password][@connect_identifier]

where proxy has the syntax:
proxyuser[username][/password][@connect_identifier]
NOTE: Brackets around username in proxy are required syntax


SQL>


Remember !  These are SQL*Plus commands, not SQL Language commands.  So you won't see help about CREATE or ALTER or SELECT and other such commands.

Since, it uses a plain-text file (helpus.sql in this case) to load the help information, it is possible to extend this.

For example, I copy helpus.sql as helpcustom.sql and add these lines into the scrip file :

INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 2, 'This Hemant''s Test Database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 3, 'A Playground database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 4, 'Running 11.2.0.4 on Linux');

INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 2, 'Test Database owned by Hemant');
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 2, 'Various Experiments by Hemant');

INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 2, 'Hemant K Chitale');
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 3, 'https://hemantoracledba.blogspot.com');

COMMIT;


and then I run the command :

sqlplus -S system/oracle @helpbld.sql `pwd` helpcustom.sql


And view the results :

SQL> connect hemant/hemant
Connected.
SQL> help dbinfo

This Hemant's Test Database
A Playground database
Running 11.2.0.4 on Linux

SQL> help ownerinfo

Test Database owned by Hemant

SQL> help who is hemant

Hemant K Chitale
https://hemantoracledba.blogspot.com

SQL>
SQL> help startup

STARTUP
-------

Starts an Oracle instance with several options, including mounting,
and opening a database.

STARTUP options | upgrade_options

where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] |
NOMOUNT ]

where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER

and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]


SQL> help shutdown

SHUTDOWN
--------

Shuts down a currently running Oracle Database instance, optionally
closing and dismounting a database.

SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]


SQL>


And, so, the SQL*Plus HELP command can be customised !

.
.
.

Categories: DBA Blogs

Oracle Multitenant feature name

Yann Neuhaus - Sun, 2016-07-10 15:46

We all know what is the multitenant option: a container database (CDB) with multiple user pluggable databases (PDB). This requires Enterprise Edition plus option. But you can use the same architecture without the option and even in Standard Edition: a CDB with only one user PDB. It is called “single-tenant” or “lone PDB”. How do you call this new 12c architecture?

I call “multitenant architecture” the following set of features, available in all editions:

  • dictionary separation between system metadata/objects and user metadata/object
  • unplug / plug features to transport PDBs
  • create a new PDB as a clone of another existing PDB
  • ability for a session to switch between PDB and CDB$ROOT explicitly with ‘ALTER SESSION’ or implicitly through metadata and object links
  • ability to read data for several containers in one query

But it may look strange to use the “multitenant” term when in Standard Edition or Enterprise edition without option.

Container database (CDB)

One idea would be to simply call it ‘CDB architecture’, because it’s the architecture of the container database, and because a database without this architecture is called “non-CDB”:

@franckpachot Oracle docs says „CDB architecture“ and „Multitenant Architecture“ – I prefer CDB, because there’s Non-CDB, too :)

— Markus Flechtner (@markusdba) July 8, 2016

consolidated database (CDB)

However, as very often with Oracle, the names change and the acronyms remain. Old names are found in the software, before new name is made public to market it. We all know that CDB stands for “container database” because a CDB is a container that contains at least 3 containers (CDB$ROOT, PDB$SEED and your PDB) and more when you have the multitenant option. However I see no mention of that name in ORACLE_HOME/rdbms/admin scripts:
[oracle@CDB ~]$ grep -i "container database" $ORACLE_HOME/rdbms/admin/* | wc
0 0 0

It seems that the original meaning of CDB was “consolidated database”:

[oracle@CDB ~]$ grep -i "consolidated database" $ORACLE_HOME/rdbms/admin/* | wc
58 465 7276
 
[oracle@CDB ~]$ grep -i "consolidated database" $ORACLE_HOME/rdbms/admin/*.bsq
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dcore.bsq:REM Consolidated Database.
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dcore.bsq:REM SEED Pluggable Database in a Consolidated Database.
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/doptim.bsq:Rem the caching in library cache in a Consolidated Database.
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dsec.bsq: * Consolidated Database
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dsec.bsq: * Consolidated Database

So, the idea behind CDB is the same as behind multitenant: it’s consolidation of several pluggable databases. And then, consolidation is not really what is done by single-tenant where we have only one user PDB per CDB and where this architecture requires 3 containers instead of one non-CDB.

Pluggable databases

Another idea would be to call it “pluggable databases architecture” because this is the real evolution. User tablespaces are transportable for a long time, since 8i introduction of extended rowid and locally managed tablespaces. 12c brought the same for system tablespaces so that PDBs are fully transportable physically, thanks to separation of dictionary.

The real point of this new architecture is the total separation of system data/metadata and user data/metadata, the separation of system DBA role and application DBA role, and this is pluggable databases. Multitenancy is just one thing that is possible with this new architecture. It’s important today because it’s required for the cloud (for consolidation and easy provisioning).

Oracle Multitenant

Actually, the feature was called ‘Oracle Pluggable Database’ until 12.1.0.1 was released publicly with a new name: Oracle Multitenant. And this is the name we can see in Database Feature Usage Statistics. Well, if you look at the first patchset of 12cR1, 12.1.0.2, you will see the old name ‘Oracle Pluggable Databases’ but this is a bug (Patch 20718081 changes back the name).

Here is what you see from Database Feature Usage Statistics after an upgrade from 12.1.0.1 to 12.1.0.2, in EM Express:

CaptureFUMultitenant

And from DBA_FEATURE_USAGE_STATISTICS view:


SQL> select name, version, detected_usages, currently_used, aux_count from dba_feature_usage_statistics where name like '%Multitenant%' or name like '%Pluggable%';
 
NAME VERSION DETECTED_USAGES CURRE AUX_COUNT
------------------------------ ----------------- --------------- ----- ----------
Oracle Multitenant 12.1.0.1.0 98 FALSE 1
Oracle Pluggable Databases 12.1.0.2.0 32 FALSE 1

So, bug aside, the name of the feature is ‘Oracle Multitenant’ and this is true for any container database, because the feature is considered as used as soon as V$DATABASE.CDB=’YES':
select count(*) into feature_boolean from v$database where cdb = 'YES'

The usage of the multitenant option is detected by the AUX_COUNT which is the number of user PDBs:
select count(*) into aux_count from v$pdbs where con_id > 2
CON_ID=0 is for the CDB, CON_ID=1 is for CDB$ROOT, CON_ID=2 is for PDB$SEED and user PDBs start at CON_ID > 2

So the name is “multitenant” whatever the number of PDBs.

So what?

I prefer to stick with “multitenant architecture” even when used without the multitenant option. It’s a good way to keep in mind that, even if it brings lot of interesting features for single-tenant as well, Oracle has taken the step of dictionary separation with the motivation of Cloud, Consolidation and Multitenancy. Charged options are a good trigger to increase priority of evolution requests…
However, this “multitenant architecture” brings very interesting features to Standard Edition and Enterprise Edition even without option. And it you doubt, I’ll try to convince you in San Francisco, September 18th.

 

Cet article Oracle Multitenant feature name est apparu en premier sur Blog dbi services.

SQL questions for improving skills

Tom Kyte - Sun, 2016-07-10 10:26
Hi Tom, My question is little weird. Where can i find good SQL questions (scenarios) for practice. I tried google but did not find good list. Can you please suggest any website or book/magazine? Thanks, DP
Categories: DBA Blogs

How to register a "service" with a listener without using local_listener

Tom Kyte - Sun, 2016-07-10 10:26
Hello I create a service like so: exec dbms_service.create_service('MYSERVICE','MYSERVICE'); exec dbms_service.start_service('MYSERVICE'); Then, I add the service in the listener.ora sid_list parameter like so: SID_LIST_LISTENER = (SI...
Categories: DBA Blogs

Politicians won't move on climate change cause they know we don't REALLY care.

FeuerThoughts - Sun, 2016-07-10 10:23


Yep. That's the truth (at the least the truth that seems to be taking shape between my ears these days).

I've been thinking about the Great Pacific Garbage Patch of late....


Lots of us seem to know it exists, and we are disgusted by it. Disgusted by us - humans who are disastrously trashing our planet.

And what are we going to do about it?

We are going to demand that Congress DO SOMETHING!

And our demands are going to be expressed in extremely powerful ways:
  • Online petitions
  • Facebook rants
  • Lots and lots of outraged tweets
Oh yes. Those. Lots of them, lots of indignation, shared outrage, thank you Facebook Echo Chamber.

And yet, and yet...somehow those awful Congresspeople ignore the Will of the People. How can this be? 

Time for more outraged and indignant rants and sarcastic memes on Facebook.

How ridiculous on two fronts:

1. Online "activism" is largely ineffective. 

2. Politicians will only listen to us when we take action that demonstrates our seriousness.

And this is where we really fall short.

So you read about all the awful plastic clogging up our oceans, killing fish and whales and dolphins and....everything, really, just about everything.

And what do you actually do?

Do you change even one iota of the way you live your life? It doesn't seem that way to me. We bitch and moan for a while, and then watch Game of Thrones or go to Six Flags or buy another case of plastic bottled water.

And since we don't seem to be willing to make the smallest sacrifices in our lives, politicians know they can just keep on serving their real masters: lobbyists of corporations.

Let's face it: if you consume and discard plastic, it's going somewhere, and it's going to be nasty, no matter the location. 

But if you don't consume that plastic, you will have not contributed to the problem. You will have not made things worse. And if millions of people did this same thing - took action in their life to change patterns of consumption - the impact would be enormous.

Here are some of the things I do to avoid plastic consumption:

1. I never, never, NEVER (well, hardly ever) buy plastic bottled water. And I especially never buy cases of plastic bottled water that is wrapped in plastic. How grotesque. Instead, buy a glass or stainless steel bottle and refill the damn thing, people.

2. I hardly ever buy processed food. I mostly buy food, like broccoli and fruit and eggs. Sure, they all require some processing. But nothing like buying a Lunchable. So gross.

3. I travel with a set of bamboo "silverware" so I can avoid using plastic-wrapped plastic forks and knives. I so detest those.

4. When I get ice cream, I get a cone: no need for a plastic dish, no plastic spoon. Of course, if I go to a lovely ice cream shop like Oberweis and eat my delight there, they use glass bowls and glasses and real silverware. So then I will treat myself to a milkshake or sundae. Yummy and no plastic.

5. I make my own yogurt instead of buying lots of plastic containers of the stuff. It's easy to do: just buy one of these

6. I buy milk in reusable glass containers. Again, thanks Oberweis!

And there's more, but you get the idea. It mostly comes down to being more intentional about how you go through the day: think ahead, always carry your water bottle and bamboo silverware, just say no to treats that come in plastic that you do not really need to eat, etc.

If millions of humans took action like this, the amount of garbage going to landfills and into the ocean would decrease substantially. 

With reduced demand, less plastic would be produced in factories, less pollution would be produced, etc.

But if you do not do things like this, if you direct your outrage to distant politicians who will never pay you attention and do not address some of that outrage at yourself, well...

Then the coral and whales and sharks and fish and birds and eventually even (dare I say it!) humans will suffer. 

Bottom line: if you want politicians to change their behavior, first change yours

That way, when they still don't give a rat's ass about you, at least you will have helped make the planet a little bit healthier.

Multiple by a million or a billion, and maybe the coral will notice.
Categories: Development

Comparing Columns Containing NULL Values

Randolf Geist - Sun, 2016-07-10 06:32
Prompted by a (not really that) recent discussion on the OTN forum I've decided to publish this note.

Sometimes you have the task of comparing column values and handling the NULL value cases correctly makes this rather cumbersome for columns that are allowed to be NULL.

The "official" SQL way of comparing two column values and to find out whether they are equal or not - under the assumption that having NULL in both columns should be treated as equal (a point that can be argued) would read in SQL something like the following for the "unequal" case:

column1 != column2 or (column1 is null and column2 is not null) or (column1 is not null and column2 is null)
and

column1 = column2 or (column1 is null and column2 is null)
for the "equal" case.

Imagine this to be done for a lot of columns and it becomes obvious that people might be looking for more concise alternatives how to express this.

Sometimes you might have come across code that uses NVL or a similar function with some "special" value that is not supposed to be part of the column data to be compared - like -1 for (positive) integer values for example, but this has the inherent risk of producing wrong results if the "special" value is (or might become in future) not that special after all.

The not really documented SYS_OP_MAP_NONNULL function could come handy if it was official, because it covers the NULL cases automatically.

Note: I still would call it undocumented even if it shows up in some "Materialized View" related part of the official 12c documentation, because it is not part of the official SQL language manual

SYS_OP_MAP_NONNULL(column1) != SYS_OP_MAP_NONNULL(column2)
is all that is needed. More concise as above, but undocumented.

And: There is one obvious drawback to SYS_OP_MAP_NONNULL, since it adds one byte to the input value to ensure the mapped NULL value differs from any other value: It doesn't work properly with maxed out columns, for example this:

SQL> create table t
2 as
3 select rpad('x', 4000) as col1, rpad('x', 4000) as col2 from dual
4 union all
5 select rpad('x', 4000) as col1, rpad('y', 4000) as col2 from dual
6 union all
7 select null as col1, rpad('y', 4000) as col2 from dual
8 union all
9 select rpad('x', 4000) as col1, null from dual
10 union all
11 select null as col1, null as col2 from dual
12 ;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't')

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from t where sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2);
select count(*) from t where sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2)
*
ERROR at line 1:
ORA-01706: user function result value was too large
errors out with "ORA-01706: user function result value was too large".

DECODE
Decode is an exception to the rule how Oracle treats NULLs in SQL - it returns the "equal" value if both operands to compare are NULL, so it allows the following construct to achieve above comparison (see above OTN forum discussion for credits where this expression appeared first, more recently here on Stew Ashton's blog):

For checking for inequality:

decode(column1, column2, 0, 1) = 1
For equality:

decode(column1, column2, 0, 1) = 0
Very concise, and officially documented.

Interestingly, starting with 11.2.0.2 there is an implicit re-write by default enabled that rewrites above expression (and only this variation, not the one testing for inequality):

decode(column1, column2, 0, 1) = 0
into

sys_op_map_nonnull(column1) = sys_op_map_nonnull(column2)
This is controlled via fix control 8551880 ("Rewrite decode predicate to join").

Guess what happens when running the following query in 11.2.0.2 or later against above data set (the fix control is enabled by default in these versions):

SQL> select /*+ opt_param('_fix_control', '8551880:0') */ count(*) from t where decode(col1, col2, 0, 1) = 0;

COUNT(*)
----------
2

SQL>
SQL> select /*+ opt_param('_fix_control', '8551880:1') */ count(*) from t where decode(col1, col2, 0, 1) = 0;
select /*+ opt_param('_fix_control', '8551880:1') */ count(*) from t where decode(col1, col2, 0, 1) = 0
*
ERROR at line 1:
ORA-01706: user function result value was too large
Finally, what about performance? For that purpose I created the following compressed 50M rows table, covering different cases:

create table t1 compress
as
select case mod(rownum, 5) + 1
when 1 then cast('C1' as varchar2(20))
when 2 then cast(null as varchar2(20))
when 3 then cast('C3' as varchar2(20))
when 4 then cast(null as varchar2(20))
when 5 then cast('C5' as varchar2(20))
end as col1,
case mod(rownum, 5) + 1
when 1 then cast('C2' as varchar2(20))
when 2 then cast(null as varchar2(20))
when 3 then cast('C3' as varchar2(20))
when 4 then cast('C4' as varchar2(20))
when 5 then cast(null as varchar2(20))
end as col2
from
(select /*+ cardinality(1e4) */ null from dual connect by level <= 1e4)
, (select /*+ cardinality(5e3) */ null from dual connect by level <= 5e3)
;
and got the following results from 11.2.0.4 (ignoring the different cardinality estimates for the moment, since I'm only interested in the elapsed time here):

SQL> select count(*) from t1;

Elapsed: 00:00:00.94

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18743 (2)| 00:03:45 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 50M| 18743 (2)| 00:03:45 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
68456 consistent gets
0 physical reads
0 redo size
346 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select count(*) from t1 where decode(col1, col2, 0, 1) = 1;

Elapsed: 00:00:02.55

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 19176 (4)| 00:03:51 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 500K| 1953K| 19176 (4)| 00:03:51 |
---------------------------------------------------------------------------

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

2 - filter(DECODE("COL1","COL2",0,1)=1)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
68456 consistent gets
0 physical reads
0 redo size
346 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select count(*) from t1 where SYS_OP_MAP_NONNULL("COL1")!=SYS_OP_MAP_NONNULL("COL2");

Elapsed: 00:00:03.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 19515 (6)| 00:03:55 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 15M| 60M| 19515 (6)| 00:03:55 |
---------------------------------------------------------------------------

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

2 - filter(SYS_OP_MAP_NONNULL("COL1")<>SYS_OP_MAP_NONNULL("COL2"))


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
68456 consistent gets
0 physical reads
0 redo size
346 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select count(*) from t1 where (col1 != col2 or (col1 is null and col2 is not null) or (col1 is not null and col2 is null));

Elapsed: 00:00:03.51

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 18876 (2)| 00:03:47 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 30M| 115M| 18876 (2)| 00:03:47 |
---------------------------------------------------------------------------

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

2 - filter("COL1" IS NULL AND "COL2" IS NOT NULL OR "COL2" IS NULL
AND "COL1" IS NOT NULL OR "COL1"<>"COL2")



Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
68456 consistent gets
0 physical reads
0 redo size
346 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select /*+ opt_param('_fix_control', '8551880:0') */ count(*) from t1 where decode(col1, col2, 0, 1) = 0;

Elapsed: 00:00:02.49

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 19176 (4)| 00:03:51 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 500K| 1953K| 19176 (4)| 00:03:51 |
---------------------------------------------------------------------------

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

2 - filter(DECODE("COL1","COL2",0,1)=0)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
68456 consistent gets
0 physical reads
0 redo size
346 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select /*+ opt_param('_fix_control', '8551880:1') */ count(*) from t1 where decode(col1, col2, 0, 1) = 0;

Elapsed: 00:00:03.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 19515 (6)| 00:03:55 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 6000K| 22M| 19515 (6)| 00:03:55 |
---------------------------------------------------------------------------

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

2 - filter(SYS_OP_MAP_NONNULL("COL1")=SYS_OP_MAP_NONNULL("COL2"))


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
68456 consistent gets
0 physical reads
0 redo size
346 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select count(*) from t1 where col1 = col2 or (col1 is null and col2 is null);

Elapsed: 00:00:02.30

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 18865 (2)| 00:03:47 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 13M| 49M| 18865 (2)| 00:03:47 |
---------------------------------------------------------------------------

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

2 - filter("COL1" IS NULL AND "COL2" IS NULL OR "COL1"="COL2")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
68456 consistent gets
0 physical reads
0 redo size
346 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Surprisingly, in my tests SYS_OP_MAP_NONNULL always performed worst, and the DECODE expression pretty close to the more verbose SQL expression - depending on the data set sometimes even faster.

So using SYS_OP_MAP_NONNULL, and in particular the implicit rewrite might not be such a good idea after all. And now you know how the automatic rewrite could be disabled if desired.

RMAN 12c NF – SQL interface in RMAN


Michael Dinh - Sat, 2016-07-09 18:51

Admittedly, I did not realize the benefit at first until there came a need.

Example:


# Set ORACLE_SID
ORACLE_SID=$1
export ORACLE_SID
### This retrieve the numeric value from PROD1/PROD2, i.e. 1,2 respectively
n=`echo "${ORACLE_SID:(-1)}"`

run {
backup tag 'ARCHIVED_LOG' archivelog all delete input skip inaccessible;
alter database backup controlfile to trace as '/rman_bkup$n/$ORACLE_SID/cf_@.sql' REUSE RESETLOGS;
create pfile='/rman_bkup$n/$ORACLE_SID/init@.ora' from spfile;
create pfile from spfile;
}

 


Oracle Education Dissatisfaction

Michael Dinh - Sat, 2016-07-09 18:28

Just like anything, there is good and bad.

Unfortunately for me, my experience with Oracle education has been predominately bad and former manager experienced the same where he was able to retake a course almost a year later since the original was unrealistic for real world scenarios.

With Oracle education, the most simplistic setup is used, e.g. no ASM, no Grid Infrastructure as it may add a little more complexity to set up?

A little advice, try to find out who will be the instructor before registering for the course. Call and ask.

Next, there is a large and great Oracle community where you may learn more about technology and new features.

Here are 2 features I came to know from twitter feed and blogs which were never covered in the 12c Data Guard Administration.

ROLLING FORWARD A PHYSICAL STANDBY USING RECOVER FROM SERVICE COMMAND IN 12C (Doc ID 1987763.1) (Courtesy Nassyam Basha)
RMAN ACTIVE DUPLICATE USING BACKUPSET IN 12C(NEW FEATURE) (Doc ID 1987193.1) (Courtesy of Deiby Gomez)

 

 


ADF 12.2.1.1 Improved Support for Programmatic View Object

Andrejus Baranovski - Sat, 2016-07-09 13:15
ADF 12.2.1.1 brings improved support for programmatic VO creation. Such VO's are handy, when we want to base VO on alternative data source, such as PL/SQL ref cursor. In ADF 12.2.1.1 developer don't need to worry which framework methods to override, now it is enough to extend from Programmatic View Object Implementation class. This is special framework helper class, designed for programmatic VO support. See example below.

Sample application (ADF12211App.zip) is based on one regular VO, which renders employees table. Programmatic VO renders data for tag cloud component, located below table:


Steps to create programmatic VO are much more simple in ADF 12.2.1.1. Select data source option to be Programmatic in VO creation wizard:


JDEV will create VO with Java implementation classes, extended from ProgrammaticViewObjectImpl class and ProgrammaticViewRowImpl class. These classes will take care for special lifecycle required for programmatic VO behavior. See extends part:


Generated class contains getScrollableData extended method. This is the place to supply data collection for the VO. In my example, I'm creating ArrayList (VO rows) of HashMap's (one HashMap, represents row data of attribute/value pair). Attributes are populated with values and collection is returned back to the framework to manage it. Method is being called automatically by the framework. There are other methods available, to retrieve row by key, etc.:


New framework class allows to work with programmatic VO's easier and leverage framework features. I would expect it would provide better support for programmatic VO data filtering.

VO data is accessed on UI through regular binding expressions:


Initialized by ADF bindings layer:

Pages

Subscribe to Oracle FAQ aggregator