result.sql

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

                                                                                
  CREATE TABLE "DYT"."ARGS"                                                     
   (	"ID" NUMBER(2,0),                                                          
	"V1" NUMBER(4,0),                                                              
	"V2" NUMBER(4,0),                                                              
	"V3" NUMBER(4,0),                                                              
	"V4" NUMBER(4,0),                                                              
	"V5" 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"."CITY"                                                     
   (	"ID" NUMBER(3,0),                                                          
	"PROVINCE_ID" NUMBER(3,0),                                                     
	"NAME" VARCHAR2(20),                                                           
	"CODE" 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 "USERS"                                                            
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."COUNTRY"                                                  
   (	"ID" NUMBER(3,0),                                                          
	"CODE" NUMBER(4,0),                                                            
	"LANGUAGE" NUMBER(2,0),                                                        
	"NAME" VARCHAR2(20),                                                           
	"TS" NUMBER(6,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 TABLE "DYT"."GETUSERINF"                                               
   (	"MSISDN" VARCHAR2(20),                                                     
	"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 TABLE "DYT"."HISTORY"                                                  
   (	"STIME" DATE,                                                              
	"MSISDN" VARCHAR2(20),                                                         
	"MSGID" NUMBER(4,0)                                                            
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  LOGGING                     
  STORAGE(                                                                      
  BUFFER_POOL DEFAULT)                                                          
  TABLESPACE "SYSTEM"                                                           
  PARTITION BY RANGE ("STIME")                                                  
 (PARTITION "HISTORY200306"  VALUES LESS THAN (TO_DATE(' 2003-07-01 00:00:00', '
ALENDAR=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 "HISTORY04" NOCOMPRESS ,                                           
 PARTITION "HISTORY200409"  VALUES LESS THAN (TO_DATE(' 2004-10-01 00:00:00', 'S
S', 'NLS_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 "HISTORY01" NOCOMPRESS ,                                           
 PARTITION "HISTORY200410"  VALUES LESS THAN (TO_DATE(' 2004-11-01 00:00:00', 'S
', 'NLS_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 "HISTORY02" NOCOMPRESS ,                                           
 PARTITION "HISTORY200411"  VALUES LESS THAN (TO_DATE(' 2004-12-01 00:00:00', 'S
, 'NLS_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 "HISTORY03" NOCOMPRESS )                                           
 ;                                                                              
                                                                                
                                                                                
  CREATE TABLE "DYT"."IP"                                                       
   (	"ID" NUMBER(2,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"."LANGUAGE"                                                 
   (	"ID" NUMBER(2,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 "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"."LIMIT_CITY"                                               
   (	"LOCAL_CITY_ID" NUMBER(3,0),                                               
	"VISIT_CITY_ID" NUMBER(3,0),                                                   
	"DSC" VARCHAR2(100)                                                            
   ) 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"."LIMIT_VLR"                                                
   (	"LOCAL_CITY_ID" NUMBER(3,0),                                               
	"VISIT_CITY_VLR" NUMBER(10,0),                                                 
	"DSC" VARCHAR2(100)                                                            
   ) 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"."MSG"                                                      
   (	"ID" NUMBER(4,0),                                                          
	"HANDLE" NUMBER(1,0),                                                          
	"LANGUAGE" NUMBER(2,0),                                                        
	"STIME" DATE,                                                                  
	"ETIME" DATE,                                                                  
	"NAME" VARCHAR2(40),                                                           
	"DSC" VARCHAR2(200),                                                           
	"MSG" VARCHAR2(400),                                                           
	"START_TS" NUMBER(4,0),                                                        
	"STOP_TS" NUMBER(4,0),                                                         
	"DELAY" NUMBER(2,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"."MSGSTAT"                                                  
   (	"STIME" DATE,                                                              
	"MSGID" NUMBER(2,0),                                                           
	"SEND" NUMBER(8,0)                                                             
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                              
  STORAGE(                                                                      
  BUFFER_POOL DEFAULT)                                                          
  TABLESPACE "USERS"                                                            
  PARTITION BY RANGE ("STIME")