result.sql

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

 (PARTITION "MSGSTAT200306"  VALUES LESS THAN (TO_DATE(' 2003-07-01 00:00:00', '
Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=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 "MSGSTAT04" NOCOMPRESS ,                                           
 PARTITION "MSGSTAT200409"  VALUES LESS THAN (TO_DATE(' 2004-10-01 00:00:00', 'S
NDAR=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 "MSGSTAT01" NOCOMPRESS ,                                           
 PARTITION "MSGSTAT200410"  VALUES LESS THAN (TO_DATE(' 2004-11-01 00:00:00', 'S
 'NLS_CALENDAR=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 "MSGSTAT02" NOCOMPRESS ,                                           
 PARTITION "MSGSTAT200411"  VALUES LESS THAN (TO_DATE(' 2004-12-01 00:00:00', 'S
24:MI:SS', 'NLS_CALENDAR=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 "MSGSTAT03" NOCOMPRESS )                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."NO"                                                       
   (	"ID" NUMBER(10,0),                                                         
	"TYPE" NUMBER(1,0),                                                            
	"CITY_ID" NUMBER(3,0),                                                         
	"PROVINCE_ID" NUMBER(3,0),                                                     
	 PRIMARY KEY ("ID")                                                            
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255                                
  STORAGE(INITIAL 3604480 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 1638400 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645       
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "SYSTEM"                                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."NOTKNOWNO"                                                
   (	"ID" NUMBER(8,0),                                                          
	"STIME" DATE,                                                                  
	 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"."NOTKNOWVLR"                                               
   (	"ID" NUMBER(8,0),                                                          
	"STIME" DATE,                                                                  
	 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"."NOTSENDMSG"                                               
   (	"MSISDN" VARCHAR2(20),                                                     
	"HANDLE" NUMBER(1,0),                                                          
	"STIME" DATE,                                                                  
	 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 "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"."OPERATEUSER"                                              
   (	"ID" NUMBER(8,0),                                                          
	"NAME" VARCHAR2(20),                                                           
	"PASSWORD" VARCHAR2(20),                                                       
	"GROUPID" NUMBER(2,0),                                                         
	"CITYID" NUMBER(4,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"."PROVINCE"                                                 
   (	"ID" NUMBER(3,0),                                                          
	"NAME" VARCHAR2(20),                                                           
	 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"."SMECONFIG"                                                
   (	"SMEIP" VARCHAR2(20),                                                      
	"SMEPORT" NUMBER(8,0),                                                         
	"SMEUSER" VARCHAR2(20),                                                        
	"SMEPASSWORD" VARCHAR2(20),                                                    
	"SMESRCADDR" VARCHAR2(20)                                                      
   ) 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"."STAT"                                                     
   (	"STIME" DATE,                                                              
	"CITY" NUMBER(4,0),                                                            
	"VLRCITY" NUMBER(4,0),                                                         
	"VISIT" NUMBER(12,0),                                                          
	"SEND" NUMBER(12,0)                                                            
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  LOGGING                     
  STORAGE(                                                                      
  BUFFER_POOL DEFAULT)                                                          
  TABLESPACE "SYSTEM"                                                           
  PARTITION BY RANGE ("STIME")                                                  
 (PARTITION "STAT200304"  VALUES LESS THAN (TO_DATE(' 2003-05-01 00:00:00', 'SYY
ALENDAR=GREGORIAN'))                                                            
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING                      
  STORAGE(INITIAL 40960 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "STAT04" NOCOMPRESS ,                                              
 PARTITION "STAT200409"  VALUES LESS THAN (TO_DATE(' 2004-10-01 00:00:00', 'SYYY
LS_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 "STAT01" NOCOMPRESS ,                                              
 PARTITION "STAT200410"  VALUES LESS THAN (TO_DATE(' 2004-11-01 00:00:00', 'SYYY
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 "STAT02" NOCOMPRESS ,                                              
 PARTITION "STAT200411"  VALUES LESS THAN (TO_DATE(' 2004-12-01 00:00:00', 'SYYY
EGORIAN'))                                                                      
  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 "STAT03" NOCOMPRESS )                                              
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."STATUS"                                                   
   (	"STIME" DATE,                                                              
	"BUFMSGCOUNT" NUMBER(10,0),