常用oracle语句备忘

/*==============================================================*/
/* DBMS name:      ORACLE Version 11i                           */
/* Created on:     2008-4-24 10:53:10                           */
/*==============================================================*/


create tablespace WRMID
datafile
     'C:\ORACLE\MAIN\DBSPACE\WRMID.ORA'
    size 50M
    reuse
    autoextend on
        next 2M
        maxsize UNLIMITED
logging
permanent;

create tablespace WRPLA
datafile
     'C:\ORACLE\MAIN\DBSPACE\WRPLA.ORA'
    size 100M
    reuse
    autoextend on
        next 5M
        maxsize UNLIMITED
logging
permanent;

create tablespace WRPER
datafile
     'C:\ORACLE\MAIN\DBSPACE\WRPER1.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED,
     'C:\ORACLE\MAIN\DBSPACE\WRPER2.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED
logging
default storage
(
    initial 200K
    next 200K
    minextents 10
    maxextents unlimited
    pctincrease 50
)
permanent;

create tablespace WRORG
datafile
     'C:\ORACLE\MAIN\DBSPACE\WRORG1.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED,
     'C:\ORACLE\MAIN\DBSPACE\WRORG2.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED
logging
default storage
(
    initial 200K
    next 200K
    minextents 10
    maxextents unlimited
    pctincrease 50
)
permanent;

create tablespace WRLOB
datafile
     'C:\ORACLE\MAIN\DBSPACE\WRLOB1.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED,
     'C:\ORACLE\MAIN\DBSPACE\WRLOB2.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED
logging
default storage
(
    initial 200K
    next 200K
    minextents 10
    maxextents unlimited
    pctincrease 50
)
permanent;

create tablespace WRIDX
datafile
     'C:\ORACLE\MAIN\DBSPACE\WRIDX1.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED,
     'C:\ORACLE\MAIN\DBSPACE\WRIDX2.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED
logging
default storage
(
    initial 200K
    next 200K
    minextents 10
    maxextents unlimited
    pctincrease 50
)
permanent;

create temporary tablespace WRTMP
tempfile
     'C:\ORACLE\MAIN\DBSPACE\WRTMP1.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED,
     'C:\ORACLE\MAIN\DBSPACE\WRTMP2.ORA'
    size 1000M
    reuse
    autoextend on
        next 10M
        maxsize UNLIMITED
extent management local;

-- Create the user 
create user wrmid
  identified by admin
  default tablespace WRMID
  temporary tablespace WRTMP;
-- Grant/Revoke role privileges 
grant connect to wrmid;
grant dba to wrmid;
-- Grant/Revoke system privileges 
grant unlimited tablespace to wrmid;


-- Create the user 
create user wrserver
  identified by admin
  default tablespace WRPLA
  temporary tablespace WRTMP;
-- Grant/Revoke role privileges 
grant connect to wrserver;
grant dba to wrserver;
-- Grant/Revoke system privileges 
grant unlimited tablespace to wrserver;


--删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

drop user tmsweb cascade;

exp userid=system/[email protected] file=d:\1.dmp log=d:\1.log owner=tsg buffer=1024000 


imp userid=system/systempassword fromuser=tsg touser=tsg2007 file=d:\1.dmp buffer=10240000

imp userid=tmsweb/[email protected]/cdptms fromuser=tmsweb touser=tmsweb file=d:\tmsweb20090625233002.dmp buffer=10240000 log=d:\1.log