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… Continue reading import into UTF8 database

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 OK (100 msec) with 12cR1 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows:… Continue reading ssl version

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… Continue reading check if using tcps part II

disable commit in procedure

There is an obscure syntax that prevents a procedure from issuing a commit alter session DISABLE COMMIT IN PROCEDURE; According to the doc, it prevents procedure from committing your data Test case SQL> alter session DISABLE COMMIT IN PROCEDURE Session altered. SQL> create table t(x number) Table created. SQL> create or replace procedure p is… Continue reading disable commit in procedure

Categorized as dba, sql Tagged

The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost Landscape for my setup One PC with Windows (PC01.EXAMPLE.COM)… Continue reading The long long route to Kerberos

Changing the log apply delay (DelayMins)

Whenever you change the DelayMins setting in Dataguard, you must remember it affects only logs that have not been shipped yet. DGMGRL> show database sDB01 delaymins DelayMins = ‘5’ DGMGRL> edit DATABASE sDB01 set property delaymins=2; Property “delaymins” updated ARC3: Archive log thread 1 sequence 3199 available in 5 minute(s) Tue Dec 03 15:34:59 2013… Continue reading Changing the log apply delay (DelayMins)

sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs Let’s take the following setup C:\tmp>type dir1\sqlnet.ora NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM) NAMES.DIRECTORY_PATH = (TNSNAMES) C:\tmp>type dir1\tnsnames.ora C:\tmp>type dir2\sqlnet.ora NAMES.DEFAULT_DOMAIN = (EXAMPLE.ORG) NAMES.DIRECTORY_PATH = (TNSNAMES) C:\tmp>type dir2\tnsnames.ora You set TNS_ADMIN to dir1 and your current directory is dir2.… Continue reading sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects; exec dbms_stats.gather_table_stats(user, ‘T1’) explain plan for… Continue reading Monitoring details on your explain plan

Delete one billion row

To delete large number of rows, for instance rows with date until 2010, you can issue this simple statement. SQL> DELETE FROM T WHERE C

Categorized as dba, sql Tagged

When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality SQL> select message from v$session_longops where target=’SCOTT.EMP’; MESSAGE ———————————————————— Table Scan: SCOTT.EMP: 7377612 out of 629683 Blocks done The total work is the Oracle estimation : SQL> select blocks from dba_tables where table_name=’EMP’; BLOCKS ———- 629683 This may differ quite a lot… Continue reading When v$session_longops is not long enough

How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history. Without catalog : select to_char(d,'”W”IW-IYYY’) compl, sum(b)/1024/1024/1024*8192 GB from ( select max(DATAFILE_BLOCKS) b, trunc(completion_time,’IW’) d from v$backup_datafile group by FILE# ,trunc(completion_time,’IW’) ) group by d order by d; COMPL GB ——– —— W30-2012 3.73 W31-2012 4.84 W32-2012… Continue reading How big was my database last month

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack). Those tables could also be accessed manually. SELECT savtime,owner,object_name,rowcnt,blkcnt FROM sys.WRI$_OPTSTAT_TAB_HISTORY w, dba_objects o WHERE o.owner=’SCOTT’ AND o.object_name=’EMP’ and o.object_id = W.OBJ# ORDER BY o.owner, o.object_name, w.savtime; SAVTIME OWNER… Continue reading How big was my table yesterday

Drop database link in another schema

Today I wrote this script : drop_database_link.sql accept owner char prompt “Enter database link owner : ” accept db_link char prompt “Enter link name : ” begin dbms_scheduler.create_job( job_name=>’&owner..drop_database_link’, job_type=>’PLSQL_BLOCK’, job_action=>’BEGIN execute immediate ”drop database link &db_link”;END;’ ); dbms_scheduler.run_job(‘&owner..drop_database_link’,false); dbms_lock.sleep(2); dbms_scheduler.drop_job(‘&owner..drop_database_link’); end; / I am using the scheduler to run a job as another user.… Continue reading Drop database link in another schema

old-hash, SHA-1, SHA-2/512

Until pretty recently, only the dubious unsalted proprietary algorithm was available to store Oracle passwords. A bunch of tool where at the time able to decode any 6-8 characters in no time, and the rainbow approach was to precalculate all possibles passwords for a specific user. Those time are not really for away, only starting… Continue reading old-hash, SHA-1, SHA-2/512

shutdown timeout

I do not like shutdown abort (see this post). I always use shutdown immediate and it always work… well almost always. Today I discovered a 9iR2 new feature : shutdown timeout ! Shutdown Timeout If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013:… Continue reading shutdown timeout

OCM 11g upgrade

Last Friday I went to Munich for THE upgrade exam. I have a few recommendations based on my preparation. DISCLAIMER: no recommendation is based on the exam Check you have a similar environment. You will get 11gR1 database and 10g entreprise manager. Personnaly I did my preparation on 11gR2 and 11g/12c em, I did not… Continue reading OCM 11g upgrade

grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations. Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role. Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE update, 2012-07-24 For purge… Continue reading grant select on sys tables

What does # mean in sqlplus?

The script used to be shutdown abort it has been replaced by #shutdown abort shutdown immediate Let’s try ! SQL> #shutdown abort ORACLE instance shut down. SQL> shutdown immediate ORA-01012: not logged on sqlplus just silently ignored the # symbol and executed the first statement. Thanks to Maxim comment, here is a new case to… Continue reading What does # mean in sqlplus?

How to get rid of corrupted blocks without a backup

First, you identify the blocks in alert log or with db verify $ dbv BLOCKSIZE=8192 file=sysaux01.dbf DBV-00201: Block, DBA 12629823, marked corrupt for invalid redo application … DBVERIFY – Verification complete Total Pages Examined : 131072 Total Pages Processed (Data) : 69691 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 28669 Total Pages… Continue reading How to get rid of corrupted blocks without a backup

Transport tablespace over db links

You do not want to export the metadata from the source database, but rather use a database link to get this. As prerequisite, you have made a set of self-contained tablespaces in read-only mode and you have copied the datafiles. SQL> create tablespace test datafile ‘/u02/oradata/db01/test01.dbf’ size 10m; Tablespace created. SQL> create table scott.x(x number)… Continue reading Transport tablespace over db links

Categorized as dba Tagged

Fast start failover

There are loads of things to do to prepare you for a fast start failover. First you must have a working set of primary / standby database Then you must have both databases in flashback mode. Then verify your dataguard configuration with OEM. Ok, once you are so far, you will need to review your… Continue reading Fast start failover

delete unused shared memory segments from an Oracle instance

Once upon a time, a dba issues some kill -9 to clean up dying database processes. Or the database instance crashes. This will left some shared memory segments. Note 68281.1 describe how to remove them on a server with multiple databases. First, list the ipc process $ ipcs IPC status from /dev/mem as of Mon… Continue reading delete unused shared memory segments from an Oracle instance

Generate network graph from command line

I recently wrote on gnuplot, today I tried another command line utility to generate graphs, graphviz, version 2.24.0 on AIX5L. Pretty straightforward syntax : ( echo “digraph Emp {” sqlplus -s -L scott/tiger