With the introduction of CDB, many views have added column CON_ID.
DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers));
select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers))
ERROR at line 1:
ORA-00913: too many values
DB1:(SYS@DB1):PRIMARY> desc dba_blockers;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select HOLDING_SESSION from dba_blockers));
INST SID SERIAL# PROGRAM (more...)
At times, I find it much simpler to shutdown my test database and create cold backup which will allow me to revert from any errors.
$ echo $ORACLE_SID
$ tar -cvzf /media/sf_OracleSoftware/$ORACLE_SID_$USER_`hostname -s`_backup.tar.gz .
$ ll /media/sf_OracleSoftware/DB01_oracle_arrow_backup.tar.gz
-rwxrwx---. 1 root vboxsf 382676286 Aug 28 13:09 (more...)
A huge thank you to Pete Finnigan for his blog post.
Hacking Oracle 12c COMMON Users
Not only is the implementation of ORACLE_MAINTAINED not ideal, it’s a possible security risk.
My tip to you is to always exit the session before performing any object creation.
From my pain as you can see, looks like “_ORACLE_SCRIPT was not reset when new users where created.
Also ORACLE_MAINTAINED Objects Don’t Export
Learned quite a few new thing this weekend and (more...)
What a disaster this is with 12c full database export and ORACLE_MAINTAINED objects.
Somehow, oracle thinks MDINH is an ORACLE_MAINTAINED user and user MDINH does not get exported.
Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655 (Doc ID 2114233.1)
There are certain system generated schemas that are not exportable using exp or expdp because they contain Oracle-managed data and metadata. SYS, MDSYS, and ORDSYS are some (more...)
I recently discovered about column ORACLE_MAINTAINED added to DBA views and got excited over nothing.
ORACLE_MAINTAINED from DBA_ROLES
Denotes whether the role was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql).
A role for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.
It looks like ORACLE_MAINTAINED is only valid for CDB and seems as if Oracle is really wanting all (more...)
I am not going to post how to convert to extended data type since there are many blogs on that already.
Just a reminder, there’s no going back; hence have backup and possibly minimize changes during testing to be able to restore (which is ideal and may not be feasible).
Before reverting to MAX_STRING_SIZE=STANDARD, columns affected by extended data type need to be identified.
From Oracle documentation, MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, (more...)
Recently, there has been discussion about using rlwrap for Goldengate on Twitter feed.
Truthfully, I did not know there was RPM for this and I did not even know it was already installed.
It just worked, when I had set it up.
A very nice cheat sheet for using yum.
Yum Command Cheat Sheet for Red Hat Enterprise Linux
I started to investigate how was rlwrap installed on the system.
$ cat /etc/issue
I was working on configuring multiple local listeners and having difficulties setting local_listener using full address list.
How would you set up local_listeners?
There are 4 listeners, 1-4.
$ lsnrctl status listener1
LSNRCTL for Linux: Version 18.104.22.168.0 - Production on 17-AUG-2016 21:34:31
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
Version TNSLSNR for Linux: Version 22.214.171.124.0 (more...)
Due to security hardening, telnet is not available.
$ rpm -q --whatprovides nc
$ yum list nc
Loaded plugins: refresh-packagekit, security
epel/metalink | 11 kB 00:00
epel | 4.3 kB 00:00
epel/primary_db | 5.9 MB 00:03
public_ol6_UEKR3_latest | 1.2 kB 00:00
public_ol6_latest | 1.4 kB 00:00
nc.x86_64 1.84-24.el6 @public_ol6_latest
$ nc -v -z -w 3 stackoverflow.com 80; echo $?
Connection to stackoverflow.com 80 port [tcp/http] succeeded!
Session 1 starts UPDATE and nothing else.
02:53:39 ARROW:(MDINH@leo):PRIMARY> update t set object_id=100;
1 row updated.
02:53:50 ARROW:(SYSTEM@leo):PRIMARY> update mdinh.t set object_id=2;
02:53:58 ARROW:(DEMO@leo):PRIMARY> update mdinh.t set object_id=200;
Monitor blocking locks
$ sysdba @b.sql
SQL*Plus: Release 126.96.36.199.0 Production on Sat Aug 6 02:55:03 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Oracle Database (more...)