1. 首页
  2. IT资讯

oracle迁移表到新的表空间-包含表空间、索引、表分区

1.创建新的表空间

1. CREATETABLESPACEbayonet

2. DATAFILE’/oradata/db/bayonet_01.DBF’

3. SIZE2G

4. AUTOEXTENDONNEXT1GMAXSIZEUNLIMITEDLOGGING

5. EXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;

2.修改用户默认表空间

1. ALTERUSERdbuserDEFAULTtablespacebayonet;

3.创建迁移表到新的表空间SQL执行语句

1. ==普通表:

2. SELECT’altertable’||table_name||’movetablespacebayonet;’

3. FROMuser_all_tables

4. WHEREtablespace_name=’USERS’;

5.

6. –无子分区

7. SELECT’altertable’||t1.TABLE_NAME||’movepartition’||t2.PARTITION_NAME||’tablespacebayonet;’

8. FROMuser_all_tablest1,USER_TAB_PARTITIONSt2

9. WHEREt1.TABLE_NAME=t2.TABLE_NAMEANDt2.tablespace_name=’USERS’andt2.subpartition_count>0;

10.

11.

12. –子分区

13. SELECT’altertable’||t1.TABLE_NAME||’movesubpartition’||t2.subpartition_name||’tablespacebayonet;’

14. FROMuser_all_tablest1,all_tab_subpartitionst2

15. WHEREt1.TABLE_NAME=t2.TABLE_NAMEANDt2.tablespace_name=’USERS’;

16.

17. SELECT’altertable’||t1.TABLE_NAME||’modifydefaultATTRIBUTESFORpartition’||t2.PARTITION_NAME||’tablespacebayonet;’

18. FROMuser_all_tablest1,USER_TAB_PARTITIONSt2

19. WHEREt1.TABLE_NAME=t2.TABLE_NAMEANDt2.tablespace_name=’USERS’;

4.创建索引重建的SQL执行语句

1. select’altertable’||table_name||’disableprimarykey;’fromuser_constraintswhereconstraint_type=’P’andR_OWNER=’DBUSER’;

2.

3. SELECT’alterindex’||index_name||’rebuildtablespacebayonet;’

4. FROMuser_indexes

5. WHEREindex_type=’NORMAL’ANDDROPPED=’NO’AND

6. TABLE_NAMEIN(

7. SELECTTABLE_NAME

8. FROMuser_all_tables

9. WHEREtablespace_name=’users’

10. );

11.

12. select’altertable’||table_name||’enableprimarykey;’fromuser_constraints

13. whereconstraint_type=’P’andR_OWNER=’DBUSER’;

5.创建表分区

1. –Createtable

2. createtableGPS_POSITION

3. (

4. IDNVARCHAR2(32)notnull,

5. CARDNONVARCHAR2(32),

6. GPSTIMEDATE,

7. LONGITUDENUMBER,

8. LATITUDENUMBER,

9. E_WNVARCHAR2(8),

10. N_SNVARCHAR2(8),

11. SPEEDNVARCHAR2(16),

12. GPSSPEEDNVARCHAR2(32),

13. DIRECTIONNVARCHAR2(16),

14. MILEAGENVARCHAR2(16),

15. MILEAGE1NVARCHAR2(16),

16. HIGHTNVARCHAR2(16),

17. TEMPERATURENVARCHAR2(16),

18. OILNVARCHAR2(16),

19. PASSENGERNVARCHAR2(16),

20. S0NVARCHAR2(2),

21. S1NVARCHAR2(2),

22. S2NVARCHAR2(2),

23. S3NVARCHAR2(2),

24. S4NVARCHAR2(2),

25. A0NVARCHAR2(2),

26. A1NVARCHAR2(2),

27. A2NVARCHAR2(2),

28. A3NVARCHAR2(2),

29. A4NVARCHAR2(2),

30. RUNTIMENVARCHAR2(32),

31. A_VNVARCHAR2(8),

32. CREATEDATEDATEdefaultsysdate,

33. GPSIDNUMBER,

34. NUMNUMBER,

35. WAYNUMBER,

36. TYPENUMBER,

37. WAYSENTDATENUMBER,

38. RECEIVEDDATENUMBER,

39. B0NVARCHAR2(2),

40. B1NVARCHAR2(2),

41. B2NVARCHAR2(2),

42. B3NVARCHAR2(2),

43. B4NVARCHAR2(2),

44. B5NVARCHAR2(2),

45. B6NVARCHAR2(2),

46. CITYIDNVARCHAR2(20),

47. DATASTATENUMBER,

48. GPSLOCKNVARCHAR2(2),

49. GPSPOWERNVARCHAR2(2),

50. ADDRESSNVARCHAR2(200)

51. )

52. partitionbyrange(GPSTIME)

53. subpartitionbylist(CITYID)

54. (

55. partitionP201010valueslessthan(TO_DATE(‘2010-10-0100:00:00′,’SYYYY-MM-DDHH24:MI:SS’,’NLS_CALENDAR=GREGORIAN’))

56. tablespacebayonet

57. pctfree10

58. initrans1

59. maxtrans255

60. storage

61. (

62. initial2

63. minextents1

64. maxextentsunlimited

65. )

66. (

67. subpartitionP201010_P600000values(‘600000’)tablespaceUSERS,

68. subpartitionP201010_P600001values(‘600001’)tablespaceUSERS,

69. subpartitionP201010_P600002values(‘600002’)tablespaceUSERS,

70. subpartitionP201010_P600003values(‘600003’)tablespaceUSERS,

71. subpartitionP201010_P600004values(‘600004’)tablespaceUSERS,

72. subpartitionP201010_P600005values(‘600005’)tablespaceUSERS,

73. subpartitionP201010_P600006values(‘600006’)tablespaceUSERS,

74. subpartitionP201010_P600007values(‘600007’)tablespaceUSERS,

75. subpartitionP201010_P600008values(‘600008’)tablespaceUSERS,

76. subpartitionP201010_P600009values(‘600009’)tablespaceUSERS,

77. subpartitionP201010_P600010values(‘600010’)tablespaceUSERS,

78. subpartitionP201010_P600011values(‘600011’)tablespaceUSERS,

79. subpartitionP201010_P600012values(‘600012’)tablespaceUSERS,

80. subpartitionP201010_P600013values(‘600013’)tablespaceUSERS,

81. subpartitionP201010_P600014values(‘600014’)tablespaceUSERS,

82. subpartitionP201010_P600015values(‘600015’)tablespaceUSERS,

83. subpartitionP201010_P600016values(‘600016’)tablespaceUSERS,

84. subpartitionP201010_P600017values(‘600017’)tablespaceUSERS,

85. subpartitionP201010_P600018values(‘600018’)tablespaceUSERS,

86. subpartitionP201010_P600019values(‘600019’)tablespaceUSERS,

87. subpartitionP201010_P600020values(‘600020’)tablespaceUSERS

88. ),

89. partitionP201112valueslessthan(TO_DATE(‘2011-12-0100:00:00′,’SYYYY-MM-DDHH24:MI:SS’,’NLS_CALENDAR=GREGORIAN’))

90. tablespacebayonet

91. pctfree10

92. initrans1

93. maxtrans255

94. (

95. subpartitionP201112_P600000values(‘600000’)tablespaceUSERS,

96. subpartitionP201112_P600001values(‘600001’)tablespaceUSERS,

97. subpartitionP201112_P600002values(‘600002’)tablespaceUSERS,

98. subpartitionP201112_P600003values(‘600003’)tablespaceUSERS,

99. subpartitionP201112_P600004values(‘600004’)tablespaceUSERS,

100. subpartitionP201112_P600005values(‘600005’)tablespaceUSERS,

101. subpartitionP201112_P600006values(‘600006’)tablespaceUSERS,

102. subpartitionP201112_P600007values(‘600007’)tablespaceUSERS,

103. subpartitionP201112_P600008values(‘600008’)tablespaceUSERS,

104. subpartitionP201112_P600009values(‘600009’)tablespaceUSERS,

105. subpartitionP201112_P600010values(‘600010’)tablespaceUSERS,

106. subpartitionP201112_P600011values(‘600011’)tablespaceUSERS,

107. subpartitionP201112_P600012values(‘600012’)tablespaceUSERS,

108. subpartitionP201112_P600013values(‘600013’)tablespaceUSERS,

109. subpartitionP201112_P600014values(‘600014’)tablespaceUSERS,

110. subpartitionP201112_P600015values(‘600015’)tablespaceUSERS,

111. subpartitionP201112_P600016values(‘600016’)tablespaceUSERS,

112. subpartitionP201112_P600017values(‘600017’)tablespaceUSERS,

113. subpartitionP201112_P600018values(‘600018’)tablespaceUSERS,

114. subpartitionP201112_P600019values(‘600019’)tablespaceUSERS,

115. subpartitionP201112_P600020values(‘600020’)tablespaceUSERS,

116. subpartitionP201112_P600021values(‘600021’)tablespaceUSERS,

117. subpartitionP201112_P600022values(‘600022’)tablespaceUSERS,

118. subpartitionP201112_P600023values(‘600023’)tablespaceUSERS,

119. subpartitionP201112_PSUB_DEFvalues(default)tablespaceUSERS

120. ),

121. partitionP201201valueslessthan(TO_DATE(‘2012-01-0100:00:00′,’SYYYY-MM-DDHH24:MI:SS’,’NLS_CALENDAR=GREGORIAN’))

122. tablespacebayonet

123. pctfree10

124. initrans1

125. maxtrans255

126. (

127. subpartitionP201201_P600000values(‘600000’)tablespaceUSERS,

128. subpartitionP201201_P600001values(‘600001’)tablespaceUSERS,

129. subpartitionP201201_P600002values(‘600002’)tablespaceUSERS,

130. subpartitionP201201_P600003values(‘600003’)tablespaceUSERS,

131. subpartitionP201201_P600004values(‘600004’)tablespaceUSERS,

132. subpartitionP201201_P600005values(‘600005’)tablespaceUSERS,

133. subpartitionP201201_P600006values(‘600006’)tablespaceUSERS,

134. subpartitionP201201_P600007values(‘600007’)tablespaceUSERS,

135. subpartitionP201201_P600008values(‘600008’)tablespaceUSERS,

136. subpartitionP201201_P600009values(‘600009’)tablespaceUSERS,

137. subpartitionP201201_P600010values(‘600010’)tablespaceUSERS,

138. subpartitionP201201_P600011values(‘600011’)tablespaceUSERS,

139. subpartitionP201201_P600012values(‘600012’)tablespaceUSERS,

140. subpartitionP201201_P600013values(‘600013’)tablespaceUSERS,

141. subpartitionP201201_P600014values(‘600014’)tablespaceUSERS,

142. subpartitionP201201_P600015values(‘600015’)tablespaceUSERS,

143. subpartitionP201201_P600016values(‘600016’)tablespaceUSERS,

144. subpartitionP201201_P600017values(‘600017’)tablespaceUSERS,

145. subpartitionP201201_P600018values(‘600018’)tablespaceUSERS,

146. subpartitionP201201_P600019values(‘600019’)tablespaceUSERS,

147. subpartitionP201201_P600020values(‘600020’)tablespaceUSERS,

148. subpartitionP201201_P600021values(‘600021’)tablespaceUSERS,

149. subpartitionP201201_P600022values(‘600022’)tablespaceUSERS,

150. subpartitionP201201_P600023values(‘600023’)tablespaceUSERS,

151. subpartitionP201201_PSUB_DEFvalues(default)tablespaceUSERS

152. )

153. );

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29578568/viewspace-2138084/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195190.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code