Duplicate table over database link

The old-style approach would be CREATE TABLE T AS SELECT * FROM T@L, where T is the table you want to duplicate and L the database link pointing to the remote database If you want to keep more info about the structure of t, the constraints, the indexes, you may use Datapump. Here it is… […]

On using ROWID

I have been challenged to assert the safety of rowid in a sql statement. Against all my beliefs, it is not safe to assume ROWID is consistent over one sql statement. If the ROWID changes, and you use ROWID in your query, you may get inconsistent results. Obviously I would not write such a post […]

send graph per mail from sqlplus

How to send a graph with a single command from your database to your mail in Unix? I tried this (gnuplot is available for Solaris, AIX and most Unix derivates) : echo ‘ set hea off pages 0 feed off prom set title “salaries of EMP” prom unset key prom unset xtics prom unset xlabel […]

On the number of installed components

I recently posted about network fained fine grained security. More precisely, I posted about the new requirement to have XDB to be able to send a mail or do a nslookup on 11g. What option should you install on your database ? SQL> select COMP_NAME,VERSION from DBA_REGISTRY; COMP_NAME VERSION —————————————- ———- Oracle Database Catalog Views […]

how to run UTL_TCP, UTL_SMTP and the like in 11g

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco : The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged […]

Do not upgrade 11.2.0.1 to 11.2.0.1

If you do run @?/rdbms/admin/catupgrd for an 11.2.0.1 Oracle Home on a 11.2.0.1, you may later realize some objects are missing (probably related to deferred segment creation). SQL> delete from t1 2 where id in ( 3 select ca.id from ca, p 4 where p.no_form like ‘%02.98’ 5 and p.id = ca.prod_id 6 ); delete […]

How to change the connection string of the Oracle Enterprise Manager Grid Control 11g repository

If you moved your repository to a new host and want to change the connection string, no need to drop it, no need to messup in the properties or xml files, simply read the doc http://download.oracle.com/docs/cd/E11857_01/em.111/e16790/ha_agent.htm#autoId13 emctl config oms -store_repos_details (-repos_host -repos_port -repos_sid | -repos_conndesc ) -repos_user [-repos_pwd ] [-no_check_db] Yes it works!