alter user identified by values in 11g

I wrote about dba_users changes in 11g . When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords. When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved. SQL> create user u identified by u; User… Continue reading alter user identified by values in 11g

Oracle 10.2.0.4

Sven’s Technik-Blog » Blog Archive » Oracle 10.2.0.4 für Linux x86 ist verfügbar… Patchset 6810189 is now available for Download on Metalink. select * from v$version; BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod PL/SQL Release 10.2.0.4.0 – Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 – Production NLSRTL Version 10.2.0.4.0… Continue reading Oracle 10.2.0.4

add a new language to Oracle

I first thought of adding Klingon. Well, finally I added Romansh, which is the fourth official language in my country. Ok, here we go : $ $ORACLE_HOME/nls/lbuilder/lbuilder & The Oracle Locale Builder tool is started. File –> New… –> Language You specify the language, the spelling for January, for Monday, etc… Most fields are mandatory.… Continue reading add a new language to Oracle

milliseconds in alert log

In Oracle11g the alert log is an XML file. The old style alertSID.log is created out of the log.xml for backward compatibility only. However, some exciting enhancement are not noticable in the old one. $ tail -3 alert*.log Mon Feb 04 15:52:38 2008 ALTER SYSTEM SET recyclebin=’OFF’ SCOPE=SPFILE; ALTER SYSTEM SET recyclebin=’ON’ SCOPE=SPFILE; If I… Continue reading milliseconds in alert log

How to resolve ORA-09925 ?

This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual… There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit]. 1) AUDIT_FILE_DEST is not writable $ env _=/usr/bin/env ORACLE_SID=FOO TERM=dtterm ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3 PWD=/u01/app/oracle/product/10.2.0/db_3 $ $ORACLE_HOME/bin/sqlplus -L “/ as sysdba”… Continue reading How to resolve ORA-09925 ?

Oracle 11g Hot patching

Online Patching : you can apply or roll back online patches while the RDBMS instance is running 1) download an interim patch for 11g, f.ex. dummy patch 6198642 2) unzip p6198642_111060_LINUX.zip 3) cd 6198642 4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql Invoking OPatch 11.1.0.6.0 Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation.… Continue reading Oracle 11g Hot patching

Display a blob

I have a table with a blob create table t(b blob); insert into t values (‘585858’); In 11g sql*plus, I can display raw data select b from t; B —— 585858 Ok, but if I want to display XXX (the character content) select utl_raw.cast_to_varchar2(b) from t; UTL — XXX However, in sql, a raw cannot… Continue reading Display a blob

Please RTFOM !

Today I opened two SR about flashback archive in 11g. In one of them, I complained that user SCOTT was not allowed to create a flashback archive. In the doc that I downloaded a few weeks ago I read : Prerequisites You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data… Continue reading Please RTFOM !

Oracle Database 11g: The Top Features for DBAs and Developers

I am always delighted to read the top features by Arup Nanda. He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range… Continue reading Oracle Database 11g: The Top Features for DBAs and Developers

flashback archive table

One of the problem with flashback queries in 10g and before is that you never know if it will works, especially you cannot expect to have flashback queries working for very old tables. Let’s imagine you want to export your CUSTOMER as of 30/6/2007. No chance in 10g… Well, with 11g, you can create a… Continue reading flashback archive table

on delete cascade

The use of a referential integrity constraint is to enforce that each child record has a parent. SQL> CREATE TABLE DEPT 2 (DEPTNO NUMBER PRIMARY KEY, 3 DNAME VARCHAR2(10)) ; Table created. SQL> CREATE TABLE EMP 2 (EMPNO NUMBER PRIMARY KEY, 3 ENAME VARCHAR2(10), 4 DEPTNO NUMBER 5 CONSTRAINT EMP_DEPT_FK 6 REFERENCES DEPT(deptno)); Table created.… Continue reading on delete cascade

errorlogging in 11g

This is a very neat feature in 11g. I have a script called foo.sql create table t(x number primary key); insert into t(x) values (1); insert into t(x) values (2); insert into t(x) values (2); insert into t(x) values (3); commit; It is eyes-popping that this script will return an error, but which one? Let’s… Continue reading errorlogging in 11g

How to compare schema

If you have receive ddl statements from your developer and you want to check if it matches the current state of the development database, because the developer have done a lot of change in a quick and undocumented manner, what are your options? I found this handy feature in Toad : 1) I create my… Continue reading How to compare schema

ORA-01466: unable to read data – table definition has changed

I re-edited this post and it is unresolved yet. I thought it was related to system time, but apparently not 😮 SQL> create table t(x number); Table created. SQL> set transaction read only ; Transaction set. SQL> select * from t; select * from t * ERROR at line 1: ORA-01466: unable to read data… Continue reading ORA-01466: unable to read data – table definition has changed

dbms_xplan and v$sql_plan

do not miss yas comment ! tested in 10.2 create or replace procedure explain_plan(hash_value number) is begin insert into plan_table select null, (select nvl(max(plan_id),0)+1 from plan_table), timestamp, remarks, operation, options, object_node, object_owner, object_name, object_alias, null, object_type, optimizer, search_columns, id, parent_id, depth, position, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, other, other_xml, distribution, cpu_cost, io_cost, temp_space,… Continue reading dbms_xplan and v$sql_plan

constraints for referential integrity

On the developpez.net forums I answered a question about referential integrity. How can you delete/update a parent row when the child exist ? SQL> create table continent( 2 name varchar2(10), 3 constraint continent_pk primary key(name)); Table created. SQL> create table country( 2 name varchar2(10), 3 continent varchar2(10), 4 constraint country_pk 5 primary key(name), 6 constraint… Continue reading constraints for referential integrity

Published
Categorized as dba, sql

v$sql and bind variable

When you see something like select * from t where x = :1 you may wonder what is :1 Ok, here is a quick join I tested in 10gR2 SQL> var y varchar2(255) SQL> exec :y:=’SCOTT’ PL/SQL procedure successfully completed. SQL> select job from emp where ename=:y; JOB ——— ANALYST SQL> select sql_text,name,value_string,datatype_string 2 from… Continue reading v$sql and bind variable

CPU2007Apr

I just downloaded and installed the Critical Patch Update April 2007 $ lsnrctl stop … The command completed successfully $ sqlplus / as sysdba SQL> shutdown immediate … ORACLE instance shut down. SQL> quit $ cd /tmp/5901891 $ ORACLE_HOME/OPatch/opatch apply … Finish at Wed Apr 18 10:28:17 CEST 2007 $ lsnrctl start … The command… Continue reading CPU2007Apr

backup database keep forever logs

If you want to keep some backups forever, you maybe tried RMAN> backup database keep forever logs; Starting backup at 13.04.2007 13:58:04 … backup will never be obsolete archived logs required to recover from this backup will expire when this backup expires … Finished backup at 13.04.2007 13:58:23 but the problem is that the archivelogs… Continue reading backup database keep forever logs

variable in a view

Imagine you have a view and you want to have a parameter in your view. You cannot have a bind variable in your view. But you could have a function which return a package variable. And this package variable could be set manually for your session Here we go SQL> create or replace package p… Continue reading variable in a view

why is bitmap index not designed for OLTP

In case you do not know it yet, having a bitmap on columns like GENDER(male/female) is a very bad practice in OLTP, because each insert does lock the whole table create table t(name varchar2(10), gender varchar2(10)); create bitmap index bi on t(gender); +——————————–+ +——————————–+ | Session 1 | | Session 2 | +——————————–+ +——————————–+ |… Continue reading why is bitmap index not designed for OLTP

user identified externally with SSL certificate

Today I configured my database to identify users with certificates. Check my previous post listener with tcps to find out how to configure a listener with SSL, which is a requisite. Ok, I have a listener.ora and a tnsnames.ora with SSL. I do not need a sqlnet.ora, the default values work. listener.ora LISTENER= (DESCRIPTION_LIST= (DESCRIPTION=… Continue reading user identified externally with SSL certificate

check if using tcp or tcps

yesterday I wrote howto listener ssl. To check the protocol : SQL> select sys_context( ‘USERENV’,’NETWORK_PROTOCOL’) PROTOCOL from dual; PROTOCOL ——– tcps

listener with tcps

How can you use SSL to encrypt your network traffic? Here is how I did it. Install Oracle Certification Authority 10.1.4 you need a CA to approve a certification request Install Oracle Database 10gR2 Enterprise Edition with Advanced Security Options Start Wallet Manager from Database Oracle Home, start $ORACLE_HOME/bin/owm create a new Wallet define a… Continue reading listener with tcps