Author: Laurent Schneider

Identified by values reloaded

To get the correct values string, here is another approach, when you have no access to sys.user$ select username, extractvalue( xmltype( dbms_metadata.get_xml(‘USER’,username)), ‘/ROWSET/ROW/USER_T/SPARE4_12/text()’) from dba_users; USER SPARE4_12 ———– ————————- SCOTT S:12345678…;T:FEDCBA… OPS$ORACLE SYSTEM S: XS$NULL S:00000000… I would then ignore users with empty strings or S:00000000% or S: strings For Scott, then use alter user […]

Pretty JSON in 21c

Oracle 21c now enables JSON as a datatype 12.2, 18c, 19c: SQL> SELECT json_object(*) from scott.emp where ename=’SCOTT’; JSON_OBJECT(*) ————– {“EMPNO”:7788,”ENAME”:”SCOTT”,”JOB”:”ANALYST”,”MGR”:7566,”HIREDATE”:”1987-04-19T00:00:00″,”SAL”:3000,”COMM”:null,”DEPTNO”:20} 21c: SQL> SELECT json_object(* returning json) from scott.emp where ename=’SCOTT’; JSON_OBJECT(*RETURNINGJSON) ————————— {“EMPNO”:7788,”ENAME”:”SCOTT”,”JOB”:”ANALYST”,”MGR”:7566,”HIREDATE”:”1987-04-19T00:00:00″,”SAL”:3000,”COMM”:null,”DEPTNO”:20} Ok, it looks similar, but it’s a no longer a string (varchar2 or clob), it is a json object. SQL> create […]

Inline editting

I come from a no-tempfile world, where you getc and putc When moving from legacy Unixes to Linux, inline editting became legend. Number of utilities like sed can now edit the file without tempfile. Your AIX sysadmin probably used to do sed “s/xxx/yyy/” /etc/importantfile > /tmp/importantfile mv /tmp/importantfile /etc which works… BUT it has a […]

Pluggable and externally identified users without using remote authentication

Yesterday I was shocked to find a note on metalink that recommends a huge security hole using a deprecated Parameter 2042219.1 : create user c##oracle identified externally + set remote_os_authent=true This is extremly sad. It is such a non-sense to recommend such a flaw. It makes me really angry 👿 Okay, for my readers I […]

cannot open database in NOARCHIVELOG

SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount quiet ORACLE instance started. Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-00258: manual archiving in NOARCHIVELOG mode must identify log First time I see this. Let’s try to […]

ipcalc in powershell

Last day I wrote how to do it in AIX or Linux ip calc with ifconfig It isn’t that different in PowerShell, the fun is to the calculation yourself. For translating 0.0.0.0 in 0, we can use [IPADDRESS]. Let’s try… $ip = [IPADDRESS]( (Get-NetIPAddress -AddressFamily “IPv4” -InterfaceAlias “Ethernet*”). ipaddress) $prefix = ( Get-NetIPAddress -AddressFamily “IPv4” […]

sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64

This error usually while you do something wrong. Wait, what’s an error when you do everything right? Okay, here it is: You install the instantclient 32 rpm oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64 On that server, you switch home using oraenv $ . oraenv ORACLE_SID = [oracle] ? DB01 The Oracle base has been set to /u01/app/oracle You start sqlplus […]

ODBC and EZCONNECT or my way out of active directory

The traditional way of connecting Excel (or Access) to Oracle (and other databases) is to use ODBC and TNSNAMES. You install an Oracle client, you create a connection and specify your tnsnames connection alias as server, here below DB01 tnsnames.ora DB01 = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(host=srv01)(Port=1521) ) (CONNECT_DATA= (SERVICE_NAME=DB01) ) ) But the file is often […]

Connect to ActiveDirectory with ldapsearch on Unix

In ancient times, ldapsearch could query ActiveDirectory without issues. In this examples, I used openldap client 2.4. Other tools may have other parameters. $ ldapsearch -H ldap://example.com:389 -b dc=example,dc=com cn=”Laurent C. Schneider” mail mail: laurent.c.schneider@example.com In Active Directory (AD) it is no longer the default since Windows Server 2003, unless you change dSHeuristics to 0000002 […]

Goldengate 19c on AIX for Oracle database

Now Oracle Goldengate 19.1.0.2 can manage 19c AIX Oracle DB instance. Linux has been out for a long time. Sparc is also available. For Windows, HPUX, patience … download.oracle.com $ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.2 OGGCORE_19.1.0.0.0_PLATFORMS_190823.0013_FBO AIX 7, ppc, 64bit (optimized), Oracle 19c on Aug 25 2019 22:10:20 Operating system character […]

Dump TNSNAMES.ORA from ActiveDirectory

Having all connections string in ActiveDirectory is nice, but maybe you need sometimes to push it to an external system (e.g. DMZ or Linux). echo “# AD” > tnsnames.ora $o = New-Object DirectoryServices.DirectorySearcher $o.Filter = ‘objectclass=orclNetService’ foreach ($p in $o.FindAll().Properties) { [String]($p.name+”=”+$p.orclnetdescstring) >> tnsnames.ora } goodies

Standard date format

Let’s start with Powershell Get-Date -format “o” 2019-03-08T17:41:02.7346332+01:00 The “O” or “o” standard format specifier represents a custom date and time format string using a pattern that preserves time zone information and emits a result string that complies with ISO 8601 docs.microsoft.com Now Linux date “+%Y%m%dT%H:%M:%S.%3N%z” In SQL to_char(current_timestamp, ‘YYYYMMDD”T”HH24:MI:SS.FF3TZH:TZM’) for my XML fans extractvalue(xmlelement(t, […]

19c

19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 12.2.0.2/18c https://mikedietrichde.com/2019/02/13/oracle-database-19-2-for-exadata-is-now-available-for-download/ was the first to mention it. By looking up in the doc I found . Distinct listagg . Desupport sqlplus product profile . listener.log log rotation Go to the doc to find more https://docs.oracle.com/en/database/oracle/oracle-database/19/whats-new.html