Oracle脚本__ZC
ZC:创建表的时候,类似“TABLESPACE "WZDTS"” 这个还是不要为好...
1、添加字段
1.1、20170628
Alter table ExerciseHis add OpTimeRelative varchar2(19) NULL; // 20170630 Edit数据库里面 没有这个表
Alter table CB add CanYaoKong int default(1); // 20170630 Edit数据库里面也要加这个字段
2、20170720
无主键
CREATE TABLE "WZDTSEDIT"."FUHE" ( "KAIGUANDUAN" VARCHAR2(80 BYTE) NOT NULL ENABLE, "RONGLIANGPERCENT" NUMBER, "HZ" NUMBER, "RONGLIANGVALUE" NUMBER ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) TABLESPACE "WZDTS" ;
2.1、上面建错地方了,应该建在数据库PAS里面 却建在了 EDIT库里面了...
CREATE TABLE "WZDTSPAS"."FUHE" ( "KAIGUANDUAN" VARCHAR2(80 BYTE) NOT NULL ENABLE, "RONGLIANGPERCENT" NUMBER, "HZ" NUMBER, "RONGLIANGVALUE" NUMBER ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) TABLESPACE "WZDTS" ;
2.2、(20170726)
CREATE TABLE "WZDTSPAS"."FUHE" ( "KAIGUANDUAN" VARCHAR2(80 BYTE) NOT NULL ENABLE, "RONGLIANGPERCENT" NUMBER, "HZ" NUMBER, "RONGLIANGVALUE" NUMBER, "P" NUMBER, "Q" NUMBER ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) TABLESPACE "WZDTS" ;
2.3、(20170803)在脚本里面 要写成这样:
CREATE TABLE FUHE ( KAIGUANDUAN VARCHAR2(80) NOT NULL ENABLE, RONGLIANGPERCENT NUMBER, HZ NUMBER, RONGLIANGVALUE NUMBER, P NUMBER, Q NUMBER );
3、20170803
CREATE TABLE "WZDTSPAS"."FUHEMATCH" ( "KAIGUANDUAN" VARCHAR2(200 BYTE), "LABEL" VARCHAR2(20 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) TABLESPACE "WZDTS" ;
3.1、
CREATE TABLE FUHEMATCH ( KAIGUANDUAN VARCHAR2(200 BYTE), LABEL VARCHAR2(20 BYTE) );
4、
4.1、20170818
Alter table ExerciseHis add P_Total float default(0);
5、20170823
修改字段的类型:
alter table PointRule modify (Point number);
alter table PointRule modify (Point float);
6、20170906
Pas库里面的表 ExerciseHis 添加字段"Changes":
Alter table ExerciseHis add Changes varchar2(500) NULL;
7、20170911 前几天 将CZDTS的用户登录 从使用UserLogon.dll(数据库是 Users库)的方式 改成更直接在 pas库中登录,里面做了一些数据库的改动,当时没有记录下来,现在补充记录一下:
7.1、新建了一张表 UserPartment
CREATE TABLE USERPARTMENT ( "NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, "ID" NUMBER(*,0) NOT NULL ENABLE, CONSTRAINT "USERPARTMENT_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WZDTS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) TABLESPACE "WZDTS" ;
修改一下,去掉表空间的指定:
CREATE TABLE USERPARTMENT ( "NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, "ID" NUMBER(*,0) NOT NULL ENABLE, CONSTRAINT "USERPARTMENT_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WZDTS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );
ZC: 这个SQL语句不知道该 怎么精简...
7.2、对原来的表 Users 做了一些修改
CREATE TABLE USERS ( "USERNAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, "PW" VARCHAR2(128 BYTE), "RIGHTS" NUMBER(*,0), "USERROLE" NUMBER(*,0), "PARTMENTID" NUMBER(*,0) DEFAULT 0, PRIMARY KEY ("USERNAME") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YUEQING" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );
7.3、网上查 修改表的表空间的方式,貌似用语句修改比较麻烦,我看到 SqlDeveloper里面有可以改的地方:
OracleSqlDeveloper-->选择某张表-->右键-->编辑...-->左侧选择“表属性”-->点击右侧“存储选项”
ZC:但是 没敢 修改... 用本地的数据库 测试下再说...
8、