Select from cdb_* views

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

Audit pluggable database

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

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

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: .

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

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

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

Irrecoverable part III : a fix

After part I:the problem, Part II: two reports here is part III. We have backed up archivelog during a full and deleted them before the full completed. RMAN> list backup of archivelog all; List of Backup Sets =================== BS Size Type Elapsed Completion — —— —- ——– ———- 15 4.00K DISK 00:00:00 13:31:08 BP Key: […]

Irrecoverable full backup part II : reporting

After my post Can you restore from a full online backup ?, I needed to come up with a report. Assuming that each backup goes in a different directory, I just wrote two reports. Report gaps in v$backup_redolog (or rc_backup_redolog if you use the catalog) DIR FIRST_CHANGE# NEXT_CHANGE# ——- ————- ———— /bck01/ 284891 285140 /bck01/ […]

Can you restore from a full online backup ?

The question is not HOW TO DO IT but WHETHER YOU CAN DO IT ! A typical backup script would contains something like BACKUP DATABASE PLUS ARCHIVELOG: backup database format ‘/u99/backup/DB01/20150518/full_0_%d_s%s_p%p’ plus archivelog format ‘/u99/backup/DB01/20150518/arc_%d_s%s_p%p’; Starting backup at 2015-05-18_18:27:55 current log archived input archived log thread=1 sequence=469 … piece handle= /u99/backup/DB01/20150518/arc_DB01_s86_p1 Finished backup at 2015-05-18_18:27:58 […]

bypass ora-20

When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba. Test case (21 is a not something to do in real life): SQL> alter system set processes=21 scope=spfile; System altered. SQL> startup force quiet ORACLE instance started. […]

switch user in Oracle

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation à la sudo. By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user. SQL> ALTER USER app_user PROXY ONLY CONNECT; SQL> CONNECT app_user/xyz ERROR:ORA-28058: login […]

Unusable index

After table maintenance, like move or split partition, underlying indexes are marked as unusable. This boils down to segment reorganisation, not dictionary change. For instance : CREATE TABLE t(x NUMBER) PARTITION BY RANGE(x) (PARTITION p1 VALUES LESS THAN (MAXVALUE)); CREATE INDEX i ON t (x); INSERT INTO t VALUES (1); ALTER TABLE T SPLIT PARTITION […]

Did you forget to run root.sh?

Not easy to detect, and depending on the product (agent/database), it may have only limited side effects. Like external jobs not running, operating systems statistics not collected. But it is not always easy to diagnose. For instance if you patch from OMS 12cR2 to 12cR3, and you run the root.sh only in 12cR2, they are […]

Do you have a partition in 2015

You need to check the high_value from dba_tab_partitions. Or you you could metadata. With metadata, it is not a long, it either clob or clob-xml. SELECT t.table_name, MAX ( TO_DATE ( REGEXP_SUBSTR ( EXTRACT ( (VALUE (x)), ‘HIBOUNDVAL’).getStringVal (), ‘ \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}’), ‘YYYY-MM-DD HH24:MI;:SS’)) high_value FROM user_tables t, TABLE ( XMLSEQUENCE ( EXTRACT ( xmltype […]

poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation. You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that […]

import into UTF8 database

A common error when you import single-byte characters (e.g. iso8859p1 or mswin1252) into multi-bytes databases (e.g. utf8) is ORA-12899: value too large for column. The root cause is the default semantics in a database being BYTE SQL> select VALUE, ISDEFAULT from v$parameter where NAME=’nls_length_semantics’ VALUE ISDEFAULT ——- ——— BYTE TRUE It means, one char equals […]