Home » RDBMS Server » Server Utilities » how to export statistics of table without table structure in oracle 9i (Export: Release 9.2.0.1.0 - Production )
how to export statistics of table without table structure in oracle 9i [message #406879] Sun, 07 June 2009 01:18 Go to next message
upendra4321
Messages: 8
Registered: March 2009
Location: sion
Junior Member

i want to export statistics of table without the table structure

Re: how to export statistics of table without table structure in oracle 9i [message #406884 is a reply to message #406879] Sun, 07 June 2009 01:23 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

http://www.dbasupport.com/oracle/ora9i/CBO4_6.shtml
Re: how to export statistics of table without table structure in oracle 9i [message #406885 is a reply to message #406879] Sun, 07 June 2009 01:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OK, proceed to do so.
exp help=yes
Re: how to export statistics of table without table structure in oracle 9i [message #406889 is a reply to message #406885] Sun, 07 June 2009 02:39 Go to previous messageGo to next message
upendra4321
Messages: 8
Registered: March 2009
Location: sion
Junior Member

exp help=y

if statistics=y and rows=n gives table structure also
Re: how to export statistics of table without table structure in oracle 9i [message #406890 is a reply to message #406889] Sun, 07 June 2009 02:44 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Okay..

If you want to use export utility then..

1. Try to export statistics with rows=n

2. Try to get all the statistics using show=y from import command.

Thanks
Re: how to export statistics of table without table structure in oracle 9i [message #406895 is a reply to message #406879] Sun, 07 June 2009 02:57 Go to previous messageGo to next message
upendra4321
Messages: 8
Registered: March 2009
Location: sion
Junior Member

AS i imported the table in database show=y its also imported the structure

C:\Documents and Settings\cmah5277>imp file='d:\a.dmp' tables=pol_gen show=y

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by INS, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
. importing INS's objects into SYSTEM
"CREATE TABLE "POL_GEN" ("POL_COUNT" VARCHAR2(500), "POL_DATE" DATE, "PREMIU"
"M" NUMBER(10, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(IN"
"ITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "INS" LOGGING NOCOMPR"
"ESS"
"GRANT SELECT ON "POL_GEN" TO "RDGEN""
"GRANT SELECT ON "POL_GEN" TO "INSSELECT""
Import terminated successfully with warnings.
Re: how to export statistics of table without table structure in oracle 9i [message #406896 is a reply to message #406895] Sun, 07 June 2009 03:07 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
upendra4321
AS i imported the table in database show=y its also imported the structure
Are you sure?

User MIKE doesn't have the "test" table; SCOTT does:
SQL> connect mike/lion
Connected.

SQL> desc test
ERROR:
ORA-04043: object test does not exist


SQL> connect scott/tiger
Connected.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL                                                VARCHAR2(1000)
SQL>

Exporting SCOTT's table:
SQL> $exp scott/tiger tables=test file=test.dmp

Export: Release 10.2.0.1.0 - Production on Ned Lip 7 10:02:23 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set

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

SQL>

"Importing" it into MIKE using the SHOW=Y option:
SQL> $imp mike/lion file=test.dmp show=y

Import: Release 10.2.0.1.0 - Production on Ned Lip 7 10:03:04 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into MIKE
Import terminated successfully without warnings.

SQL>

Checking whether MIKE's schema now contains "test" table (according to your statement, it does):
SQL> connect mike/lion
Connected.

SQL> desc test
ERROR:
ORA-04043: object test does not exist


SQL>

It appears that you were wrong.
Re: how to export statistics of table without table structure in oracle 9i [message #406905 is a reply to message #406896] Sun, 07 June 2009 08:15 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Addition;

C:\>imp help=y

Import: Release 10.2.0.4.0 - Production on Mon Jun 8 18:36:26 2009

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



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

     Example: IMP SCOTT/TIGER

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

     Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
               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         import entire file (N)
BUFFER   size of data buffer         FROMUSER     list of owner usernames
FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
[B][U]SHOW     just list file contents (N)[/U][/B] TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
DESTROY                overwrite tablespace data file (N)
INDEXFILE              write table/index info to specified file
SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
FEEDBACK               display progress every x rows(0)
TOID_NOVALIDATE        skip validation of specified type ids
FILESIZE               maximum size of each dump file
STATISTICS             import precomputed statistics (always)
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
COMPILE                compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION  import streams general metadata (Y)
STREAMS_INSTANTIATION  import streams instantiation metadata (N)

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set

Import terminated successfully without warnings.

C:\>


Previous Topic: load into oracle 7.3
Next Topic: sqlldr help needed
Goto Forum:
  


Current Time: Fri Mar 29 08:29:33 CDT 2024