Author: Laurent Schneider

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

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

Something I always wanted arrived this week, a checksum of a column ! SQL> create table t1(x number); Table created. SQL> create table t2(x number); Table created. SQL> insert into t1(x) values (1); 1 row created. SQL> insert into t2(x) values (1); 1 row created. SQL> select (select checksum(x) from t1)t1, (select checksum(x) from t2)t2 […]

To use an object table or an object column over a database link, a type with the same OID as remote type must exist locally. SQL> conn user1/***@remotedb01 Connected. SQL> create type tt as object (x number) 2 / Type created. SQL> create table t (x tt); Table created. SQL> insert into t values (tt(1)); […]

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

When you move away from commercial UNIX to Linux, some goodies are just fun, even if they are simple and old. Let’s look at grep. By default, the matched pattern is red. But the color could be changed. Some magic regexp could be used to get more than one color $ tnsping DB01 |    […]

When downloading software to my database server, I used to first download locally and later copy to my Unix box… but wouldn’t be convenient to download it directly on the database server? Quite often, you get no X and no Browser and no Internet access on your datacenter. Therefore, we’ll use wget to the purpose. […]

Just reading the Doc 742060.1, the release 20c, which was due 2020, disappeared from the roadmap. Don’t wait anymore for 20c, there won’t be one. There was a preview release in the cloud, but Oracle failed to release one new release every year. While we are all used to wait 2-6 years for a new […]

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

Sometimes you like to copy files from A to B and you have sudo rights on A and B and you do a lot of “cp” to /tmp and chmod and chown’s. This is annoying… Firstly, I dislike tempfiles. they use space they generate bugs when run in parallel they often are prone to code […]

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

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

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

Most *nix are different. I’ll start with a plain Linux output ifconfig eth0 eth0: flags=4163 mtu 1500 inet 93.184.216.34 netmask 255.255.255.0 broadcast 93.184.216.255 to get the network ip, I just bitwise-and the inet and the netmask. To do it with the shell, I convert the ip to an integer and use the & (AND) operator […]

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

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: [email protected] In Active Directory (AD) it is no longer the default since Windows Server 2003, unless you change dSHeuristics to 0000002 […]

I could not miss this ! After offering free apex for non-productive usage (apex.oracle.com), free sql environment for playing (livesql.oracle.com), Oracle now offers free for ever infrastructure and database. With a few clicks, a credit card (that won’t be charged) and a few minutes of patience, you will be able to have your own Linux […]

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

While most programming languages are accepting arguments as an array of strings, shell doesn’t arglist.c #include int main(int argc, char **argv) { int i; for (i=1; argc>i; i++) printf(“$%d=%s\n”,i,argv[i]); } $ make arglist cc arglist.c -o arglist $ ./arglist one two three four $1=one $2=two $3=three $4=four To do the same in shell, it requires […]

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

A common frustration with standby is that your database is doing nothing else than applying logs. One may want to run some reports on it. Usually, the database is MOUNTED and not OPEN. This means, apart from selecting from DUAL and performance views like v$$managed_standby or v$session, there is little you can do. Possibly, you […]

many ignore the difference meaning of .. (dot dot) as an argument when used with ls or cd this leads to buggy coding and wrong parsing of arguments in scripts let’s start with an example $ mkdir $HOME/test $HOME/test/physical $HOME/test/foo $ cd $HOME/test/foo $ ln -s ../physical logical $ cd logical $ ls -l .. […]

With Oracle 18c and even better in Oracle 19c, you can manage your Oracle database users in Active Directory. This was supposed to be a very nice new feature as many of us struggle with many thousand users spread over many versions, environments, platforms and even cloud or exadata. Is this going to help you? […]

There is no privileges strong enough for you to view all objects in all databases Let’s try as sys: SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ———- ———- 1 22749 3 22721 as non-sys SQL> create user c##u identified by ***; User created. SQL> grant create session, select any dictionary to […]

One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware. If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_* OLD: SQL> select count(*) from dba_users; COUNT(*) ———- 121 NEW: non-cdb SQL> select con_id, count(*) from cdb_users group by con_id; CON_ID […]

Let’s start with some basics. The basics works as well on Unix, Linux and Windows. Later techniques only work on linux/unix $ ls -l hosts -rw-r–r–. 1 root root 211 Oct 5 2015 hosts $ ls -l xxx ls: cannot access xxx: No such file or directory $ read x foo $ Outpout and error […]

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

a common task of the DBA is to add databases in EM. If you do it 1000 times, you’ll get bored. I guess… sure you could go to EM Cloud Management Pack, but maybe you use SQLPLUS + Create database + catalog/catproc. Okay, there are some ways/hacks to do it with emcli/emctl, but let’s face […]

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba. In single-tenant (or multi-tenant), things get complicated. Once again, the doc must be read at least twice 😉 If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in […]

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