1. orcale建表时候没有指定表空间 【orcale没有指定表空间且orcale用户没有指定默认表空间时，建表的时候在系统默认表空间上建表会出现没权限错误】
2. orcale用户没有指定默认的表空间 【建表的时候没有权限】
3.orcale建表按日分区
CREATE TABLE DW_DELIVERY_DETAIL
( OPER_DATE DATE,
HQ_CODE VARCHAR2(20),
AREA_CODE VARCHAR2(20),
DEPT_CODE VARCHAR2(20),
WAYBILL_NO VARCHAR2(50),
COURIER_CODE VARCHAR2(20),
RESOURCE_ID VARCHAR2(20),
IS_EQUALS VARCHAR2(10),
OPER_TIME VARCHAR2(50),
BAR_SN VARCHAR2(10)
)
partition by range (OPER_DATE)
(
partition P20160628 values less than (TO_DATE(' 2016-06-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160629 values less than (TO_DATE(' 2016-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160630 values less than (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160701 values less than (TO_DATE(' 2016-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160702 values less than (TO_DATE(' 2016-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160703 values less than (TO_DATE(' 2016-07-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160704 values less than (TO_DATE(' 2016-07-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160705 values less than (TO_DATE(' 2016-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160706 values less than (TO_DATE(' 2016-07-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160707 values less than (TO_DATE(' 2016-07-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160708 values less than (TO_DATE(' 2016-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160709 values less than (TO_DATE(' 2016-07-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160710 values less than (TO_DATE(' 2016-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160711 values less than (TO_DATE(' 2016-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160712 values less than (TO_DATE(' 2016-07-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160713 values less than (TO_DATE(' 2016-07-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160714 values less than (TO_DATE(' 2016-07-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160715 values less than (TO_DATE(' 2016-07-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160716 values less than (TO_DATE(' 2016-07-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160717 values less than (TO_DATE(' 2016-07-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160718 values less than (TO_DATE(' 2016-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160719 values less than (TO_DATE(' 2016-07-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160720 values less than (TO_DATE(' 2016-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160721 values less than (TO_DATE(' 2016-07-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160722 values less than (TO_DATE(' 2016-07-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160723 values less than (TO_DATE(' 2016-07-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160724 values less than (TO_DATE(' 2016-07-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160725 values less than (TO_DATE(' 2016-07-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160726 values less than (TO_DATE(' 2016-07-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160727 values less than (TO_DATE(' 2016-07-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160728 values less than (TO_DATE(' 2016-07-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160729 values less than (TO_DATE(' 2016-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160730 values less than (TO_DATE(' 2016-07-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160731 values less than (TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160801 values less than (TO_DATE(' 2016-08-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160802 values less than (TO_DATE(' 2016-08-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160803 values less than (TO_DATE(' 2016-08-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160804 values less than (TO_DATE(' 2016-08-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160805 values less than (TO_DATE(' 2016-08-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160806 values less than (TO_DATE(' 2016-08-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160807 values less than (TO_DATE(' 2016-08-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160808 values less than (TO_DATE(' 2016-08-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160809 values less than (TO_DATE(' 2016-08-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160810 values less than (TO_DATE(' 2016-08-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160811 values less than (TO_DATE(' 2016-08-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160812 values less than (TO_DATE(' 2016-08-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160813 values less than (TO_DATE(' 2016-08-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160814 values less than (TO_DATE(' 2016-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160815 values less than (TO_DATE(' 2016-08-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160816 values less than (TO_DATE(' 2016-08-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160817 values less than (TO_DATE(' 2016-08-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160818 values less than (TO_DATE(' 2016-08-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160819 values less than (TO_DATE(' 2016-08-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160820 values less than (TO_DATE(' 2016-08-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160821 values less than (TO_DATE(' 2016-08-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160822 values less than (TO_DATE(' 2016-08-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160823 values less than (TO_DATE(' 2016-08-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160824 values less than (TO_DATE(' 2016-08-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160825 values less than (TO_DATE(' 2016-08-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160826 values less than (TO_DATE(' 2016-08-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160827 values less than (TO_DATE(' 2016-08-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160828 values less than (TO_DATE(' 2016-08-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160829 values less than (TO_DATE(' 2016-08-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160830 values less than (TO_DATE(' 2016-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P20160831 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
;4. orcale备份数据还原表操作
1、验证默认的表空间是否为： SFBOD_SGS, 是否有写入权限：
create table testTable(
fieldA varchar(20),
fieldB varchar(20)
);
insert into testTable values('test','test');
commit;
执行后是否报错， 并查看表的表空间是否为: SFBOD_SGS
2、升级原表的字段，保证与新表结构一致。
alter table DW_DELIVERY_TASK_REPORT add BAR_SN varchar2(10);
comment on column DW_DELIVERY_TASK_REPORT.BAR_SN
is '设备类型';
3、备份原数据到备份表：
create table DW_DELIVERY_TASK_REPORT_BACKUP
as
select * from DW_DELIVERY_TASK_REPORT;
create table DW_DELIVERY_DETAIL_BACKUP
as
select * from DW_DELIVERY_DETAIL;
create table DW_ORDER_ANALYSIS_BACKUP
as
select * from DW_ORDER_ANALYSIS;
create table DW_RECEIVE_DETAIL_BACKUP
as
select * from DW_RECEIVE_DETAIL;
4、删掉原表:
drop table DW_DELIVERY_TASK_REPORT;
drop table DW_DELIVERY_DETAIL;
drop table DW_ORDER_ANALYSIS;
drop table DW_RECEIVE_DETAIL;
drop table DW_INPUT_RETURN;
drop table DIC_STATUS_TWO;
drop table DIC_STATUS_FOUR;
drop table DIC_STATUS_CODE;
drop table DIC_IMAGE_EDIT_STATUS;
drop table DIC_IS_FINISHED;
drop table DIC_ORDER_SOURCE;
drop table DIC_TAKE_PICTURE;
drop table DIC_DELIVERY_TYPE;
5、重新建立新的表（不指定表空间、指定一部分的分区名）
直接执行提供的sql脚本
6、把备份数据导入到新的表中:
insert into DW_DELIVERY_TASK_REPORT
select * from DW_DELIVERY_TASK_REPORT_BACKUP;
insert into DW_DELIVERY_DETAIL
select * from DW_DELIVERY_DETAIL_BACKUP;
insert into DW_ORDER_ANALYSIS
select * from DW_ORDER_ANALYSIS_BACKUP;
insert into DW_RECEIVE_DETAIL
select * from DW_RECEIVE_DETAIL_BACKUP;
7、确认无误后，删除备份表
drop table DW_DELIVERY_TASK_REPORT_BACKUP;
drop table DW_DELIVERY_DETAIL_BACKUP;
drop table DW_ORDER_ANALYSIS_BACKUP;
drop table DW_RECEIVE_DETAIL_BACKUP;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
