Unix ODBC Oracle

To connect via ODBC, check https://laurentschneider.com/wordpress/tag/odbc This article is related to Unix/Linux. Often you have a fat client written in C, while java uses JDBC instead of ODBC. Okay, it’s pretty easy, if you have an oracle client, you probably already have libsqora.so.xx.1 in your LD_LIBRARY_PATH. In this case you can connect using ODBC. What… Continue reading Unix ODBC Oracle

Published
Categorized as linux, unix Tagged

Privileges on a view

Granting too many privileges on a view could be disastrous. A view is often used as a security element; you grant access to only a subset of columns and rows to one user. Mostly only SELECT. If you want to grant update to only some rows, the security could be enhanced with the WITH CHECK… Continue reading Privileges on a view

super-long-lines in CLOB

Sometimes you use sqlplus and your line is longer than your linesize SQL> select n||’;’||x from t2; 1;one 2;twoPxMQztzLaqjWjGKOXIVIVrrHC fJkTLbRgCPiENfWrrCjUMRSkmCfLUY RdVASFacGtyEnUplOAXspDJZmSLPfg ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy lZLQROFxIxOzqeQeFMGXcGkuJwnGGg zvNSuCFMVxyQgboLAUpDcYnBsuVyXX vFtWPICwqmcEdmzRRkOzzPethrqjRR aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh ZcxJBvSCVXwONmS prVzTykEfSsePyYwyLVoyYrVLynUzs MLFWQxwUKNsVcYzUOAhslNldnBpITS rxPlpJbLSjJqgxNxsGVsrYhkWAMufk QnRayieEkSDYrNqyLejJuggADNxcgV tszjJIYKCxPweNGhXsOFKGbMkTBPCf DXwjBNgQYswbaNWBOEtSTHjIhdLAyM nbhyhRKKdfaTTpTgHqQelVWmnkBHjA ZTrGqdtlYAgoXNHnoryxHxVVyaMiGR SjdVlRwMas 3;three SQL> There are two extra line breaks, one after ONmS and one after wMas An easy solution is… Continue reading super-long-lines in CLOB

Published
Categorized as sqlplus Tagged

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

Published
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

Published
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

Add-OdbcDsn

I wrote a few odbc articles using ODBCCONF in my blog, so I edit them because ODBCCONF will be removed; read https://docs.microsoft.com/en-us/sql/odbc/odbcconf-exe 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 functionshttps://t.co/UfddLQ2tn5 pic.twitter.com/BxnXeIKoCx — 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

Published
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

Published
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

Published
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

Published
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. [srv01.example.com, 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=(service_name=DB01.example.com)))” 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

Published
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. O12.java 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

Published
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 https://twitter.com/connor_mc_d/status/811563990346186752 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