in doubt transaction

Distributed transactions allows you to have multiple DML’s over multiple databases within a single transaction For instance, one local and one remote insert into t values(1); insert into t@db02 values(2); commit; If you lose connection to db02 and wants to commit, your database server may/does not know about the state of the remote transaction. The… Continue reading in doubt transaction

Categorized as dba

How to migrate non-cdb to pdb

In case you are desesperately looking for an upgrade from non-cdb to pdb with two clicks, here is the answer from Mike : you can’t Nope – not via "upgrade". You can use Data Pump, Transportable Tablespaces or Full Transportable Export/Import as a direct option but you can't upgrade and plugin in one pass unfortunately… Continue reading How to migrate non-cdb to pdb

powershell code signing

Unix users don’t use this. Maybe some java developers do. But no Unix sysadmins. Never. On Windows, things are getting more secure every release, especially if you pay attention to those details. In Unix, if I have a script called “getdate” which shows me the date, I can copy it to another machine. $ cat… Continue reading powershell code signing

Single Tenant duplicates

I recently reported an issue regarding single tenant. In old-time non-cdb, the SID used to be unique on a server. If you connect to srv01:port:sid, then you know where you connect. Unfortunately, this is no longer true. If for instance you have two database sid’s S01 and S02 with a pluggable P01, and both run… Continue reading Single Tenant duplicates

Categorized as 12c Tagged

disallow pseudo terminal in ssh

Some Oracle documentation wants you to setup ssh with no password and no passphrase. Configuring ssh This is not really something your security admin will like. First, using DSA, which is deprecated and disabled by default in OpenSSH 7.0, is a pretty dump instruction OpenSSH 7.0 and greater similarly disable the ssh-dss (DSA) public key… Continue reading disallow pseudo terminal in ssh

dynamic linesize in 18.1

Whenever you select and describe in sqlplus it looks ugly default: pagesize 14 linesize 80 change the default: it is often too large or too narrow Let’s try WINDOW in sqlplus 18.1, which is available for download on Solaris / Linux / Windows SQL> set lin window SQL> sho lin linesize 95 WINDOW SQL> sho… Continue reading dynamic linesize in 18.1


I wrote a few odbc articles using ODBCCONF in my blog, so I edit them because ODBCCONF will be removed; read Using Powershell Add-OdbcDsn is much easier PS> remove-OdbcDsn -name DB01 -dsntype User PS> Add-OdbcDsn -name DB01 -DriverName “Oracle in client12201” -DsnType “User” -SetPropertyValue @(“Server=DB01”) PS> Get-OdbcDsn Name : DB01 DsnType : User Platform… Continue reading Add-OdbcDsn

How to get dbms_output to print line before the end of the procedure?

begin dbms_output.put_line(‘Kilroy1’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy2’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy3’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy4’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy5’); dbms_lock.sleep(1); end; / You wait five seconds, then get the output. This is the way it works with dbms_output. Now I try a new trick The output does not wait the end. There was a trick I wrote in 2007 using utl_file, but it was… Continue reading How to get dbms_output to print line before the end of the procedure?

Dynamic number of columns revisited

Itching to start playing with 18c? Now you can on @oraclelivesql ! Here's a script to get you started: a dynamic CSV-to-columns converter using polymorphic table functions — Chris Saxon (@ChrisRSaxon) February 17, 2018 The ingenious solution of Anton Scheffer using Data Cartridge is now beaten in 18c using polymorphic table function Anthologic post… Continue reading Dynamic number of columns revisited

Categorized as 18c Tagged

better than CTAS

SQL> create table t1(x number primary key); Table created. SQL> desc t1 Name Null? Type ———————– ——– —————- X NOT NULL NUMBER SQL> create table t2 as select * from t1; Table created. SQL> desc t2 Name Null? Type ———————– ——– —————- X NUMBER The table T2 has the column X, but not the constraint… Continue reading better than CTAS

check invalid directories

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS. CREATE FUNCTION status (DIRECTORY_NAME VARCHAR2) RETURN VARCHAR2 IS BEGIN IF (DBMS_LOB.FILEEXISTS( BFILENAME (DIRECTORY_NAME, ‘.’)) = 1) THEN RETURN ‘VALID’; ELSE RETURN ‘INVALID’; END IF; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END; / SELECT directory_name NAME, directory_path PATH, status (directory_name) STATUS… Continue reading check invalid directories

Categorized as dba Tagged

check invalid database link for the DBA

followup of check invalid database link If you need to check db link in another schema, you need to create code that run with that schema. base on the example from yesterday, here is an extended version for the dba CREATE FUNCTION dba_status (owner VARCHAR2, db_link VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; status VARCHAR2 (4000);… Continue reading check invalid database link for the DBA

Categorized as dba Tagged

check invalid database link

If one database link is invalid, you cannot select through it SQL> select * from dual@z; select * from dual@z * ERROR at line 1: ORA-02019: connection description for remote database not found However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function. create function… Continue reading check invalid database link

Categorized as dba Tagged

list targets

$p=new XML::Parser;$p->setHandlers(Start=>&b);$p->parsefile("-");sub b{($i,$e,%a)=@_;if($e eq"Target"){print$a{"NAME"}.":".$a{"TYPE"}}} — laurentsch (@laurentsch) September 19, 2017 As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target $ emctl config agent listtargets Oracle Enterprise Manager Cloud Control 13c Release 2 Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved. [, host]… Continue reading list targets

sid and pluggable

I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant. You create a container CDB01 and a pluggable DB01, you can connect with sqlplus “scott/tiger@ (description=(address= (host=srv01)(port=1521)(protocol=tcp)) (connect_data=(” But one of your application has hardcoded SID=DB01 sqlplus “scott/tiger@ (description=(address= (host=srv01)(port=1521)(protocol=tcp)) (connect_data=(sid=DB01)))” ORA-12514: TNS:listener does not currently know… Continue reading sid and pluggable

default listener port

Long time ago, Maxime Yuen registered 1521 for nCube License Manager. By googling I found : Ellison cleans house at nCube, and since them 1521 has been used as a default port for Oracle. Still, you’ll see nCube in IANA.ORG service names port numbers and in /etc/services the nCube name. I don’t know which one… Continue reading default listener port

Single-Tenant over bequeath connections

If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break. I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally sqlplus / as sysdba SQL>… Continue reading Single-Tenant over bequeath connections

remote transaction timeout

If you access one table via database link and the row is locked, you may get a timeout SQL> update emp@l set sal=sal+1 where ename=’SCOTT’; 1 row updated. SQL> update emp@l set sal=sal+2 where ename=’SCOTT’; update emp@l set sal=sal+2 * ERROR at line 1: ORA-02049: timeout: distributed transaction waiting for lock ORA-02063: preceding line from… Continue reading remote transaction timeout

Categorized as sql Tagged

old jdbc driver generates ORA-28040

I read on note 401934.1 that Oracle 10gR2 jdbc clients are still supported with Oracle 12c. I have an application using an oracle10gr2 jdbc, and connection to 12c generates ORA-28040. Connection to 11gR2 works like a charm. import java.util.Properties; import java.sql.*; public class O12 { public static void main(String argv[]) throws SQLException { Properties… Continue reading old jdbc driver generates ORA-28040

Categorized as 12c, java Tagged

Restricted sqlplus and sql_script

Yesterday I wrote about execute sql script from plsql. Connor then posted a 12c new feature that I overviewed If you are still considering the 11g approach with sqlplus, you should read about restricted sqlplus If you run sqlplus -L -s -R 3 scott/tiger@db01 lot’s of OS-command are disabled SQL> get /etc/passwd SP2-0738: Restricted… Continue reading Restricted sqlplus and sql_script

ANNOUNCEMENT: 12cR2 documentation is available

The doc is available. Check new features and stay tuned. Okay, just 12 for today : HIGH compressed index, case insensitive database with bound collation, partitioned external tables, AL32UTF8 default for new database, listagg overflow, VALIDATE_CONVERSION, approx_percentile, json_exists, flashback pluggable database, the SHARING clause, RAC streched clusters site definition and INACTIVE_ACCOUNT_TIME in profile. Some… Continue reading ANNOUNCEMENT: 12cR2 documentation is available

To shrink or to move

If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation. If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space. In this case, you could move your… Continue reading To shrink or to move

Categorized as 12cR2, dba Tagged

OTN Appreciation Day : Partition your table online !

#ThanksOTN @oraclebase No, I am not talking about DBMS_REDEFINITION, where you get a kind of online feeling. No, I don’t want to rename my table, rename my foreign keys, my primary key, my not-null-constraints, recreate my referential integrity, recompile my triggers. I just want to partition a non-partitioned table. ALTER TABLE EMP MODIFY PARTITION BY… Continue reading OTN Appreciation Day : Partition your table online !