Server Utilities :: How To Take Exp / Imp For Index Organized Table
Dec 2, 2011I am trying to take exp / imp for Index organized table.
View 16 Replies
ADVERTISEMENT
PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?
Apr 24, 2013I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:
CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;
Recreate Steps:
1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data
[code]...
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
Export/Import/SQL Loader :: Impdp Index-organized Table And Temp Usage?
Aug 1, 2012Env: RHEL 5.8 RAC 11.2.0.2
I'm currently moving an IOT from one database to another using expdp/imdp. The IOT is non-partitioned and about 100GB in size containing ~1,1 billion rows.
The dumpfile contains nothing else but the IOT. I'm importing with no special parameters, no pre-created IOT, just ordinary dumpfile import. (impdp username/password dumpfile=impdp:iot.dmp nologfile=y )
During import I got unable to extend TEMP errors from impdp.
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
I had to add 2 additional files to my Temp tablespace (total 96GB of temp) before the import could finish off.
Is this temp usage to be expected when importing IOT's ?
Server Utilities :: Export Data Ordered And Organized
Nov 29, 2011is any way to export an Oracle database organized in manner that, both tables and constraints would be exported in the correct order.An easy sample:
- An database with 2 tables, with constraints between them. Table 1 has a FK on Table 2.
Is it possible to export both structure and data regarding the constraints, resulting in an script that makes it possibly to import it in a way that would not give me problems about constraints?
Server Utilities :: Processing Object Type SCHEMA_EXPORT / TABLE / INDEX
Apr 28, 2011I am importing the dump.All the tables are getting imported but when it comes to the part as shown in the error it gets struck there and nothing is showed after.
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
The dumps for which I am doing the import is huge. Its around 46 GB.
select
sl.sid,
sl.serial#,
sl.sofar,
sl.totalwork
from
v$session_longops sl,
v$session s,
dba_datapump_sessions d
where
s.saddr = d.saddr
and s.sid = sl.sid
and s.serial# = sl.serial#
order by start_time;
It showing the lock for two sid's. Should I kill the process and import the dump again? I used below command to import dums
impdp sys/*****@dbname directory=dump_dir dumpfile=prod_dump_%U_200311 SCHEMAS=int_aaa int_bbb int_ccc int_ddd int_eee int_fff
int_ggg int_hhh int_iii int_jjj int_kkk int_lll
Server Utilities :: Datapump And Index Building
Jan 13, 2012Currently 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.
Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?
Feb 7, 2012We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
Server Administration :: Index And Table Rebuild Method
Jan 17, 2012The best way to rebuild index and table and also give the reason as why we need to use this method?
View 10 Replies View RelatedServer Administration :: How To Find Which Table Or Index Needs To Be Reorg
Apr 21, 2009How to find which table or Index needs to be reorg..??
IS there any Query!!!.
Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table
Jul 11, 2013I 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]....
PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full
Oct 5, 2013Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
Performance Tuning :: Local Index Versus Global Index On Partitioned Table
Jun 28, 2011I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies View RelatedServer Administration :: Possible To Run SHRINK SPACE Against Table With Function Based Index
Jun 27, 2013It is not possible to run SHRINK SPACE against a table with a function based index. This is documented, and I've tested on the current release. I've reverse engineered it a bit, and the issue is in fact that you cannot SHRINK SPACE if there is an index on a virtual column:SQL> SQL> create table t1(c1 number, c2 as (c1*2)) segment creation immediate;
Table created.
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t1 shrink space;
Table altered.
SQL> create index i1 on t1(c2);
Index created.
SQL> alter table t1 shrink space;
alter table t1 shrink space
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object.
Server Utilities :: Import A Table With Table Already Present With New Columns
Mar 31, 2010I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.
TABLE_EXISTS_ACTION=TRUNCATE
The new table
SQL> desc "TESTINVENTORY"."TTRANSACTION"
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TRANSACTIONIDNOT NULL CHAR(26)
BRANCHCODE NOT NULL CHAR(3)
EXTERNALSYSTEM NOT NULL CHAR(3)
EXTRACTSYSTEM NOT NULL CHAR(3)
OWNERBRANCHCODE NOT NULL CHAR(3)
TRADEREFERENCE NOT NULL CHAR(20)
[code]...
It giving error while doing an import.
Server Administration :: Convert Global Index To Local Index
Jun 23, 2011I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?
Apr 8, 2013splitting a table partition without making its primary key index ar any other indexes unusable.
I think it is possible to do so 10g onwards.
DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP
Performance Tuning :: Force Index If Table Not Using Index?
Aug 9, 2013How to force an index if the table not using the index?
View 10 Replies View RelatedServer Utilities :: Import Table With Different Name
Nov 29, 2006We have a table in Oracle9i database with around 14 million records and we would like to import that table into 10g database with similar structure. We have exported the table from 9i database and would like to import the table into 10g database within same schema name with different table name as we already have the table with same name in 10g database in same schema. Is it possible to import a table with different table name?
We have a way around to import the table into 10g database in another schema and then push the data into our main table but want to know whether the above requirement is possible.
Server Utilities :: How To Exclude Table
Feb 6, 2012I tried to export a schema excluding some table, but expdp exit with this error:
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression
The command that I use is:
expdp system/password@ORADB directory=EXPORT_ORA_DIR schemas=maxdb logfile=maxdb.log
dumpfile=maxdb.dump EXCLUDE=TABLE:"IN ('max_table_1','max_table_2')";
Where I made a mistake?
Server Utilities :: Importing Table From 9i To 10g
May 31, 2012I want to import my oracle 9i dump file into oracle 10g version. What should i do ???
View 2 Replies View RelatedServer Utilities :: How To Get Proper Value In External Table
May 3, 2012getting proper value from the file in external table.
How can I get the whole status in STATUS column like completed , Inprogress, incompleted.
Right now, if I gave position like (38:9) full status doesn't show. if I give (38:11) then '|1' is adding in status from the flat file.
BATCH_NO FILE_DATEEMP_ID COMPANY_ID TRANSACTIN_ID FILE_NAME STATUS DOC_NO
10000104252012100001***4252012**1:35:57***D100001***04252012***10:35:57***Diverified
[Code].....
Server Utilities :: Exporting Table Structure From A Particular DB?
Nov 9, 2010know 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.
Server Utilities :: Dump Table From One To Other Schema
Mar 4, 2010i need to dump the table from A schema to B schema with different table name.
Suppose i have TABLE A IN "A" SCHEMA i need to dump the table with DATA+sTRUCTURE in " B"SCHEMA WITH TABLE NAME AS B.
Server Utilities :: Migrate Table From 10g Xe To Oracle 9i?
Dec 1, 2011how to migrate a table from 10g xe database to a oracle9i database.Both the database are stand alone and we cannot create a dblink.
View 2 Replies View RelatedServer Utilities :: Import A Table From Prod To Dev
Jan 26, 2011I am getting the below error when I import a table from Prod to Dev. I understand this error will be occured if length of the datatype is low. First I got the error when the datatype(length) which is 25 for the column PASSWORD column.Then I increased the length of this column to 45, then it was imported successfully.
why am facing the error when the datatye and length for this table is same in prod and dev? What are the possible ways to import the data without increasing the PASSWORD column length?
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "ANEES"."SALSA_WEB_ACCESS"."PASSWORD" (actual: 28, maximum: 25)
[code]....
Server Utilities :: Skip One Table While Import
May 12, 2010How to skip one table while import in traditional exp/imp not in DP.
View 4 Replies View RelatedServer Utilities :: Table Creation - SQL Loader?
Aug 2, 2010Check table creation script
CREATE TABLE "SCOTT"."TEST_USER"
("TX_SID" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"TX_FIRST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"TX_LAST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
[code]...
Here i'm loading data into these three tables through sql loader. Here is the control file
OPTIONS (SKIP=1,ROWS=5)
LOAD DATA
INFILE 'C:SQL LOADER DEMO estuser_data_lat.csv'
INTO TABLE TEST_USER
[code]...
Here are the two function which i'm calling from sql loader control file
CREATE OR REPLACE FUNCTION get_role_id(p_role_name VARCHAR2)
RETURN NUMBER IS
lv_role_id NUMBER;
BEGIN
[code]..
i'hv attached the testuser_data_lat.csv file, which is the data file.Command line
C:SQL LOADER DEMO>SQLLDR scott/sc CONTROL=rd_users_control.ctl
Now let me tell u what is happening Whem i'm running the above sqlldr, log is generating saying
Record 1: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")
Record 2: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")
[code]...
But when i remove
INTO TABLE TEST_ROLE(
TX_SID POSITION(1:3) CHAR,
ID_ROLE "get_role_id(:ROLE_NAME)" ,
TX_CREATED_BY CONSTANT "SYSTEM",
[code]...
from Control file, data is getting popupalated in TEST_USER and TEST_TITLE similarly if remove
INTO TABLE TEST_TITLE(
TX_SID POSITION(1:3) CHAR,
ID_TITLE "get_title_id(:TITLE_NAME)" ,
[code]...
from Control file, TEST_USER and TEST_ROLE is getting populated.
Here RD_ROLE_MASTER script
CREATE TABLE RD_ROLE_MASTER (
"ID_ROLE" NUMBER(38,0) NOT NULL ENABLE,
"TX_ROLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE
[code]...
Here is RD_TITLE_MASTER script
CREATE TABLE RD_TITLE_MASTER(
"ID_TITLE" NUMBER(38,0) NOT NULL ENABLE,
"TX_TITLE_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE);
Insert into RD_TITLE_MASTER (ID_TITLE,TX_TITLE_NAME) values (7,'RED_LOB_ESCALATION_L1'); what is the problem?
Server Utilities :: Load To A Table Through Sql*loader
Apr 6, 2010I Have one csv file.i want to load to a table trough sql*loader.but in table 3 column is there.but in the csv file some record hav one semicolumn in last filed like this
1234;"hogit";78887;89
4567;"rtef";12565;89
how can we load...
Server Utilities :: How To Change Table Name While Import
Jan 18, 2010I wanted to export a table "emp_production" from Production database then import it as "emp_datawarehouse" in Data warehouse database.Both tables has same structure. I have granted IMPORT FULL DATABASE & EXPORT FULL DATABASE privileges to both schema
I tired with the following syntax
$ Expdp u1/p1@h1[/email] tables= emp_production directory=test dumpfile=test1.dmp
$ Impdp u1/p2@h2[/email] directory=test dumpfile=test1.dmp remap_schema=u1.emp_production:u2.emp_datawarehouse
remap_tablespace=Example1:Example2
But I am getting the following error
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping.
Why this ? "emp_production" table has 150 million rows, every week importing this table then inserting into "emp_datawarehouse" table takes long time.
Server Utilities :: Sql Loader Value Needed From Another Table?
Aug 10, 2011I have a file having 10k of rows and I need to use *sql loader to insert the data into table. Below are the information.
SQL> desc EMPLOYEE
Name Type
EMP_ID NUMBER(10) -- PrimaryKey
EMP_NAME VARCHAR2(30)
DEPT_ID NUMBER(10) -- ForeignKey from DEPARTMENT
SQL> desc DEPARTMENT
Name Type
DEPT_ID NUMBER(10)
DEPT_NAME VARCHAR2(30)
myFile.txt
------------
1,Edward,Account
2,Andrew,Finance
3,Sam, IT
CONTROL FILE (SQLLOADER)
------------
load data
infile myFile.txt
append into table EMPLOYEE
FIELDS TERMINATED BY ','
(EMP_ID,
EMP_NAME,
DEPT_ID ) <--- ?? What should do in here
what should i do in this line because the value that i want is DEPT_ID but the file is giving the DEPART_NAME. If there any sql statement can be used in control file?