result.sql

本人在工程上收集的比较常用的oracle sql语句 有问题请发email:[e-mail]

	"TOTAL" NUMBER(10,0),                                                          
	"LOST" NUMBER(10,0),                                                           
	"EMPTY" NUMBER(10,0),                                                          
	"SENDED" NUMBER(10,0),                                                         
	"FAIL" NUMBER(10,0),                                                           
	 PRIMARY KEY ("STIME")                                                         
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                     
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "SYSTEM"  ENABLE                                                   
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  LOGGING                     
  STORAGE(                                                                      
  BUFFER_POOL DEFAULT)                                                          
  TABLESPACE "SYSTEM"                                                           
  PARTITION BY RANGE ("STIME")                                                  
 (PARTITION "STATUS200306"  VALUES LESS THAN (TO_DATE(' 2003-07-01 00:00:00', 'S
AR=GREGORIAN'))                                                                 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING                      
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "STATUS04" NOCOMPRESS ,                                            
 PARTITION "STATUS200409"  VALUES LESS THAN (TO_DATE(' 2004-10-01 00:00:00', 'SY
S_CALENDAR=GREGORIAN'))                                                         
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "STATUS01" NOCOMPRESS ,                                            
 PARTITION "STATUS200410"  VALUES LESS THAN (TO_DATE(' 2004-11-01 00:00:00', 'SY
ALENDAR=GREGORIAN'))                                                            
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "STATUS02" NOCOMPRESS ,                                            
 PARTITION "STATUS200411"  VALUES LESS THAN (TO_DATE(' 2004-12-01 00:00:00', 'SY
NDAR=GREGORIAN'))                                                               
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "STATUS03" NOCOMPRESS )                                            
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."TESTLOG"                                                  
   (	"STIME" DATE,                                                              
	"MSISDN" VARCHAR2(20),                                                         
	"LOGTYPE" NUMBER(2,0),                                                         
	"DSC" VARCHAR2(200)                                                            
   ) 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 "USERS"                                                            
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."TEST_MSISDN"                                              
   (	"ID" NUMBER,                                                               
	"MSISDN" VARCHAR2(20),                                                         
	"NAME" VARCHAR2(50),                                                           
	 PRIMARY KEY ("MSISDN")                                                        
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255                                
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS"  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 "USERS"                                                            
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."USERINF"                                                  
   (	"ID" NUMBER(*,0),                                                          
	"MSISDN" VARCHAR2(20),                                                         
	"CITY" VARCHAR2(50),                                                           
	"STIME" DATE,                                                                  
	"STIME1" VARCHAR2(20),                                                         
	"VCITY1" VARCHAR2(50),                                                         
	"STIME2" VARCHAR2(20),                                                         
	"VCITY2" VARCHAR2(50),                                                         
	 PRIMARY KEY ("ID")                                                            
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255                                
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "SYSTEM"  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 "SYSTEM"                                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."VLR"                                                      
   (	"ID" NUMBER(10,0),                                                         
	"TYPE" NUMBER(2,0),                                                            
	"CITY_ID" NUMBER(3,0),                                                         
	"PROVINCE_ID" NUMBER(3,0),                                                     
	 PRIMARY KEY ("ID")                                                            
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255                                
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "SYSTEM"  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 "SYSTEM"                                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."WEATHER"                                                  
   (	"CITYCODE" VARCHAR2(20),                                                   
	"WEATHER" VARCHAR2(200),                                                       
	 PRIMARY KEY ("CITYCODE")                                                      
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255                                
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "SYSTEM"  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 "SYSTEM"                                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."WLANGUAGE"                                                
   (	"COUNTRY_ID" NUMBER(4,0),                                                  
	"LANGUAGE" NUMBER(2,0),                                                        
	 PRIMARY KEY ("COUNTRY_ID")                                                    
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255                                
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS"  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 "USERS"                                                            
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."WNO"                                                      
   (	"ID" NUMBER(10,0),                                                         
	"COUNTRY_ID" NUMBER(4,0),                                                      
	"NAME" VARCHAR2(40),                                                           
	"LANGUAGE" NUMBER(2,0),                                                        
	"HANDLE" NUMBER(1,0)                                                           
   ) 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 "SYSTEM"                                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE UNIQUE INDEX "DYT"."CITY_ID_IDX" ON "DYT"."CITY" ("ID")                
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                            
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS"                                                            
 ;                                                                              
                                                                                
                                                                                
  CREATE INDEX "DYT"."HISTORY_MSISDN_IDX" ON "DYT"."HISTORY" ("MSISDN")         
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                            
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "SYSTEM"                                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE INDEX "DYT"."HISTORY_STIME_IDX" ON "DYT"."HISTORY" ("STIME")           
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                            
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "SYSTEM"                                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE UNIQUE INDEX "DYT"."LIMIT_CITY_IDX" ON "DYT"."LIMIT_CITY" ("LOCAL_CITY_
  PCTFREE 10 INITRANS 2 MAXTRANS 255