better than CTAS

SQL> create table t1(x number primary key); Table created. SQL> desc t1 Name Null? Type ———————– ——– —————- X NOT NULL NUMBER SQL> create table t2 as select * from t1; Table created. SQL> desc t2 Name Null? Type ———————– ——– —————- X NUMBER The table T2 has the column X, but not the constraint […]

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

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

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

Datapump : table like ‘FOO%’ or like ‘BAR%’

Today I tried to put two like condition in an INCLUDE clause of datapump. I have the following tables SQL> select table_name from user_tables order by 1; TABLE_NAME —————————— AAA BAR1 *** BAR2 *** BLA FOO *** FOO1 *** GOZ and I want tables like BAR% and tables likes FOO% First try : $ expdp […]