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

1.创建新的表空间 

1.  CREATE TABLESPACE bayonet   

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

3.  SIZE 2G   

4.  AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING  

5.  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;  

2.修改用户默认表空间 

1.  ALTER USER dbuser DEFAULT tablespace bayonet;  

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

1.  ==普通表：  

2.  SELECT 'alter table ' ||table_name || ' move tablespace bayonet;'     

3.  FROM user_all_tables     

4.  WHERE tablespace_name='USERS';  

5.    

6.  --无子分区  

7.  SELECT 'alter table ' ||t1.TABLE_NAME || ' move partition '|| t2.PARTITION_NAME ||' tablespace bayonet;'     

8.  FROM user_all_tables t1,USER_TAB_PARTITIONS t2      

9.  WHERE t1.TABLE_NAME=t2.TABLE_NAME AND t2.tablespace_name='USERS' and t2.subpartition_count>0;   

10.   

11.   

12. --子分区  

13. SELECT 'alter table ' ||t1.TABLE_NAME || ' move subpartition '|| t2.subpartition_name ||' tablespace bayonet;'     

14. FROM user_all_tables t1,all_tab_subpartitions t2      

15. WHERE t1.TABLE_NAME=t2.TABLE_NAME AND t2.tablespace_name='USERS';  

16.   

17. SELECT 'alter table ' ||t1.TABLE_NAME || ' modify default ATTRIBUTES FOR partition '|| t2.PARTITION_NAME ||' tablespace bayonet;'     

18. FROM user_all_tables t1,USER_TAB_PARTITIONS t2      

19. WHERE t1.TABLE_NAME=t2.TABLE_NAME AND t2.tablespace_name='USERS';   

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

1.  select 'alter table '||table_name||' disable primary key;' from user_constraints where constraint_type='P' and R_OWNER='DBUSER';  

2.    

3.  SELECT 'alter index ' ||index_name || ' rebuild  tablespace bayonet;'   

4.  FROM user_indexes    

5.  WHERE index_type='NORMAL' AND DROPPED='NO' AND    

6.  TABLE_NAME IN (  

7.      SELECT TABLE_NAME   

8.      FROM user_all_tables   

9.      WHERE tablespace_name='users'  

10. );  

11.   

12. select 'alter table '||table_name||' enable primary key;' from user_constraints   

13. where constraint_type='P' and R_OWNER='DBUSER';  

5.创建表分区 

1.  -- Create table  

2.  create table GPS_POSITION  

3.  (  

4.    ID           NVARCHAR2(32) not null,  

5.    CARDNO       NVARCHAR2(32),  

6.    GPSTIME      DATE,  

7.    LONGITUDE    NUMBER,  

8.    LATITUDE     NUMBER,  

9.    E_W          NVARCHAR2(8),  

10.   N_S          NVARCHAR2(8),  

11.   SPEED        NVARCHAR2(16),  

12.   GPSSPEED     NVARCHAR2(32),  

13.   DIRECTION    NVARCHAR2(16),  

14.   MILEAGE      NVARCHAR2(16),  

15.   MILEAGE1     NVARCHAR2(16),  

16.   HIGHT        NVARCHAR2(16),  

17.   TEMPERATURE  NVARCHAR2(16),  

18.   OIL          NVARCHAR2(16),  

19.   PASSENGER    NVARCHAR2(16),  

20.   S0           NVARCHAR2(2),  

21.   S1           NVARCHAR2(2),  

22.   S2           NVARCHAR2(2),  

23.   S3           NVARCHAR2(2),  

24.   S4           NVARCHAR2(2),  

25.   A0           NVARCHAR2(2),  

26.   A1           NVARCHAR2(2),  

27.   A2           NVARCHAR2(2),  

28.   A3           NVARCHAR2(2),  

29.   A4           NVARCHAR2(2),  

30.   RUNTIME      NVARCHAR2(32),  

31.   A_V          NVARCHAR2(8),  

32.   CREATEDATE   DATE default sysdate,  

33.   GPSID        NUMBER,  

34.   NUM          NUMBER,  

35.   WAY          NUMBER,  

36.   TYPE         NUMBER,  

37.   WAYSENTDATE  NUMBER,  

38.   RECEIVEDDATE NUMBER,  

39.   B0           NVARCHAR2(2),  

40.   B1           NVARCHAR2(2),  

41.   B2           NVARCHAR2(2),  

42.   B3           NVARCHAR2(2),  

43.   B4           NVARCHAR2(2),  

44.   B5           NVARCHAR2(2),  

45.   B6           NVARCHAR2(2),  

46.   CITYID       NVARCHAR2(20),  

47.   DATASTATE    NUMBER,  

48.   GPSLOCK      NVARCHAR2(2),  

49.   GPSPOWER     NVARCHAR2(2),  

50.   ADDRESS      NVARCHAR2(200)  

51. )  

52. partition by range (GPSTIME)  

53. subpartition by list (CITYID)  

54. (  

55.   partition P201010 values less than (TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  

56.     tablespace bayonet  

57.     pctfree 10  

58.     initrans 1  

59.     maxtrans 255  

60.     storage  

61.     (  

62.       initial 2  

63.       minextents 1  

64.       maxextents unlimited  

65.     )  

66.   (  

67.     subpartition P201010_P600000 values ('600000') tablespace USERS,  

68.     subpartition P201010_P600001 values ('600001') tablespace USERS,  

69.     subpartition P201010_P600002 values ('600002') tablespace USERS,  

70.     subpartition P201010_P600003 values ('600003') tablespace USERS,  

71.     subpartition P201010_P600004 values ('600004') tablespace USERS,  

72.     subpartition P201010_P600005 values ('600005') tablespace USERS,  

73.     subpartition P201010_P600006 values ('600006') tablespace USERS,  

74.     subpartition P201010_P600007 values ('600007') tablespace USERS,  

75.     subpartition P201010_P600008 values ('600008') tablespace USERS,  

76.     subpartition P201010_P600009 values ('600009') tablespace USERS,  

77.     subpartition P201010_P600010 values ('600010') tablespace USERS,  

78.     subpartition P201010_P600011 values ('600011') tablespace USERS,  

79.     subpartition P201010_P600012 values ('600012') tablespace USERS,  

80.     subpartition P201010_P600013 values ('600013') tablespace USERS,  

81.     subpartition P201010_P600014 values ('600014') tablespace USERS,  

82.     subpartition P201010_P600015 values ('600015') tablespace USERS,  

83.     subpartition P201010_P600016 values ('600016') tablespace USERS,  

84.     subpartition P201010_P600017 values ('600017') tablespace USERS,  

85.     subpartition P201010_P600018 values ('600018') tablespace USERS,  

86.     subpartition P201010_P600019 values ('600019') tablespace USERS,  

87.     subpartition P201010_P600020 values ('600020') tablespace USERS  

88.   ),  

89.   partition P201112 values less than (TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  

90.     tablespace bayonet  

91.     pctfree 10  

92.     initrans 1  

93.     maxtrans 255  

94.   (  

95.     subpartition P201112_P600000 values ('600000') tablespace USERS,  

96.     subpartition P201112_P600001 values ('600001') tablespace USERS,  

97.     subpartition P201112_P600002 values ('600002') tablespace USERS,  

98.     subpartition P201112_P600003 values ('600003') tablespace USERS,  

99.     subpartition P201112_P600004 values ('600004') tablespace USERS,  

100.     subpartition P201112_P600005 values ('600005') tablespace USERS,  

101.     subpartition P201112_P600006 values ('600006') tablespace USERS,  

102.     subpartition P201112_P600007 values ('600007') tablespace USERS,  

103.     subpartition P201112_P600008 values ('600008') tablespace USERS,  

104.     subpartition P201112_P600009 values ('600009') tablespace USERS,  

105.     subpartition P201112_P600010 values ('600010') tablespace USERS,  

106.     subpartition P201112_P600011 values ('600011') tablespace USERS,  

107.     subpartition P201112_P600012 values ('600012') tablespace USERS,  

108.     subpartition P201112_P600013 values ('600013') tablespace USERS,  

109.     subpartition P201112_P600014 values ('600014') tablespace USERS,  

110.     subpartition P201112_P600015 values ('600015') tablespace USERS,  

111.     subpartition P201112_P600016 values ('600016') tablespace USERS,  

112.     subpartition P201112_P600017 values ('600017') tablespace USERS,  

113.     subpartition P201112_P600018 values ('600018') tablespace USERS,  

114.     subpartition P201112_P600019 values ('600019') tablespace USERS,  

115.     subpartition P201112_P600020 values ('600020') tablespace USERS,  

116.     subpartition P201112_P600021 values ('600021') tablespace USERS,  

117.     subpartition P201112_P600022 values ('600022') tablespace USERS,  

118.     subpartition P201112_P600023 values ('600023') tablespace USERS,  

119.     subpartition P201112_PSUB_DEF values (default) tablespace USERS  

120.   ),  

121.   partition P201201 values less than (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  

122.     tablespace bayonet  

123.     pctfree 10  

124.     initrans 1  

125.     maxtrans 255  

126.   (  

127.     subpartition P201201_P600000 values ('600000') tablespace USERS,  

128.     subpartition P201201_P600001 values ('600001') tablespace USERS,  

129.     subpartition P201201_P600002 values ('600002') tablespace USERS,  

130.     subpartition P201201_P600003 values ('600003') tablespace USERS,  

131.     subpartition P201201_P600004 values ('600004') tablespace USERS,  

132.     subpartition P201201_P600005 values ('600005') tablespace USERS,  

133.     subpartition P201201_P600006 values ('600006') tablespace USERS,  

134.     subpartition P201201_P600007 values ('600007') tablespace USERS,  

135.     subpartition P201201_P600008 values ('600008') tablespace USERS,  

136.     subpartition P201201_P600009 values ('600009') tablespace USERS,  

137.     subpartition P201201_P600010 values ('600010') tablespace USERS,  

138.     subpartition P201201_P600011 values ('600011') tablespace USERS,  

139.     subpartition P201201_P600012 values ('600012') tablespace USERS,  

140.     subpartition P201201_P600013 values ('600013') tablespace USERS,  

141.     subpartition P201201_P600014 values ('600014') tablespace USERS,  

142.     subpartition P201201_P600015 values ('600015') tablespace USERS,  

143.     subpartition P201201_P600016 values ('600016') tablespace USERS,  

144.     subpartition P201201_P600017 values ('600017') tablespace USERS,  

145.     subpartition P201201_P600018 values ('600018') tablespace USERS,  

146.     subpartition P201201_P600019 values ('600019') tablespace USERS,  

147.     subpartition P201201_P600020 values ('600020') tablespace USERS,  

148.     subpartition P201201_P600021 values ('600021') tablespace USERS,  

149.     subpartition P201201_P600022 values ('600022') tablespace USERS,  

150.     subpartition P201201_P600023 values ('600023') tablespace USERS,  

151.     subpartition P201201_PSUB_DEF values (default) tablespace USERS  

152.   )  

153. );  

 

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

转载于:http://blog.itpub.net/29578568/viewspace-2138084/