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

check invalid database link for the DBA

followup of check invalid database link If you need to check db link in another schema, you need to create code that run with that schema. base on the example from yesterday, here is an extended version for the dba CREATE FUNCTION dba_status   (owner VARCHAR2, db_link VARCHAR2)   RETURN VARCHAR2 IS   PRAGMA AUTONOMOUS_TRANSACTION;   status   VARCHAR2 (4000); […]

check invalid database link

If one database link is invalid, you cannot select through it SQL> select * from dual@z; select * from dual@z                    * ERROR at line 1: ORA-02019: connection description for remote database not found However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function. create […]

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: 15   Status: […]

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/        285140       285178 […]

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

last partition

if you really need to quickly find the latest partition per table, I have written this little gem WITH FUNCTION d (b BLOB, len number) RETURN DATE IS   d DATE; BEGIN   IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw('07') and len=83   THEN     DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d);   ELSE     d := NULL;   END IF;   RETURN d; […]

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

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

ssl version

I wrote about ssl version in jdbc thin yesterday The default version also no longer works for the thick client with 12c client and 11g Server. With 11gR2 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 OK (100 msec) with 12cR1 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: […]

check if using tcps part II

in your current session, as written there, check sys_context('USERENV', 'NETWORK_PROTOCOL') in another session, you could grab some hints out of the network service banner. Do the maths, when it is not-not using ssl, it probably is… select sid,program,   case when program not like 'ora___@% (P%)' then   (select max(case when NETWORK_SERVICE_BANNER like '%TCP/IP%'       then 'TCP' when […]