本文档创建两个数据库空间:一个用于存放数据,另一个用于存放索引。注意其中索引空间较小。用户:sa,密码:liuxuezong,服务命名:rtsos。
一、创建一个服务命名
二、创建表空间文件
---------------------------------------------------------------------------
-- 标题 : 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...
三、使用system登录sqplus工具
四、执行rtsos_tablespace.sql文件
五、执行rtsos_createtable.sql文件
六、使用PLSQL Developer查看生成的库和表
七、删除表空间文件
---------------------------------------------------------------------------
-- 标题 : 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