Home » RDBMS Server » Server Utilities » IOT table restoration (10.2.0.4)
IOT table restoration [message #533967] Fri, 02 December 2011 00:27 Go to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member
Hi,

I am trying to take exp / imp for Index organized table.

Can anyone guide me on it.

Thanks

Report message to a moderator

Re: IOT table restoration [message #533979 is a reply to message #533967] Fri, 02 December 2011 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no difference with any other table type.

Regards
Michel

Report message to a moderator

Re: IOT table restoration [message #533983 is a reply to message #533979] Fri, 02 December 2011 01:02 Go to previous messageGo to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member
Ok,

So how can i take export for table "S553_IDX$".
Any help appreciated.

Thanks in Advance.

Report message to a moderator

Re: IOT table restoration [message #533991 is a reply to message #533983] Fri, 02 December 2011 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
C:\>exp help=y

Export: Release 10.2.0.4.0 - Production on Ven. DÚc. 2 08:13:38 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export


Database Utilities

Regards
Michel

Report message to a moderator

Re: IOT table restoration [message #534023 is a reply to message #533991] Fri, 02 December 2011 04:23 Go to previous messageGo to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member
Hi Michel,

I am tried with all options. But still i am not able to take export for "S553_IDX$" table. Becz while taking export , it is giving error as table not exist.
Below is my export query:

$ exp / TABLES=Schema.S553_IDX$ file=S553_IDX.dmp log=S553_IDX.log

Export: Release 10.2.0.4.0 - Production on Fri Dec 2 00:39:40 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DEALER_CHANNEL
EXP-00011: schema.S553_IDX does not exist
Export terminated successfully with warnings.

Any hint for it.....

Thanks

Report message to a moderator

Re: IOT table restoration [message #534024 is a reply to message #534023] Fri, 02 December 2011 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Escape the $: exp / TABLES=Schema.S553_IDX\$ file=S553_IDX.dmp log=S553_IDX.log

Regards
Michel

Report message to a moderator

Re: IOT table restoration [message #541912 is a reply to message #534024] Fri, 03 February 2012 03:00 Go to previous messageGo to next message
sguinales
Messages: 6
Registered: February 2012
Location: España
Junior Member
I have the same problem but with thit didn't solve it,

1) table info:

table: S44_IDX$

SQL> desc DGC_USM.S44_IDX$
Name Null? Type
------------------------------------------------------------ -------- --------------------
GX NOT NULL NUMBER(38)
GY NOT NULL NUMBER(38)
MINX NOT NULL NUMBER(38)
MINY NOT NULL NUMBER(38)
MAXX NOT NULL NUMBER(38)
MAXY NOT NULL NUMBER(38)
SP_ID NOT NULL ROWID

SQL> select bytes,SEGMENT_NAME,TABLESPACE_NAME from dba_segments where OWNER='DGC_USM' and SEGMENT_NAME like '%S44%';

BYTES SEGMENT_NAME TABLESPACE_NAME
---------- --------------------------------------------------------------------------------- ------------------------------
458752 S44$_IX1 DGC_USMDATOS
196608 S44$_IX2 DGC_USMDATOS

table def:
CREATE TABLE "DGC_USM"."S44_IDX$"
( "GX" NUMBER(*,0),
"GY" NUMBER(*,0),
"MINX" NUMBER(*,0),
"MINY" NUMBER(*,0),
"MAXX" NUMBER(*,0),
"MAXY" NUMBER(*,0),
"SP_ID" ROWID,
CONSTRAINT "S44$_IX1" PRIMARY KEY ("GX", "GY", "MAXX", "MAXY", "MINX", "MINY",
"SP_ID") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 0 INITRANS 4 MAXTRANS 255 LOGGING
STORAGE(INITIAL 409600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DGC_USMDATOS"
PCTTHRESHOLD 5


2) Exporting

sigtst1// /oracled/SIGD >exp "'/ as sysdba'" TABLES=DGC_USM.S44_IDX\$ file=exp_S44.dmp log=exp_S44.log buffer=10000

Export: Release 9.2.0.7.0 - Production on Fri Feb 3 09:00:40 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8DEC character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DGC_USM
EXP-00011: DGC_USM.S44_IDX$ does not exist
Export terminated successfully with warnings.




info:
HP-UX sigtst1 B.11.23 U 9000/800 1252522213 unlimited-user license
Oracle 9.2.0.7.

Report message to a moderator

Re: IOT table restoration [message #541917 is a reply to message #541912] Fri, 03 February 2012 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How could it be possible that you have a table without segment? I think in the "desc" you have a view or a synonym.

Use SQL*Plus and post:
select name, instance_name from v$database, v$instance;
select owner, object_type from dba_objects where object_name='S44_IDX$' order by 1, 2;


Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

In addition:
- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.

Regards
Michel

Report message to a moderator

Re: IOT table restoration [message #541933 is a reply to message #541917] Fri, 03 February 2012 03:47 Go to previous messageGo to next message
sguinales
Messages: 6
Registered: February 2012
Location: España
Junior Member
Michel is a table:

SQL> select name, instance_name from v$database, v$instance;

NAME      INSTANCE_NAME
--------- ----------------
SIGD      SIGD


SQL> select owner, object_type from dba_objects where object_name='S44_IDX$' order by 1, 2;

OWNER                          OBJECT_TYPE
------------------------------ ------------------
DGC_USM                        TABLE

Report message to a moderator

Re: IOT table restoration [message #541936 is a reply to message #541933] Fri, 03 February 2012 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Can you add:
select owner, segment_type from dba_segments where segment_name='S44_IDX$' order by 1, 2;
select owner, iot_type, partitioned, temporary, nested 
from dba_tables 
where table_name='S44_IDX$' order by 1;


Regards
Michel

Report message to a moderator

Re: IOT table restoration [message #541938 is a reply to message #541936] Fri, 03 February 2012 04:08 Go to previous messageGo to next message
sguinales
Messages: 6
Registered: February 2012
Location: España
Junior Member
 
SQL> select owner, segment_type from dba_segments where segment_name='S44_IDX$' order by 1, 2;
select owner, iot_type, partitioned, temporary, nested 
from dba_tables 
where table_name='S44_IDX$' order by 1;

no rows selected

Elapsed: 00:00:00.15
11:07:15 sys@SIGD SQL> 11:07:15   2  11:07:15   3  
OWNER                          IOT_TYPE     PAR T NES
------------------------------ ------------ --- - ---
DGC_USM                        IOT          NO  N NO

Report message to a moderator

Re: IOT table restoration [message #541943 is a reply to message #541938] Fri, 03 February 2012 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Can you execute the following (as I did, with the owner of the table) and post the result:
SQL> sho user
USER is "MICHEL"
SQL> create table iot (id integer primary key, val varchar2(10)) organization index;

Table created.

SQL> host exp michel/michel file=t.dmp tables=(iot)

Export: Release 10.2.0.4.0 - Production on Ven. FÚvr. 3 11:22:37 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            IOT          0 rows exported
Export terminated successfully without warnings.

Regards
Michel

Report message to a moderator

Re: IOT table restoration [message #541946 is a reply to message #541943] Fri, 03 February 2012 04:47 Go to previous messageGo to next message
sguinales
Messages: 6
Registered: February 2012
Location: España
Junior Member
Michel,thanks, I create the test you told me and run ok, I suspect problem is with table name "S44_idx$" an the character "$"
Im trying to scape it with out success Sad :

exp "'/ as sysdba'" TABLES=DGC_USM.S44_IDX$ file=exp_S44.dmp log=exp_S44.log buffer=10000
EXP-00011: DGC_USM.S44_IDX$ does not exist

exp "'/ as sysdba'" TABLES=DGC_USM.S44_IDX\$ file=exp_S44.dmp log=exp_S44.log buffer=10000
EXP-00011: DGC_USM.S44_IDX$ does not exist

exp "'/ as sysdba'" TABLES="DGC_USM"."S44_IDX$" file=exp_S44.dmp log=exp_S44.log buffer=10000
EXP-00011: DGC_USM.S44_IDX$ does not exist

exp "'/ as sysdba'" TABLES="DGC_USM"."S44$_IDX\$" file=exp_S44.dmp log=exp_S44.log buffer=10000
ksh: _IDX: parameter not set



exp "'/ as sysdba'" TABLES=(DGC_USM.S44_IDX\$) file=/lv_tmp/exp_S44.dmp log=/lv_tmp/exp_S44.log buffer=10000
ksh: syntax error: `(' unexpected

exp "'/ as sysdba'" TABLES=(DGC_USM.S44_IDX$) file=/lv_tmp/exp_S44.dmp log=/lv_tmp/exp_S44.log buffer=10000
ksh: syntax error: `(' unexpected


how i can solve this?

Report message to a moderator

Re: IOT table restoration [message #541948 is a reply to message #541946] Fri, 03 February 2012 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Do NOT use SYSDBA
2/ What is your OS?
3/ You have to escape parenthesis as well as $ sign
4/ Rename the table and try to do the export
5/ Post what you do.

Regards
Michel

Report message to a moderator

Re: IOT table restoration [message #541950 is a reply to message #541948] Fri, 03 February 2012 05:15 Go to previous messageGo to next message
sguinales
Messages: 6
Registered: February 2012
Location: España
Junior Member
1)
OK, im going to use system

2)
sigtst1// /oracled/SIGD >uname -a
HP-UX sigtst1 B.11.23 U 9000/800 1252522213 unlimited-user license

3) i did it, same result Sad
exp userid=system TABLES=\(DGC_USM.S44_IDX\$\) file=/lv_tmp/exp_S44.dmp log=/lv_tmp/exp_S44.log

Current user changed to DGC_USM
EXP-00011: DGC_USM.S44_IDX$ does not exist
Export terminated successfully with warnings.


4)
create table DGC_USM.S44_ID as (select * from DGC_USM.S44_IDX$);
exp "'/ as sysdba'" TABLES=DGC_USM.S44_ID file=exp_S44ID.dmp log=exp_S44ID.log buffer=10000
. . exporting table                         S44_ID        984 rows exported


I think i have two options, is a db migration:
a)rename al "$" iot tables, exp/imp, rename again with $
b)Phsisycal backup and copy al dabase files and in remote rename paths




Report message to a moderator

Re: IOT table restoration [message #541956 is a reply to message #541950] Fri, 03 February 2012 05:38 Go to previous messageGo to next message
sguinales
Messages: 6
Registered: February 2012
Location: España
Junior Member
finally Option A, new system is not binary compatible, imposible to do with phsisycal backup.

Report message to a moderator

Re: IOT table restoration [message #541961 is a reply to message #541956] Fri, 03 February 2012 06:06 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
'$' should not have any impact for Oracle as it has no more meaning than other any character (but "):
SQL> create table S44_IDX$ (id integer primary key, val varchar2(10)) organization index;

Table created.

SQL> host exp michel/michel file=t.dmp tables=(S44_IDX$)

Export: Release 10.2.0.4.0 - Production on Ven. FÚvr. 3 13:05:32 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                       S44_IDX$          0 rows exported
Export terminated successfully without warnings.


Can you try to do it inside SQL*Plus (using host command) as I did it.

Regards
Michel

Report message to a moderator

Previous Topic: how to exclude streams
Next Topic: EXPDP: how exclude table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 16:14:50 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.