Oraclue

Oracle internals, debugging and undocumented features

Category Archives: Tools

Export / Import data over db link – using dbms_datapump and dbms_file_transfer packages

— Datapump FULL MODE EXPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’EXPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED’;
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;

— Transfer dump file to the destination directory
dbms_file_transfer.put_file(
source_directory_object => ‘”DPUMP_DIR”‘,
source_file_name => ‘test.dmp’,
destination_directory_object => ‘”DPUMP_DIR”‘,
destination_file_name => ‘test.dmp’,
destination_database => ‘test.world’);

COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

PROMPT Connecting as  administrator to site 2
CONNECT username/password
— Datapump FULL MODE IMPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’IMPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.set_parameter(h1, ‘STREAMS_CONFIGURATION’, 0);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED’;
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

Advertisements

Script – How to find table inside PL/SQL

SELECT owner,
       object_type,
       object_name,
       object_id,
       status
  FROM sys.DBA_OBJECTS
 WHERE object_id IN
          (    SELECT object_id
                 FROM public_dependency
           CONNECT BY PRIOR object_id = referenced_object_id
           START WITH referenced_object_id =
                         (SELECT object_id
                            FROM sys.
                                  DBA_OBJECTS
                           WHERE     owner = ‘<owner_name>’
                            AND object_name ='<table>’
                            AND object_type = ‘TABLE’))

Installing OEM Agent version 11.1.0.1

Oracle is always changing.It never ends..

The other day I was trying to install new OEM agent version 11.1.0.1.
And of course, like in the past I just started ./runInstaller command .
That did not work and I decided to dig into some documentation ( what I should have done before ) and found it is not possible to install the agent by simply invoking the installer.

They are various supported methods of agent installation.

The following link describes different methods for installing 11g agent:

http://download.oracle.com/docs/cd/E11857_01/install.111/e16847/part_install_agent.htm#DAFEAJID

They are 9 chapters describing different methods.Pick one that works for you.

Oracle Data Pump vs Update

Here is simple scenario:

First I am going to create simple table with two rows and pouplate it with 10000 records:
SQL> create table demo.test ( a number, b number);

Table created.

SQL> begin
for i in 1..10000 loop
insert into demo.test values (i,1);
end loop;
end;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> select count(*) from demo.test;

COUNT(*)
———-
10000

SQL> commit;

Commit complete.

SQL> select * from demo.test where rownum < 10;

A          B
———- ———-
1          1
2          1
3          1
4          1
5          1
6          1
7          1
8          1
9          1

9 rows selected.

Next I am going to alter table and add new column.Also I need to insert some values into this newly created columns:
SQL> ALTER TABLE DEMO.TEST ADD (C  NUMBER);
ALTER TABLE DEMO.TEST ADD (D  NUMBER);
Table altered.

SQL>

Table altered.

SQL> desc test
Name                                      Null?    Type
—————————————– ——– —————————-
A                                                  NUMBER
B                                                  NUMBER
C                                                  NUMBER
D                                                  NUMBER

SQL>  select * from test where rownum < 10;  ( quick check )

A          B          C          D
———- ———- ———- ———-
1          1
2          1
3          1
4          1
5          1
6          1
7          1
8          1
9          1

9 rows selected.
The C and D columns are empty since I did not specified default/virtual value.

e.g.

ALTER TABLE DEMO.TEST  ADD (c  NUMBER  DEFAULT 0);
ALTER TABLE DEMO.TEST  ADD (d  NUMBER  DEFAULT 1);
When I use default/virtual value for column I run into issues with Materialiezed Views.If you do not have MW than this will work.

The other options is to run update statement and populate this columns: Read more of this post

Red Hat Enterprise Virtualization (RHEV)

If you haven’t heard Red Hat released Red Hat Enterprise Virtualization (RHEV).  It has a lot of advantages over proprietary virtualization solutions.

http://www.redhat.com/virtualization/rhev/

http://www.redhat.com/v/swf/rhev/demo.html