PL/SQL 操作数据库常见脚本

---------------------------------------------------------------------------

-- 标题     : rtsos_tablespace.sql 

--

-- 功能     ：创建'RTSOS'数据库的表空间和用户。

--

-- 项目组   ：RTSOS

--

--

---------------------------------------------------------------------------

 

/*-------------------------------------------------------------------------

 *

 * 创建表空间

 *

 *-------------------------------------------------------------------------

 */

 

CREATE TABLESPACE RTSOS

         NOLOGGING 

         EXTENT MANAGEMENT LOCAL

         DATAFILE 'D:\oracle\product\10.2.0\oradata\RTSOS.dbf' 

         SIZE 128M 

         AUTOEXTEND ON 

         NEXT 16M 

         MAXSIZE UNLIMITED;

 

CREATE TABLESPACE RTSOS_INDEX 

         NOLOGGING 

         EXTENT MANAGEMENT LOCAL

         DATAFILE 'D:\oracle\product\10.2.0\oradata\RTSOS_INDEX.dbf' 

         SIZE 16M 

         AUTOEXTEND ON 

         NEXT 2M 

         MAXSIZE UNLIMITED;

 

---------------------------------------------------------------------------

--

-- 增加用户

-- 

---------------------------------------------------------------------------

 

 

prompt 增加用户：sa

 

-- 查询系统用户:select username from dba_users;

 

create user sa 

         identified by liuxuezong       default tablespace RTSOS

         temporary tablespace TEMP

         profile DEFAULT; 

 

grant connect to sa;

grant dba to sa;

grant resource to sa;

grant unlimited tablespace to sa;


/*---------------------------------------------------------------------------

-- 标题     : rtsos_createtable.sql 

--

-- 功能     ：创建'RTSOS'数据库中相关的表。

--

-- 项目组   ：RTSOS

--

--            

---------------------------------------------------------------------------*/

connect  sa/liuxuezong@RTSOS;

---------------------------------------------------------------------------

--

-- 工务表:TRPW_SAMPLE100HZ

--

---------------------------------------------------------------------------

 

prompt Creating TRPW_SAMPLE100HZ...

 

create table TRPW_SAMPLE100HZ

(

  TYPE        number (2) not null,

  SAMPLE_TIME NUMBER(12) not null,

  VAL0        number(9,3) null,

  VAL1        number(9,3) null,

  VAL2        number(9,3) null,

  VAL3        number(9,3) null,

  VAL4        number(9,3) null,

  VAL5        number(9,3) null,

  VAL6        number(9,3) null,

  VAL7        number(9,3) null,

  VAL8        number(9,3) null,

  VAL9        number(9,3) null,

  VAL10       number(9,3) null,

  VAL11       number(9,3) null,

  VAL12       number(9,3) null,

  VAL13       number(9,3) null,

  VAL14       number(9,3) null,

  VAL15       number(9,3) null,

  VAL16       number(9,3) null,

  VAL17       number(9,3) null,

  VAL18       number(9,3) null,

  VAL19       number(9,3) null,

  VAL20       number(9,3) null,

  VAL21       number(9,3) null,

  VAL22       number(9,3) null,

  VAL23       number(9,3) null,

  VAL24       number(9,3) null,

  VAL25       number(9,3) null,

  VAL26       number(9,3) null,

  VAL27       number(9,3) null,

  VAL28       number(9,3) null,

  VAL29       number(9,3) null,

  VAL30       number(9,3) null,

  VAL31       number(9,3) null,

  VAL32       number(9,3) null,

  VAL33       number(9,3) null,

  VAL34       number(9,3) null,

  VAL35       number(9,3) null,

  VAL36       number(9,3) null,

  VAL37       number(9,3) null,

  VAL38       number(9,3) null,

  VAL39       number(9,3) null,

  VAL40       number(9,3) null,

  VAL41       number(9,3) null,

  VAL42       number(9,3) null,

  VAL43       number(9,3) null,

  VAL44       number(9,3) null,

  VAL45       number(9,3) null,

  VAL46       number(9,3) null,

  VAL47       number(9,3) null,

  VAL48       number(9,3) null,

  VAL49       number(9,3) null,

  VAL50       number(9,3) null,

  VAL51       number(9,3) null,

  VAL52       number(9,3) null,

  VAL53       number(9,3) null,

  VAL54       number(9,3) null,

  VAL55       number(9,3) null,

  VAL56       number(9,3) null,

  VAL57       number(9,3) null,

  VAL58       number(9,3) null,

  VAL59       number(9,3) null,

  VAL60       number(9,3) null,

  VAL61       number(9,3) null,

  VAL62       number(9,3) null,

  VAL63       number(9,3) null,

  VAL64       number(9,3) null,

  VAL65       number(9,3) null,

  VAL66       number(9,3) null,

  VAL67       number(9,3) null,

  VAL68       number(9,3) null,

  VAL69       number(9,3) null,

  VAL70       number(9,3) null,

  VAL71       number(9,3) null,

  VAL72       number(9,3) null,

  VAL73       number(9,3) null,

  VAL74       number(9,3) null,

  VAL75       number(9,3) null,

  VAL76       number(9,3) null,

  VAL77       number(9,3) null,

  VAL78       number(9,3) null,

  VAL79       number(9,3) null,

  VAL80       number(9,3) null,

  VAL81       number(9,3) null,

  VAL82       number(9,3) null,

  VAL83       number(9,3) null,

  VAL84       number(9,3) null,

  VAL85       number(9,3) null,

  VAL86       number(9,3) null,

  VAL87       number(9,3) null,

  VAL88       number(9,3) null,

  VAL89       number(9,3) null,

  VAL90       number(9,3) null,

  VAL91       number(9,3) null,

  VAL92       number(9,3) null,

  VAL93       number(9,3) null,

  VAL94       number(9,3) null,

  VAL95       number(9,3) null,

  VAL96       number(9,3) null,

  VAL97       number(9,3) null,

  VAL98       number(9,3) null,

  VAL99       number(9,3) null

)
STORAGE ( INITIAL 64K NEXT 64K minextents 1 maxextents UNLIMITED PCTINCREASE 0)
NOLOGGING;

 

alter table TRPW_SAMPLE100HZ

  add  PRIMARY KEY(TYPE, SAMPLE_TIME)

  using index tablespace RTSOS_INDEX 
         STORAGE(INITIAL 64K NEXT 64K minextents 1 maxextents UNLIMITED PCTINCREASE 0)

         NOLOGGING;

 

grant select on TRPW_SAMPLE100HZ to PUBLIC;

 

---------------------------------------------------------------------------

prompt 1 Tables Created...

---------------------------------------------------------------------------

-- 标题     : rtsos_droptablespace.sql 

--

-- 功能     ：删除'RTSOS'数据库的表空间和用户。

--

-- 项目组   ：RTSOS

--

--            

---------------------------------------------------------------------------

 

/*-------------------------------------------------------------------------

 *

 * 删除表空间

 *

 *-------------------------------------------------------------------------

 */

DROP TABLESPACE RTSOS INCLUDING CONTENTS AND DATAFILES;

 

 

DROP TABLESPACE RTSOS_INDEX INCLUDING CONTENTS AND DATAFILES;

 

 

---------------------------------------------------------------------------

--

-- 删除用户

--

---------------------------------------------------------------------------

 

 

prompt 删除用户：sa

 

DROP USER sa CASCADE