ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 创建大型数据库规划

创建大型数据库规划

原创 Linux操作系统 作者:sxrenjb 时间:2012-05-18 18:18:04 0 删除 编辑

一、创建数据库

1、创建电销数据管理数据库  tel_sale_mage_system ===>  TSMS

2、创建表空间
1)系统管理表空间
DROP TABLESPACE tsms_mang INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE tsms_mang LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_mang_01.ora' SIZE 10M  AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_mang_02.ora' SIZE 10M  AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

   
--EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO   ;

2)数据表空间 
---一个表空间多个同时建立的文件,基本是平均写入数据。

DROP TABLESPACE tsms_data_A INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE tsms_data_A LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_a_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_a_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_a_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_a_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_a_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_a_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


DROP TABLESPACE TSMS_data_B INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE tsms_data_B LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_b_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_b_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_b_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_b_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_b_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_b_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

DROP TABLESPACE TSMS_data_C INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE TSMS_data_C LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_c_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_c_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_c_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_c_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_c_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_c_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

DROP TABLESPACE TSMS_data_D INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE TSMS_data_D LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_d_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_d_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_d_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_d_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_d_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_d_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

DROP TABLESPACE TSMS_data_E INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE TSMS_data_E LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_e_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_e_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_e_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_e_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_e_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_e_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

DROP TABLESPACE TSMS_data_F INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE TSMS_data_F LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_f_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_f_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_f_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_f_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_f_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_f_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

3)系统临时表空间组
alter database tsms default temporary tablespace temp;
drop tablespace tsms_group including contents and datafiles;


DROP TABLESPACE tsms_temp_group1 INCLUDING CONTENTS CASCADE CONSTRAINTS;
create temporary tablespace tsms_temp_group1 tempfile
     'D:\install\oracle\oradata\tsms\tsms_temp_group1_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_temp_group1_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,   
     'D:\install\oracle\oradata\tsms\tsms_temp_group1_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
     EXTENT MANAGEMENT LOCAL
tablespace group tsms_group;

DROP TABLESPACE tsms_temp_group2 INCLUDING CONTENTS CASCADE CONSTRAINTS;
create temporary tablespace tsms_temp_group2 tempfile
     'D:\install\oracle\oradata\tsms\tsms_temp_group2_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_temp_group2_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,   
     'D:\install\oracle\oradata\tsms\tsms_temp_group2_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
     EXTENT MANAGEMENT LOCAL
tablespace group tsms_group;

alter database default temporary tablespace tsms_group ;

4)为建立的临时处理表空间
DROP TABLESPACE tsms_data_temp INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE tsms_data_temp LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_temp_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_temp_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_temp_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_temp_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_temp_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_temp_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

5)表索引表空间
DROP TABLESPACE tsms_data_idx1 INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE tsms_data_idx1 LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_idx1_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx1_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx1_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx1_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx1_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx1_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

DROP TABLESPACE tsms_data_idx2 INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE tsms_data_idx2 LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_data_idx2_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx2_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx2_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx2_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx2_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_data_idx2_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


5)创建原数据区(从核心或外部进来的数据)
DROP TABLESPACE tsms_sdata INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE tsms_sdata LOGGING
    DATAFILE
     'D:\install\oracle\oradata\tsms\tsms_sdata_01.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_sdata_02.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_sdata_03.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_sdata_04.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_sdata_05.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M,
     'D:\install\oracle\oradata\tsms\tsms_sdata_06.ora' SIZE 10M AUTOEXTEND ON NEXT 32M MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


查看表空间是否具有自动扩展的能力
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

 

2、创建USER
1)--创建系统管理用户DBA
drop user tsms cascade;
create user tsms identified by tsms
 default tablespace tsms_data_A
 temporary tablespace tsms_group;

grant dba to tsms;
ALTER USER tsms QUOTA UNLIMITED ON tsms_data_A;
ALTER USER tsms QUOTA UNLIMITED ON tsms_data_B;
ALTER USER tsms QUOTA UNLIMITED ON tsms_data_C;
ALTER USER tsms QUOTA UNLIMITED ON tsms_data_D;
ALTER USER tsms QUOTA UNLIMITED ON tsms_data_E;
ALTER USER tsms QUOTA UNLIMITED ON tsms_data_F;

ALTER USER tsms QUOTA UNLIMITED ON tsms_sdata ;

ALTER USER tsms QUOTA UNLIMITED ON tsms_data_idx1;
ALTER USER tsms QUOTA UNLIMITED ON tsms_data_idx2;


2)创建系统开发用户,只在tsms_data_temp上具有创建表的权限;

drop user tsms_dev cascade;
create user tsms_dev identified by tsms_dev
 default tablespace tsms_data_temp
 temporary tablespace temp;

grant create session,create trigger,create procedure, create sequence,create public synonym, drop public synonym  to tsms_dev;
grant delete any table to tsms_dev;
grant insert any table to tsms_dev;
grant select any table to tsms_dev;
grant update any table to tsms_dev;
grant create database link to tsms_dev;
grant create table   to tsms_dev   ;

grant lock any table  to tsms_dev   ;


ALTER USER tsms_dev QUOTA 1 ON tsms_data_A;
ALTER USER tsms_dev QUOTA 1 ON tsms_data_B;
ALTER USER tsms_dev QUOTA UNLIMITED ON tsms_data_temp;
ALTER USER tsms_dev QUOTA 1 ON tsms_data_idx;
ALTER USER tsms_dev QUOTA 1 ON tsms_mang;
ALTER USER tsms_dev QUOTA 1 ON tsms_sdata;      

 

create table adssd (aa varchar(10)) tablespace tsms_data_temp ;

drop table ADASD;

 

二、初始化参数设置注意事项

http://tech.cncms.com/shujuku/oracle/18349_2.html 

三、CREATE TABLESPACE命令详解(转)http://www.cnblogs.com/Annie/articles/1280893.html

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11949963/viewspace-730180/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-05-16

  • 博文量
    26
  • 访问量
    28603