I need to take only backup of schema objects with out data using exp (export) into .sql file and need to run that .sql file in the target.because I dont have exp/imp privs on target database.
i have to export data from emp table which has address column and address column contain comma, when i am running below script, the comma part in address field comes in next tab in csv file, is there any way we can avoid shifting to next tab and can have complete address in one tab.
set echo off set verify off set termout on set heading off set pages 50000 [code]....
I have added a bitmap image in my workbook but when i am exporting it into excel or HTML ,only text part of the title is exporting into excel file . The bitmap is only visible in discoverer workbook ,after exporting to excel or HTML, it disappears,
In one of my projects I am exporting a couple of views into a flat file. The export utility is generic and uses dynamic sql to generate a flat file. We have a test environment and a production environment. On both the code is the same. We noticed that the output is different between the environments although it is supposed to be the same. If I export a view in the production I will get a record like this:
The code I am running is not changing any settings explicitly. It looks like this and it will be run as EXECUTE IMMEDIATE:
DECLARE v_sql VARCHAR2 (32000); v_sql_count NUMBER := 0; v_error VARCHAR2 (4000); v_new_file UTL_FILE.file_type; BEGIN [code]........
I also tried to do the following on production in order to get it equal to the test environment:
BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = AMERICAN ' || 'NLS_NUMERIC_CHARACTERS = ''.,''' || 'NLS_TIMESTAMP_FORMAT = ''DD-MON-RR HH.MI.SSXFF AM'''; END;
This would change the formatting for the timestamp columns for almost all files. Almost. Two of those files remain unchanged and still show the decimal separator from the old setting:
i'm working on sql developer my table contains 40 columns and contains around 4 to 5 lakhs records........
when i'm trying to export the results into excel or text file my sql developer is getting hanged... if the result is less than 2lakh record its copying....
I want to export the oracle data into an excel sheet. I have written the code by using UTL_FILE package. but i am getting the output as shown in the screen shot(without formatting the column size as the width of the data it has). But I want the output column width to be set according to the size of the data automatically.
Trying to export a TUNINGSET.Here are my steps below.
select * from dba_sqlset order by name; ID NAME OWNER DESCRIPTION CREATED LAST MOD STATEMENT_COUNT 8LOAD_TEST_1SYS11g upgrade load test 1.15-JUL-1115-JUL-11180 7LOAD_TEST_2SYS11g upgrade load test 2.15-JUL-1115-JUL-11210
[code]...
ERROR at line 1: ORA-19374: invalid staging table ORA-06512: at "SYS.DBMS_SQLTUNE", line 4328 ORA-06512: at "SYS.DBMS_SQLTUNE", line 6829 ORA-06512: at line 2
Now we are having 100+ sql queries and we making all those queries as procedures.after that we want to schedule those procedures and get data to export into excel file.
so we are planning to use utl_file to get data export excel. we may have rows of 30000 above.is it utl_file will be able upload all these rows into excel.any performance issue will come.
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
I am exporting a table that is 3 GB in size and also Partitioned with option NOCOMPRESS specified.
Now when i export it with COMPRESS=N option of exp utility then it should take 3 Gb in target server but will exporting it with COMPRESS=Y will save some storage during import or once NOCOMPRESS option specified on partition has no impact on exp utility COMPRESS=Y option and it will take 3 GB space in both cases
Is this true that whether u specify COMPRESS=N|Y during export it does not matter the size will be 3 GB always after import?
I would like to export few tables from the physical standby which is in read only mode.
I have tried both the exp and expdp methods and could successfully export and import the tables from physical standby using exp unfortunately the expdp does not allow this process from a read only database.
Does this mean that we still have to use the exp feature instead of expdp ?
Note : I would expect a proper response from experts and no unwanted comments like "Contact Oracle support" or "Paste the entire command here" or "Read the Manuals" or "Why i am exporting from Standby and not from Primary" etc.
extract a huge amount of data from a couple of views... the problem is that they want it in TXT files with fixed record length. There will be like 6 files, for a total amount of about 10GB.
export those tables in the fastest possible way? If I'm not mistaken exp and expdp can't create txt files, so do I really need to use utl_file or spool?
I am trying to export a partition of a table and import it to another database. I get the below error when I try to import.
ORA-14400: inserted partition key does not map to any partition
If I export the table(for that particular partition) and import the table(after dropping the table) in destination, the partitions and sub partitions are created without any problem.
The table is Range Partitioned and Sub partitioned in List. So I had to perform the below operation if I want to retain other data in the Destination table.
1. Drop the existing partition 2. Create the partition and sub partition, same as source 3. Execute imp
In fact I had to perform step#2, as if I split the partition also, the sub partition gets replicated in the new partition, which again throws the same error. Is there better way of managing the partitions and subpartition in destination with exp/imp utility, so that I need not perform step#1 and step#2 manually.
I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).
For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?
Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.
I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.
how will i take backup including with primary keys?
Sunddenly my exports hangs at 'exporting cluster definitions'. I had been using this database since last 4 years and it never cause a problem or hangs at this level. here i'm pasting my screen details. it is my production db.
[oracle1@wbh_as1 smbshare]$ exp wb/wb
Export: Release 9.2.0.1.0 - Production on Thu Dec 23 00:02:44 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Enter array fetch buffer size: 4096 >
Export file: expdat.dmp > wb
(2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) . exporting pre-schema procedural objects and actions . exporting foreign function library names for user WB . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user WB About to export WB's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions
Tables same column names but diffrenet index structures and traget one to be partitioned hence only want to import the content Each table on source datbaase hascolumn seq number and only want to extract the last few months of data.
TABLES:table1,table2... DUMPFILE=dump_dir CONTENT=data_only QUERY= table1:"WHERE seq_num >100 "want to use expdp but not sure about how to ensure all tables have the WHERE seq_num >100 condition, if leave table1: out and just have QUERY= "WHERE seq_num >100 " will this condition be applied to all tables which is what we want.
I'm assuming also can use impdp CONTENT=data_only?
I am considering all of the capabilities and benefits of using Data Pump for exporting and importing extremely large data files. Would like to know if importing to tape is possible? If so, would the data be accessible if needed later?