home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #20 / NN_1992_20.iso / spool / comp / database / oracle / 1520 < prev    next >
Encoding:
Internet Message Format  |  1992-09-08  |  5.6 KB
  1. Path: sparky!uunet!munnari.oz.au!mel.dit.csiro.au!yarra!cmutual.com.au!aaj
  2. From: aaj@cmutual.com.au (Tony Jambu)
  3. Newsgroups: comp.databases.oracle
  4. Subject: Re: how to compress database and move indexes to different tablespace
  5. Message-ID: <1992Sep8.023145.3884@cmutual.com.au>
  6. Date: 8 Sep 92 02:31:45 GMT
  7. Organization: Colonial Mutual Life Australia
  8. Lines: 112
  9.  
  10. Path: cmutual.com.au!aaj
  11. Newsgroups: comp.databases.oracle
  12. Distribution: world
  13. Followup-To: 
  14. References: <1992Sep2.100626.1@bbs.mdcbbs.com>
  15. <1992Sep4.090637.346@hhcs.gov.au>
  16. From: aaj@cmutual.com.au (Tony Jambu)
  17. Organization: Colonial Mutual Life Australia 
  18. Subject: how to compress database and move indexes to different t
  19.  
  20. In article <1992Sep4.090637.346@hhcs.gov.au>, pihlab@hhcs.gov.au
  21. writes:
  22. > > However, how to I move the corrisponding indexes? I have tried using the
  23. > > "show" option of IMP to generate a file, but this file is in such a poor
  24. > > format I cannot use it without EXTENSIVE editing. Does anybody have a
  25. > > utility that will take the file generated by the "show" option and make it
  26. > > usable? The format I get is ONLY 80 column records and EVERY line is
  27. > > enclosed in double quotes.
  28. > 
  29. > I don't have any formatter for you.
  30. > 
  31. > There is an INDEXES=file (can't remember the exact name) parameter for IMPORT
  32. > which pulls out the TABLE and INDEX definitions to a flat file automatically.
  33. > It is mentioned in the V5 to V6 upgrade documentation because you had to
  34. check
  35. > that INDEX names were unique across all objects for each user.  If you can
  36. get
  37. > that working then you need only add the TABLESPACE component.
  38. > 
  39. > You but you might consider creating a script of CREATE INDEX statements
  40. > directly from the INDEX definitions in the database.
  41. > 
  42.  
  43.  
  44. The exact syntax is
  45.     imp user/password INDEXFILE=FILE file=...
  46.  
  47. Problem with this approach is that it does not include a number of objects.
  48. For example views.
  49.  
  50. Now, I have this utility that was written by a colllegue and is called 'impl2sql'.
  51. It is very useful as it formats the output from imp's SHOW in a very useable
  52. format.  To use it, 
  53.  
  54. 1. Cut starting from the 'begin' line to the 'end' line into a file, FILE
  55. 2. Decode it useing 'uudecode FILE'
  56. 3. uncompress impl2sql.Z
  57.  
  58. To use it have a read of the description in the head of the file.
  59.  
  60.  
  61. --------------------------------- ATTACHMENT ---------------------------------
  62. begin 777 impl2sql.Z
  63. M'YV0(T*\$)/&S8LY:!2,4 C"29 F11@F2-,&#AL9<^*P =$"!! 4(U(,>>/&
  64. M3ADY=.: >"(GS!@V94!0A/,&)0@4;MZ D//FSIP4(-BDF4,'!!V=4Z(P 3%G
  65. MC)PT<.@PG)+%R1,H4Y),80BBJU>$.F16!/%B9\\Y/=R ,),&9H\R>."0"4/'
  66. M!9FQ"'OVR -"A@\3,4#P$6L1HT80/@@+)>HB(QNN7KN. 4N8:1ZB9=J\:!/&
  67. M39@S)\WZ3,L4C5Z^;-W"E4O7[E@^)D H3#"QXD7'B!4/K>N8(9$B4X9(20*%
  68. M2I(G3B!'-I)&#E$0;\R ",.�L03LO0C1DFZ)LS:]O&-,.SC5$T,=_4H0-G
  69. M/73I+%W"E&C#!0P7,V 0KEET?=LT=.3A0E=5S!$3'6C0-9U*",8TDWMFU'3>
  70. M4$PY!954(X"0P!UHI#$&&B#<T=9&=SQ%!W=,U0%@&&+ !$)23*@ !1MUJ-34
  71. M4U$9I1-19*A7ET(24=&A2OX)%:!,*L$1!DH>UL'&DFSP56,99C@9(GIJ-0C"
  72. M:OR%YZ(<5)XTAT1'@2!"A&"F<88;(AC%(DQS*#E&&2IU1@8(G/&5H$D@U'%&
  73. ME"# @<9E'E+'U%$M@2:1DBVU4<:)SIVG8)X@B''@&SH1!!Z:(.2AGAR68=;&
  74. M@"!0H1-H16DY%QUA2%10F=W-P5EUH<UQV8EM7,>6<T7)48=:\;WD8$5=%@D@
  75. M7R(B2"9Z9O(TFEIYW;%7FV]$E09)=;IQ)QTMP7%>>NNUEZI.W=U1TQH%G2%1
  76. M:F60*L6O$ZK$[GD\^0GB3+<=5B8</)%1QYS?,J61135*=..%TVF[)1D !@SF
  77. M'$Z.FV+#GM8!*JLMTBDGG0F3L>B@<Q2Z$5$U?183&70V1Z<+$DET!9;Q@I"@
  78. M2I:6H5:/;I1QG9T!XW9PCKZZH=(32]"KWAD)99B JG2Q&(:!UU6,76??WJR3
  79. MEC-U&5W +M%1AZ'.SL'"HF":<=*;R ((H@AJY@3F7MB]T48; $X+G;78,O4H
  80. MJ4*>W+3!ICEY9TYW5.J@6A6#NJK3!H:H=J>?!EKV26#>27*B92RZ9!B.0CH'
  81. MRQE:105P( 3A!!$@7!&$%$XDX<016RG==WB\!KID45MK^7.O98SQG1M#<1QG
  82. M[VD82M13;IS!8((8:IAS&=MF.EY;D$(/G7L2:HFF>;JS:M/6W<%XZ':.NO&C
  83. M[,P*E;-*),GDAKC3@5EUI3RM87.?WBJX!N%JB3!'&&9K$TU>];D@,<M OE,8
  84. MHW G'=U9*$=J>$-!5*(^CG'H) <Z(*MP9;/F;>AI#7D"%>CW!ONI14&Q F!,
  85. M!FB^CH&@1QSK#D$8*+E'!2A=^ .=<KSRA)Q-*'D@2(+,PL G?&6F@]9+X)SD
  86. MD+,[?:@SH&&03J84J61U*$OHD8B_QL(NO@TI1""48</ 9[N"7&<..A$B_ HR
  87. M'1 , 3E.*,(01FBP#1ZQA?SJ'?2L)P:^=$<*16@"1UK0D2$@P71'* ((JC"%
  88. M(DAAD!VIH/L"YJ@Y_ \TH-.0D"B4%\$9;F$ LA[/M$3%$R3I:7,PEQSNM$8W
  89. MD.H)#9*#B* FD3&XIP[>TIYX*J4GGO$,50&CHHYV J\&F<= C-I.E'2HM)$(
  90. MC5MA(. 0^923HN3A48:[WXDP0P9F=B4!(YE;!U5B1?JYQ'XIF:0Q'5>=3_I)
  91. M*&R!GC<3X 2=<,8-7]N(DD#3*6QVA"@CF@X;!!6&>5I!3-<2VGM6TA)A[>=[
  92. ML904%A7$L..E00SKL9X5)6*'^MPG/_BY215:AP6@["DFHLMFENATHFYV99,J
  93. MF5RDRM0@B<"E=^MYD\Y".$(M<<DF\P*0@=A@ADP&H0I40,(3I" 1(A Q#7>B
  94. MPH"$0!<0)2 &.<A!#!C2A#<P+)YWTL%L3.4&OBB!<Q@%4H:\TH*8G. %7G"!
  95. M7.=*U[K:]:YXS:M>]\I7%Y#@!2'8PPDXXM8YO$ $?]W!"P;;5A"<H ^#Y8("
  96. M#'2GQK[5"UL(0@NT$(86Z*$++R!#9!40&<L:U@M<&%#6F 1$87(!!5OP @BZ
  97. M$-O9JH +0%'!7P$I2$(6\I"O4R0C'0E)R;[1"7&<(PBX$ /%CK:T;GU!:I<[
  98. MH#F@"PYP2%=HG\O6Z'KAKZ)=+FF[ZUBX]O6\Z$VO>NGZU\ RMK"'3>QB">M8
  99. MR(H7NHX];5=$\-HC2,%T(SQN<JF VQ?L0++,G:]D\6M*N.ZWO_]UP@@!.84G
  100. M5$$*0RA"@0^L@ 1SMRNF=;"9( S@()ZN"%C8,()CH.#Q@AB^LATQ"OQ;XM\P
  101. MH0BC4W&'6?QA^C8XQOR=<81'& 0FC$X*.O;P?<G[XP<+N<15@ (1@I#C%!AX
  102. MQ2UFL'YE3&,)OZ@(-YYCDGF\Y!?G5\1![O((7==(*1#8RAQ6\H*9O.4T#QD$
  103. M1!!"$,:<93JC^;7"*0*5%2F%)S"!"7H>PA+XW.,0 QG0@!QTJ8(@A!LSNLP^
  104. MKC.D!3VZ%U4%.5EHPJ7G;.8FRSC0DH9"%2J=A"&,VL69_C,*4-WI1D:A"D5P
  105. M0H9?K659TUJXK;NUAN&,Y4;#V,F_-O%O4DSL'?>YU)J>=:1K_6DGA)K7?GZT
  106. MM#FM2"LDH0A7P#:T95WD(T^ZTL"!0A!VW6PYP]K13BXW<8>+Y':3F=2QUO8;
  107. MFP 1+R-GN?9^=JQO4A;)HN %9S#VF:_SVMC"H 4YZ$*!&>[LA&,ZQ"FP<@HD
  108. M:V6+XSO$(+"R>#L^V,%(EK(^CN\+!.MC)[SWS/Q5BYE>,-\0QURQSO6Q?3\>
  109. M7<2N_.4G<'FL8[Y?F@/=L#<W<,W=NO-W]_RO++>LT&W.!9F+P.A#KSK.EUY?
  110. MA3?X.E>G^-4][O0S9_P%&U< R2\.7Z"   IS- (@BX#VKD@6[E20>Q'H3O9>
  111. MOX@*2PU"(@\NWBD _K^)%._!^^YGH%"!TC>>@KHS7'?)/A[=DE\WW[U^6A2\
  112. )U@4%]KRS!ZL 
  113.  
  114. end
  115.  
  116.  
  117. -- 
  118. -----------------------------------------------------------------------------
  119.  _____       ________ / ____ |Tony Jambu, Database Administrator
  120.   /_  __       /_ __ /       |Colonial Mutual Life Australia. (ACN 004021809)
  121.  /(_)/ ((_/ \_/(///(/_)/_(   |EMAIL:  TJambu@cmutual.com.au
  122.