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 […]

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 […]

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 […]

Categories: .

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 […]

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   : […]

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 […]

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 […]

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 (primary key / not null). […]

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 […]

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); […]

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 […]

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] [srv01.example.com:3872, […]

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 […]

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 […]

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> […]

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 […]

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 […]

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 […]

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 […]