1 --1、表创建实例
2 --测试表名
3 --drop sequence FLXUSER.SEQ_CST_D_COMPONENTFAULT;
4
5 create sequence FLXUSER.SEQ_CST_D_COMPONENTFAULT
6 increment by 1
7 start with 100000000
8 nomaxvalue
9 nominvalue
10 nocycle
11 noorder;
12
13
14
15 --drop trigger FLXUSER.INS_CST_D_COMPONENTFAULT
16 --/
17
18 --drop table FLXUSER.CST_D_COMPONENTFAULT cascade constraints
19 --/
20
21 /*==============================================================*/
22 /* Table: CST_D_COMPONENTFAULT */
23 /*==============================================================*/
24 create table FLXUSER.CST_D_COMPONENTFAULT
25 (
26 ID NUMBER not null,
27 WIPORDERNO NVARCHAR2(40),
28 WIPORDERTYPE NUMBER,
29 LASTUPDATEON DATE,
30 LASTUPDATEDBY NVARCHAR2(50),
31 CREATEDON DATE,
32 CREATEDBY NVARCHAR2(50),
33 ACTIVE NUMBER(1) default 1 not null,
34 LASTDELETEON DATE,
35 LASTDELETEDBY NVARCHAR2(50),
36 LASTREACTIVATEON DATE,
37 LASTREACTIVATEDBY NVARCHAR2(50),
38 ARCHIVEID NUMBER,
39 LASTARCHIVEON DATE,
40 LASTARCHIVEDBY NVARCHAR2(50),
41 LASTRESTOREON DATE,
42 LASTRESTOREDBY NVARCHAR2(50),
43 ROWVERSIONSTAMP NUMBER(38) default 1,
44 constraint AK_CST_D_COMPONENTFAU_CST_D_CO unique (ID)
45 )
46 pctfree 10
47 initrans 1
48 storage
49 (
50 initial 64K
51 next 1024K
52 minextents 1
53 maxextents unlimited
54 )
55 tablespace FLEXNET
56 logging
57 nocompress
58 monitoring
59 noparallel
60 /
61
62 comment on table FLXUSER.CST_D_COMPONENTFAULT is
63 '测试表'
64 /
65
66 comment on column FLXUSER.CST_D_COMPONENTFAULT.ID is
67 'ID'
68 /
69
70
71 create trigger FLXUSER.INS_CST_D_COMPONENTFAULT before insert on FLXUSER.CST_D_COMPONENTFAULT FOR EACH ROW
72 DECLARE
73 i_LAST NUMBER;
74 BEGIN
75 SELECT SEQ_CST_D_COMPONENTFAULT.NEXTVAL INTO i_LAST FROM Dual;
76 PKG_IDENTITY.SET_IDENTITY(i_LAST);
77 :NEW.ID := i_LAST;
78 END;
79 /
80
81 GRANT
82 SELECT ON "FLXUSER"."CST_D_COMPONENTFAULT" TO "APP_READER_FLXUSER";
83 GRANT DELETE, INSERT,
84 SELECT , UPDATE ON "FLXUSER"."CST_D_COMPONENTFAULT" TO "APP_WRITER_FLXUSER";
85
86
87
88 --2、表备份实例
89 --1
90 create table CST_WIPOPERATION_EXT_D as select * from CST_WIPOPERATION_EXT;
91
92 --2
93 drop table CST_WIPOPERATION_EXT;
94
95 --3
96 CREATE TABLE "FLXUSER"."CST_WIPOPERATION_EXT"
97 ( "ID" NUMBER NOT NULL ENABLE,
98 "WIPORDERNO" NVARCHAR2(40),
99 "WIPORDERTYPE" NUMBER,
100
101 "WIPORDERNOEXTEND" NVARCHAR2(40), --新增字段
102
103 "LASTUPDATEON" DATE,
104 "LASTUPDATEDBY" NVARCHAR2(50),
105 "CREATEDON" DATE,
106 "CREATEDBY" NVARCHAR2(50),
107 "ACTIVE" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE,
108 "LASTDELETEON" DATE,
109 "LASTDELETEDBY" NVARCHAR2(50),
110 "LASTREACTIVATEON" DATE,
111 "LASTREACTIVATEDBY" NVARCHAR2(50),
112 "ARCHIVEID" NUMBER,
113 "LASTARCHIVEON" DATE,
114 "LASTARCHIVEDBY" NVARCHAR2(50),
115 "LASTRESTOREON" DATE,
116 "LASTRESTOREDBY" NVARCHAR2(50),
117 "ROWVERSIONSTAMP" NUMBER(38,0) DEFAULT 1,
118 CONSTRAINT "PK_CST_WIPOPERATION_EXT" UNIQUE ("ID")
119 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
120 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
121 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
122 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
123 TABLESPACE "FLEXNET" ENABLE
124 ) SEGMENT CREATION IMMEDIATE
125 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
126 NOCOMPRESS LOGGING
127 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
128 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
129 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
130 TABLESPACE "FLEXNET" ;
131 CREATE UNIQUE INDEX "FLXUSER"."PK_CST_WIPOPERATION_EXT" ON "FLXUSER"."CST_WIPOPERATION_EXT" ("ID")
132 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
133 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
134 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
135 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
136 TABLESPACE "FLEXNET" ;
137 ALTER TABLE "FLXUSER"."CST_WIPOPERATION_EXT" ADD CONSTRAINT "PK_CST_WIPOPERATION_EXT" UNIQUE ("ID")
138 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
139 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
140 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
141 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
142 TABLESPACE "FLEXNET" ENABLE;
143 ALTER TABLE "FLXUSER"."CST_WIPOPERATION_EXT" MODIFY ("ACTIVE" NOT NULL ENABLE);
144 ALTER TABLE "FLXUSER"."CST_WIPOPERATION_EXT" MODIFY ("ID" NOT NULL ENABLE);
145 COMMENT ON COLUMN "FLXUSER"."CST_WIPOPERATION_EXT"."ID" IS 'ID';
146
147 GRANT UPDATE ON "FLXUSER"."CST_WIPOPERATION_EXT" TO "APP_WRITER_FLXUSER";
148 GRANT SELECT ON "FLXUSER"."CST_WIPOPERATION_EXT" TO "APP_WRITER_FLXUSER";
149 GRANT INSERT ON "FLXUSER"."CST_WIPOPERATION_EXT" TO "APP_WRITER_FLXUSER";
150 GRANT DELETE ON "FLXUSER"."CST_WIPOPERATION_EXT" TO "APP_WRITER_FLXUSER";
151 GRANT SELECT ON "FLXUSER"."CST_WIPOPERATION_EXT" TO "APP_READER_FLXUSER";
152
153 create trigger FLXUSER.INS_CST_WIPOPERATION_EXT before insert on FLXUSER.CST_WIPOPERATION_EXT FOR EACH ROW
154 DECLARE
155 i_LAST NUMBER;
156 BEGIN
157 SELECT SEQ_CST_WIPOPERATION_EXT.NEXTVAL INTO i_LAST FROM Dual;
158 PKG_IDENTITY.SET_IDENTITY(i_LAST);
159 :NEW.ID := i_LAST;
160 END;
161
162 --4
163 insert into CST_WIPOPERATION_EXT( ID,
164 WIPORDERNO,
165 WIPORDERTYPE,
166 LASTDELETEON,
167 LASTDELETEDBY,
168 LASTREACTIVATEON,
169 LASTREACTIVATEDBY,
170 ARCHIVEID,
171 LASTARCHIVEON,
172 LASTARCHIVEDBY,
173 LASTRESTOREON,
174 LASTRESTOREDBY,
175 ROWVERSIONSTAMP)
176 select ( ID,
177 WIPORDERNO,
178 WIPORDERTYPE,
179 LASTUPDATEON,
180 LASTUPDATEDBY,
181 CREATEDON,
182 CREATEDBY,
183 ACTIVE,
184 LASTDELETEON,
185 LASTDELETEDBY,
186 LASTREACTIVATEON,
187 LASTREACTIVATEDBY,
188 ARCHIVEID,
189 LASTARCHIVEON,
190 LASTARCHIVEDBY,
191 LASTRESTOREON,
192 LASTRESTOREDBY,
193 ROWVERSIONSTAMP) from CST_WIPOPERATION_EXT_D;
浙公网安备 33010602011771号