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
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
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
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
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
FBI = function based index
Let me today tell you, I now hate FBI for real. Let’s start with an easy working example SQL> CREATE TABLE t(x NUMBER PRIMARY KEY) Table created. SQL> insert into t(x) values (1) 1 row created. SQL> insert into t(x) values (2) 1 row created. SQL> insert into t(x) values (3) 1 row created. SQL>… Continue reading FBI = function based index
Administrative privileges like SYSDBA
The most well-known administrative privilege is sysdba. Back in Oracle 7.2, oracle recommended to no longer use the INTERNAL but to connect as sysdba. A second one, seldom used, is SYSOPER. When you use sqlplus / as sysdba, or connect / as sysdba in sqlplus / srvmgrl, the you log as SYS. That’s the most… Continue reading Administrative privileges like SYSDBA
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
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
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
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
lock sys
In the old days, locking sys had not much effect. SQL> alter user sys identified by *** account lock; User altered. SQL> select account_status from dba_users where username=’SYS’; ACCOUNT_STATUS ——————————– LOCKED SQL> conn / as sysdba Connected. SQL> conn sys/** as sysdba Connected. SQL> conn sys/***@db01 as sysdba Connected. Well, in the very-old days, Oracle7,… Continue reading lock sys
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
Legacy users get ORA-01017 in 12.2
10g value string disappeared
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
ANNOUNCEMENT: 12cR2 is available on AIX and HPUX and #sqldev 4.2
download it from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html sqldeveloper 4.2 is there : http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
Monitor audit_file_dest !
Until 11.2, audit_file_dest used to remain small with default settings and reasonably sized and active database. Suddenly, in 12c, you will sooned or later get ORA-09925: Unable to create audit trail file. At that point, no more connection is possible to the database, it is a complete loss of service. Why suddenly in 12c ?… Continue reading Monitor audit_file_dest !
connect / as sysoper generates ORA-1017
Today I had the issue that I could not login as sysoper on one database, despite being in the dba and oper groups. The problem is that the osoper group was -probably- not selected during installation e.g. in 12c /AIX it may be called differently on your system $ sqlplus -L -s / as sysoper… Continue reading connect / as sysoper generates ORA-1017
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
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
execute sql script from pl/sql
When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of… Continue reading execute sql script from pl/sql
ANNOUNCEMENT: 12cR2 documentation is available
The doc docs.oracle.com/database/122 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