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

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

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

What is the instance name?

If your Oracle SID doesn’t match your instance name in init.ora, this is quite confusing. Check my previous post, what is sid in oracle In the instance_name column of the view v$instance, as well as in USERENV context, it matches the ORACLE_SID of the underlying operating system. SQL> var ORACLE_SID varchar2(9) SQL> set autoprint on […]

What is SID in Oracle ?

In the doc you’ll find, it’s the Oracle system identifier. Okay, let’s imagine the following Environment: ORACLE_SID=ORA001 init.ora: DB_NAME=DB001 DB_UNIQUE_NAME=UNI001 INSTANCE_NAME=INS001 SERVICE_NAMES=SVC001,SVC002 DB_DOMAIN=EXAMPLE.COM GLOBAL_NAMES=false database: SQL> select * from GLOBAL_NAME; listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_NAME=GLO001.EXAMPLE.COM) (SID_NAME=ORA001) ) ) What is my SID? Actually there is more than one correct answer. In […]

duplicate to a future date

If you work with large databases, you often wait way to long for the clones. Typically you want to duplicate a 10TB database to production timestamp 9am, and you start at 9am and then you wait for hours. Is it possible to start the clone, let’s say, at midnight, and set until time 9am? No! […]

column width change in 12c

In 11g I used to have 30 characters width in my dictionary SQL> select table_name, column_name from user_tab_columns; TABLE_NAME COLUMN_NAME —————————— —————————— BONUS COMM BONUS SAL BONUS JOB BONUS ENAME DEPT LOC DEPT DNAME DEPT DEPTNO EMP DEPTNO EMP COMM EMP SAL EMP HIREDATE TABLE_NAME COLUMN_NAME —————————— —————————— EMP MGR EMP JOB EMP ENAME EMP […]

powershell odbc sybase

Oracle goes there ==> …/oracle-odbc-hello-world-with-powershell.html To test Sybase ODBC driver with Powershell, it’s not much different configure the DsN with odbcconf or %SYSTEMROOT%\SysWOW64\odbcconf for the 32bits drivers under Windows 64. cmd /c “%SYSTEMROOT%\SysWOW64\odbcconf.exe /a {configdsn “”Adaptive Server Enterprise”” “”DSN=helloworld|database=mydb|port=25000|server=srv01″”}” The name of the driver is important. It is probably also called “Adaptive Server Enterprise” on […]

Generate 11g password hash

An easy way to generate a value string from the ssl is to use openssl Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important. The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A- $ echo “SafePassw0rDABCDEFGHIJ\c” | openssl dgst -sha1 (stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb With this hash, I can construct my value SQL> create user testuser identified […]

delete all data

How do you delete all data? The simplistic approach would be to truncate all tables SQL> select table_name from user_tables; TABLE_NAME ———- T1 SQL> truncate table t1; Table truncated. You cannot truncate if you have referential integrity constraints. SQL> truncate table t2; ORA-02266: unique/primary keys in table referenced by enabled foreign keys Ok, let’s disable […]

tracefile tim to readable date format

In trace file, the time is recorded, this could be used to measure time between two timestamps But how do you convert 31796862227375 to a human format? This is how I proceeded : SQL> oradebug SETMYPID Statement processed. SQL> oradebug tracefile_name /u01/log/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_32440740.trc SQL> alter session set nls_date_format=’YYYY-MM-DD_HH24:MI:SS’ sql_trace=true; Session altered. SQL> select ‘&_DATE’,’Start’ from dual; […]

job_name cannot be null

exec dbms_scheduler.create_job(job_name=>null,job_type=>’PLSQL_BLOCK’,job_action=>’BEGIN NULL; END;’) ORA-27451: JOB_NAME cannot be NULL ORA-06512: at “SYS.DBMS_ISCHED”, line 146 ORA-06512: at “SYS.DBMS_SCHEDULER”, line 288 ORA-06512: at line 1 This sounds like a proper error message. A bit less obvious is the drop_job message SQL> exec dbms_scheduler.drop_job(job_name=>null) ORA-20001: comma-separated list invalid near ORA-06512: at “SYS.DBMS_UTILITY”, line 236 ORA-06512: at “SYS.DBMS_UTILITY”, line […]