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.
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?
How to take table structure in oracle? Actually I got it through this command "SELECT dbms_metadata.get_ddl(a.object_type,a.object_name) FROM user_objects a where object_type != 'PACKAGE BODY'"
any other way to get it? I need like table name field name datatype
I am struck in a requirement to show the organization structure in a Top Down Organizational structure.
Is there any way we can make the well structured report OR form?
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedI 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?
--this for txn details
CREATE TABLE txn_det(
txnid NUMBER PRIMARY KEY,
amount NUMBER,
status varchar2(50),
cust_id NUMBER);
----this for customer details
CREATE TABLE cust_det(
cust_id NUMBER PRIMARY KEY,
cust_name VARCHAR2(50),
cust_acc number(15));
--data to insert for customer table
INSERT INTO cust_det VALUES(101,'Miller','12345');
INSERT INTO cust_det VALUES(201,'Scott','45678');
----data to insert for txn table
INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);
INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);
insert into txn_det values('tx0049',78.12,'success',101);
INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);
Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det table but I know that encourgaes redundancy.
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?
I have a table called cust_file, his table consists of a lot of columns (one of these columns called cus_tax) and have a lot of data,I use oracle 11g, I want to change the default value of the column cus_tax to be equal 1, I wrote
ALTER TABLE cust_file MODIFY(cus_tax DEFAULT 1); table alteredbut
after I inserted new data to test the operation, I found that the new record has a value
= null for the column cus_taxthen
I tested using the following query select
data_default from all_tab_columns where table_name='CUST_FILE' and column_name='CUS_TAX'; no rows selected...
Change the default value of the column cus_tax.
create table my_rows
(
my_envvarchar2(100),
anumber(2),
bnumber(2)
)
/
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
[code]....
The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.
i would like to view all table statructure of a schema in a file. i need to take print out of it for one analysys.
i did like this
exp user/pwd file=dumpfile.dmp full=y rows=n log=logfilename.log
imp user/pwd file=dumpfile.dmp full=y indexfile=indexfile.sql logfile=logfilename.log
but
its look like this i could not format
REM CREATE TABLE "SYSTEM"."DEF$_AQCALL" ("Q_NAME" VARCHAR2(30), "MSGID"
REM RAW(16), "CORRID" VARCHAR2(128), "PRIORITY" NUMBER, "STATE" NUMBER,
REM "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO"
REM TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN"
REM NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" NUMBER,
[Code] ..........
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]...
my working is relating with PUMP of oracle.
I would like to use command, for ex:
expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp COMPRESSION=ALL LOGFILE=export.log SCHEMAS=hr
But some tables in Schema HR, I don't want to export data, just only need table structure.
know the process of exporting only the table structure of a Database without the actual content of it.
Note:: I don't know how many tables are present in the DB.
a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.
View 2 Replies View Relatedcreate trigger on certain column for table structure.
SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....
I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .
Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)
Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column
I have a requirement to be coded like this:
A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,
means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type
func f return pl_tab
as
...
end;
note : pl_tab_type will vary for each run of function f
i.e.,for example, pl_tab_type can be changed to as follows:
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))
how to return pl/sql table of dynamic type from func,
I have a small question is it possible to find the details of a user who modified the structure of a table, including what command he ran to change the structure of the table?
View 2 Replies View Relatedi want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?
following Output needed
userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername
how can i export all table structure as script
note : i have multi schema's not one schema
i use
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM DBA_TABLES u;
but i need it for all schemas
sample code in OCI in C for receiving records of table in array of structure? Or dynamically storing the result-set in an array..using array of pointers to structure..
View 6 Replies View RelatedI am working on Pro*C and i have a requirement where i need to select all the rows from a table into a c - structure variable. Since i get to know the no of rows in the table which is getting selected only at run time, i need to create a pointer variable to the structure and i'll allocate the size to it based on the count of rows in the table using malloc or calloc.I tried allocating memory using calloc and it does not show any error. But when i when the exec select statement run it shows an error.
Statements i have used:
struct common *comp;
struct common_ind *comp_i;
comp = (struct common*) calloc(rowcount, sizeof(struct common));
comp_i = (struct common_ind*) calloc(rowcount, sizeof(struct common_ind));
exec sql at db1 select * into :comp indicator :comp_i from tab1;
Error i get :
Stop Error: -2112
Stop Error: -1012
Stop Error: -1012
howto to setup an oracle server and "import" my old databases just from the copied directory structure d:\oracle\product\... ("cold backup") to the new install.i was trying to setup an new server by shutting down the db and just copying back my data/control/log/*.ora files which doesnt seem to work.
i could see the old tables structures in the local sqlplus console, but the databases are not reachable over the net.
also the enterprise manager is not able to startup the dbs, it show just an running listener.
The best way to structure my schemas within a single Oracle instance to support multiple project development. At the moment, within an Oracle instance I have Area_Dev and Area1_Test schemas, with the intention that Project Xv1.0 would use Dev schema for development and Test schema for testing.
Lets say I want to start on Project Xv2.0 development while I am still finalizing Project Xv.10 development what is the best way of accommodating that, without creating individual schemas for each project?
I created a table and some type definitions in oracle 10.2.0.3 DB as follows. There is also a procedure that I defined and is shown below.
SQL> create table test_proc
(
test_name varchar2(10),
test_ver number (3,3),
active_flag number(2)
);
Table created.
SQL> select * from test_proc;
TEST_NAME TEST_VER ACTIVE_FLAG
---------- ---------- -----------
AFP 1.5 2
PSA 11.89 0
EHIV 99.5 3
aHAVM 1.45 9
[code]....
Now from Pro*C function I want to pass a similar array of structure to this procedure and return it via the out parameter of the procedure back to Pro*C. How do I do it?
I am using the attached program but its giving me compiler error as follows..
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
1
PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS'
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
[code]....
I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?
View 2 Replies View RelatedTo convert the following traditional exp/imp to datapump .
1. exp custom/custom FILE=expgt.dmp TABLES=raj.GENERAL_TABLE QUERY = \"WHERE to_date\(lchg_time\) \= \(select db_date from mtda\) \" indexes=n statistics=none
2.exp custom/custom FILE=expdata.dmp TABLES=raj.BAL_TABLE,raj.GEN_TABLE ,raj.CREDITS_TABLE QUERY = \"WHERE to_date\(lchg_time\) \>= \(select db_date from mtda\) \" indexes=n statistics=none
in the second one there tables to the same dmp file with the same where clause
We have eight servers. One of them is for centeral management. The other seven is for development and test databases. Every night, we start a process which exports database schemas (without data) into the centeral server. An example of the process we run is just like below:
CODEcd /data1/backup
mknod exp_pipe_oracle1 p
compress<exp_pipe_oracle1>dev.dmp.Z&
exp user/****@dev file=exp_pipe_oracle1 owner=schema1,schema2,schema3 statistics=none rows=n log=dev.log
As you can see, we use @dev to connect remote database. But dump file is created in local (centeral) server. We are planning to use datapump instead of classic export in future. But I couldn't find a solution, that could take a database export in a remote server and create dump file in local. I have looked at NETWORK_LINK parameter; but it doesn't seem to work for our case.
Is it possible to backup a remote database with datapump and make it create dumpfile in local? (Of course we can use some solutions such as NFS, but we really not prefer this; if datapump has ability to remotly backup.)
I have a problem to read in data from dmp file to target table.
I've created a dmp file using:
CREATE TABLE table_name
ORGANIZATION EXTERNAL
(TYPE oracle_datapump
DEFAULT DIRECTORY directory_name
LOCATION ('file_name.dmp')
) AS SELECT col1, col2, col3 from source_table;
[Code]...
The file created via db_link from target db to remote db and then the data are insterted into target db table target_table.
desc target_table
col1 varchar2(20)
col2 number
col3 number
There are no values longer than 20 characters in source table, but when I insert into target_table(col1, col2, col3) as select col1, col2 col3 from source_table; I get ORA-12899: value too large for column "target_table"."col1" (actual: 25, maximum: 20).
I gues it has something to do with how the oracle datapump stored the data in dmp file. When I select col1 from source_table where length(col1) > 20; I get two values which clearly are not longer than 20 characters.
Selected values are:
748473358
693197674
where the bug is hidden or any "normal" workaround?
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?