Server Utilities :: DBMS-DATAPUMP.metadata-filter And Long Table List?

Jun 9, 2010

I have a problem with DBMS_DATAPUMP.metadata_filter.Let's suppose that I need to export a huge list of tables (a,b,c,d,e,f,g,h,i...). Let's suppose that the list is dynamic do NOT want to use

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (''a'', ''b'', ...)',
object_type => NULL);

In my_export_table there is the list:

CREATE TABLE my_export_table
(
EXPORT_OBJECT_NAME VARCHAR2(50 BYTE)
)

Now I'm trying to use this form:

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (SELECT a.export_object_name FROM my_export_table a, user_objects b WHERE a.export_object_name = b.object_name AND b.object_type = ''TABLE'')',
object_type => NULL
);

but it results in error.

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist

[code]...

View 1 Replies


ADVERTISEMENT

Server Utilities :: DBMS-DATAPUMP Import Error?

Oct 25, 2010

have a requirement to load .dmp files into existing staging tables and there is package to load the ODS tables from staging.So,I thought of using DBMS_Datapump utility to import the data from .DMP files to the Tables and this need be automated.

--Create Directory
CREATE
OR REPLACE DIRECTORY test_dir AS 'C:Test'

--grant Access to the User
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

--Script to import
DECLARE
l_dp_handle1 NUMBER;
BEGIN
l_dp_handle1 := dbms_datapump.OPEN(operation => 'IMPORT',

[code]...

Errors

ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 938
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4590
ORA-06512: at line 4

View 6 Replies View Related

Server Utilities :: Datapump Export Taking Long Time (HUNG)?

Aug 23, 2012

Expdp directory=xxx.dmp dumpfile=aaa.dmp logfile=xxx.log FULL=Y
: :: : : :: : : : ;
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.87 MB
Processing object type SCHEMA_EXPORT/USER

[code]...

then my export hangs..... checked in alert log nothing found.and then killed the job and reran again but same....checked the status and it's saying EXECUTING.

View 15 Replies View Related

Server Utilities :: Rename Table While Using Datapump Import?

Mar 18, 2013

I have a Datapump Export File which was created in Schema mode.

I have to import the tabelles in a new database where a have to use the REMAP SCHEMA Parameter.

Additionally I would like to add a prefix to tablenames.

For example:

original tablename: THE_TABLE
Name after import: IMP_THE_TABLE

Is there a way to add a prefix while using Datapump Import?

View 5 Replies View Related

SQL & PL/SQL :: Using DBMS-METADATA To Transform Tablespace DDL?

May 18, 2010

I'm currently busy with database consolidation, so I'm searching for a solution to generate some useful DDL to prepare the new target database before importing the application's data. This should include TABLESPACE DDL and all additional users with their grants.

So first I thought of developing a simple script, which will create the CREATE TABLESPACE DDL but with transformed datafile paths.But my throws some errors and I don't understand why:

ORA-31604: invalid NAME parameter "STORAGE" for object type TABLESPACE in function SET_FILTER
declare
l_hObject NUMBER;
l_Objddl CLOB;

[code]...

View 7 Replies View Related

Server Utilities :: Exporting Metadata Backup

Jul 19, 2011

i am using a schema which i need to take a backup of meta data only i am using exp utility

exp shan/shan@shan file=/backup_dump/shan.dmp log=/backup_dump/shan.log owner=shan rows=n

but it will return me below error, i have only access to user shan our client cant allow me to use system or sysdba schema or any other required grants or privileges so is there any way to take metadata backup of user shan from user shan.

EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

View 3 Replies View Related

Server Utilities :: Datapump API?

Jun 15, 2011

I am trying to use the export the database using datapump API dbms_datapump.I want to export the entire database excluding some schemas.

My question is how to exclude some schemas using datapump API?

View 1 Replies View Related

Server Utilities :: Data Migration Using Datapump?

May 11, 2011

I got an assignment to create Oracle 11g db. I will be provided the full datapump export dump of an Oracle 10g db in linux. I need to import it to 11g Database in Windows. I have no information about the tablespaces, users etc I have created db with system,sysaux,undotbs temp and users tablespaces.

View 28 Replies View Related

Server Utilities :: Export XML Object Using Datapump

Jan 2, 2012

As a part of our back up we used to export the production data every day using Original export Utility but from 11g original export Utility is de supported and also datapump doesn't support XML Objects so is there any other way to export the full database else any option to export xml Object using datapump.

View 2 Replies View Related

Server Utilities :: Oracle Directory And Datapump

Mar 31, 2010

We have three instances on the RAC, When I create a directory is there a default instance that it gets created on? When I execute an datapump export on instance 1, it works but on 2 and 3 it fails with error relating to directory.

Error -
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

Is my only option is to use shared storage space?

View 6 Replies View Related

Server Utilities :: Datapump And Index Building

Jan 13, 2012

Currently we are using "exp and imp" utilities to unload from production and load into Dev server. While importing, we are following below steps

(1) Load only data [by specifying INDEXES=N in the par file]
(2) Unlock statistics
(3) Load indexes, other objects [by specifying ROWS=N]

After doing these steps, both data, indexes and others objects are loaded. To verify indexes, we are checking DBA_INDEXES.

DBA_INDEXES :
-------------
OWNER INDEX_NAME TABLE_NAME STATUS LAST_ANALYZED
----- ---------- ---------- ------ -------------
MYSCH CP_INDEX_1 CP_TABLE_1 VALID 14/JAN/12

Question :-

(1) Does imp utility rebuild the indexes while loading data ? or it simply takes the rows from dump and load into test system without building from scratch ?

(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?

(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ?
(b) I am planning to implement this in two steps :

first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE

then, load data - CONTENT=DATA_ONLY.

View 1 Replies View Related

Server Utilities :: Using The Datapump Tool To Migrate 10g DB To 11g?

Jan 3, 2012

I am trying to use the datapump tool to migrate a 10g db to 11g. Everything works fine except for the "nameless" check constraints.

View 7 Replies View Related

Server Utilities :: ORA-31685 Deploy Schema Using DATAPUMP API

Feb 7, 2011

I'm trying to deploy the schema using DATAPUMP API. The user from which the schema get deployed has the direct privilege of CREATE USER (not through role). But got the insufficient privileges error.

Processing object type SCHEMA_EXPORT/USER
ORA-31685: Object type USER:"SCOTT1" failed due to insufficient privileges. Failing sql is:
CREATE USER "SCOTT1" IDENTIFIED BY VALUES '4EBB0DDE3C79FE47' DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP2" PROFILE "APP_PROFILE".

But the user get created successfully when run the CREATE statement manually.I have created the user manually and again run the deployment procedure. Got the below error for ROLE_GRANTS.

Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01932: ADMIN option not granted for role 'EXP_FULL_DATABASE'
Failing sql is:
GRANT "EXP_FULL_DATABASE" TO "SCOTT1"

The user has EXP_FULL_DATABASE with ADMIN Option and IMP_FULL_DATABASE with ADMIN option direct privileges.which privileges the user needs to deploy the schema successfully?

View 1 Replies View Related

Server Utilities :: Datapump Import And Referential Integrity Constraints

May 23, 2012

I am using Datapump import using database link to import an entire schema from another Server but it gives issues with constraints.I tried to first only import the metadata and then disable the constraints and import data and enable constraint but in this case the temp tablespace keeps filling up and i am out of space. Is there any method to do a full import including constraints and indexes.

View 7 Replies View Related

Server Utilities :: Total Estimation Using BLOCKS Method - Datapump

Apr 5, 2013

I have analyzed that, datapump estimation is 9.902GB. When i check size of .dmp file, it's shows 1.44Gb.

Export: Release 11.2.0.1.0 - Production on Fri Apr 5 02:00:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=expdp_LVGITRN_30_24_050413.dmp directory=DP_DIR logfile=expdp_LVGITRN_30_24_050413.log full=y exclude=statistics
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: [bold]9.902 GB [/bold]

Why Datapump estimate so much than actual size?

View 8 Replies View Related

Server Utilities :: How To Take All Schema Metadata Export Except One Schema (scott)

Jul 5, 2012

how to take all schema metadata export except one schema (scott)

can i use like EXCLUDE=schema:"IN('SCOTT')

View 4 Replies View Related

SQL & PL/SQL :: Filter Each Row In A Select Statement Based Upon A List?

May 18, 2011

Is it possible to filter each row in a select statement based upon a list that you loop through. For instance in a where clause having an in statement but rather than selecting any value in the in statement looping the main select through each value in the "in" filter.

Eg:

CREATE TABLE TRANSAC(
Item_id number ,
Transaction_Date DATE,
Category_id number
)

You could then write

SELECT MAX(Transaction_Date)
from TRANSAC
where category_id = '141'

and then where category_id = '142' then '143' etc.

I understand i could use an inline view instead of a where clause but the issue is that i already have the list of values I want to compare against.

View 5 Replies View Related

Server Utilities :: ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jul 6, 2010

I was cloning a schema user1 as user2 in the same database.

user1 had quota on 2 tablespaces user1_data and user1_index.

I created user with name as user2.

I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege) Now exported user1 schema as follows

exp system/<passowrd> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user1_schema_exp.log owner=user1 rows=y constraints=y triggers=y indexes=y statistics=none recordlength=65535 compress=no consistent=n grants=y

then imported in in user2 schema as follows

imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user2_schema_imp.log fromuser=user1 touser=user2 rows=y constraints=y indexes=y statistics=none recordlength=65535 grants=y

during import i encountered following errors for so many constraints

"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:

I found that the it happened as the primary key of table1 was not created for which error was logged in the log file

. . importing table "table1" 19441 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "table1" ADD CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_"
"ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE "
. . importing table "table5" 0 rows imported

However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?

I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import.

I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same.

There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export.

View 4 Replies View Related

Server Administration :: Reorganization Includes A Table With A LONG Column

Jan 18, 2012

I am trying to reoarganise a tablespace with Enterprise Manager from manual to automatic, but script generation gives the following warnng :

Quote:Reorganization includes a table with a LONG column. To support reorganization of tables with LONG columns that are greater than 32Kbytes the external procedure MGMT$REORG_MOVELONGCOMMAND must be configured properly. It has been determined this external procedure is not currently configured as expected. Configure SQL*Net appropriately to allow it to call the external procedure service process.

I made changes suggested by [URL] ..... in the section Using Reorganize Objects with LONG Columns but the warning persists. I noticed however that $ORACLE_HOME/lib/libnmuc.so is an empty file (0 bytes).

Oracle Database 10g 10.1.0.2.0
Solaris (SunOS 5.9 Generic_117171-07 (64-bit))
Oracle home /data/lun1/oracle/product/10.1.0/db_1
{ORACLE_HOME}/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /usr/oracle/product/10.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
[code]....

View 1 Replies View Related

Forms :: Filter Hard Coded Values In List Item (Tlist) Based On Value Entered In Text Item

May 22, 2010

I have 2 items in my form:

1) Text Item
2) Tlist

Upon form load, TList will be populated with predefined item. The behavior i am trying to achieve is to have a text item so user could entered specific text which will then filter the values in TList .

View 1 Replies View Related

Data Filter Within Table?

Sep 5, 2013

I am not sure if Oracle has a simple solution for a problem that I have in retrieving data?

Conditions:

As per rule we have to hold data from 7 - 10 years so the data can be searched at any given time.Don't want to purge data and keep the historic data separate so the request can be redirected based on the year.

I am looking for a solution similar to windows .ocx search. When a application is requesting an ocx (active X control) the search happens in C;\windows\system32 and if the ocx doesn't exists then the search is done on the entire system. Similarly can a table be partitioned (just the term I am using not discussing Oracle partition here) so I can partition data from 2011,2012 and 2013 to search first and if the searched data doesn't exists then search the other partition (data from 2003-2010) ?

View 2 Replies View Related

Datapump - Importing With Inconsistent Table Structure

Apr 30, 2013

I am receiving two large export files from a vendor, so I have no control over the contents. I need to import these into our database. The two export files are very similar, except the one has slightly differenet columns in it. So, export file 1 may have a table:

COLUMN_A
COLUMN_B
COLUMN_C

The second file may have:

COLUMN_A
COLUMN_B
COLUMN_D

At the destination, I have a table that has:

COLUMN_A
COLUMN_B
COLUMN_C
COLUMN_D

Is there a parameter that would let me interchangably import either (or both) files into this destination table? This is my first attempt at data pump - but I know using import this has caused me issues. Not sure if the same limitations exist? Will the missing columns cause it to fail?

View 3 Replies View Related

Oracle Datapump - Table Structure Change?

Oct 19, 2010

we have daily partitioned table, and for backup we are using data pump (expdp). we policy to drop partition after backup (archiving).

we have archived dump files for 1year, few days back developer made changes with table structure they added one new column to table.

Now we are unable to restore old partitions is there a way to restore partition if new column added / dropped from currect table.

View 4 Replies View Related

Datapump Error On Import To Linux Server?

Sep 27, 2011

have 2 Oracle server. One with Suse Linux (Oracle 10.2.0.4.0) and one with Windows 2003 Server x64 (Oracle 11.2.0.1.0).
I made a mistake by installing Oracle 11 x32 on a x64 server. Nevertheless it works for about half a year. Then my backups
with datapump don't work. I changed the SGA_TARGET down to 1024M and the datapump works again. Now I want to renew the Windows server and want to import the datapump schemes to the Linux server.

Exp: expdp SCHEMA1/****@db1 DIRECTORY=dmpdir DUMPFILE=SCHEMA1_EXPDAT.DMP LOGFILE=SCHEMA1_EXP.LOG VERSION=10.2.0.3 REUSE_DUMPFILES=YES
Imp: impdp SCHEMA1/**** DIRECTORY=dmpdir DUMPFILE=SCHEMA1_EXPDAT.DMP LOGFILE=SCHEMA1_IMP.LOG

Log:
..importing SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
..importing SCHEMA_EXPORT/SEQUENCE/SEQUENCE

[code]...

View 1 Replies View Related

Server Administration :: DBMS-STATS And ORA-20000?

Dec 20, 2011

I've just installed an Oracle 11.2.0.1.0 64 bit server on my windows 7 machine in order to play around while using attempting to run

exec dbms_stats.gather_table_stats('eii','v2x4e')

I get the following:
ORA-20000: Unable to analyze TABLE "EII"."V2X4E", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 20327
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

My initial google searches indicate that I need the select any table and analyze any privileges. I don't think that can be right/appropriate - but I've granted them anyway to no avail.

Select * from user_tables

returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?

View 5 Replies View Related

Server Administration :: DBMS-SCHEDULER For Particular Instance?

Apr 29, 2013

How to create DBMS_SCHEDULER job for particular instance,we have 3 instance we want to schedule few jobs in Instance 2,how to schedule in Particular instance.

View 3 Replies View Related

Server Administration :: Tracking DBMS Job Timing

Jul 20, 2010

I have a database job to ANALYZE TABLES,, it takes more than 40 hours. I want to know exactly how much time does it take..

My job has started on 19th Jul at 02:32 hrs.. What is the way to know the exact time when the job ends.

View 4 Replies View Related

Server Administration :: DBMS Scheduler Jobs Are Not Running

Jan 12, 2011

I have scheduled few jobs (scheduled for each day) which has not run today, which was running successfully till yesterday. When I query dba_scheduler_jobs, i could see last_run_date as yesterday's date 6am and next_run_date is still showing today's date and time (it should show tomorrow's date/time).

View 3 Replies View Related

Server Administration :: Disabling All DBMS-SCHEDULER Jobs?

Jul 13, 2010

When performing test recoveries of a database we often wish to prevent database jobs from running application processes such as (a) sending emails; or (b) logging events to a central logging database. In order to accomplish this, we set the job_queue_processes to zero before opening the database.

This means that no dbms_jobs run on the open instance, but it does not prevent dbms_scheduler jobs from running.How can we prevent any dbms_scheduler jobs from running?

I have looked at setting the MAX_JOB_SLAVE_PROCESSES attribute using DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE, but this cannot be set to zero.

View 3 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....

View 24 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved