• 安插Oracle后,常常运用的批改表空间的SQL代码


     来源:网海拾贝




    设置装备安插:
    Windows NT 4.0 中文版
    5块10.2GB SCSI硬盘
    分:C:盘、D:盘、E:盘、F:盘、G:盘
    Oracle 8.0.4 for Windows NT
    NT安插在C:WINNT,Oracle安插在C:ORANT

    目标:
    因细碎的回滚段太小,现狡计生成新的回滚段,
    设立树立大的、新的表空间(数据表空间、索引表空间、回滚表空间、临时表空间、)
    建两个数据表空间、两个索引表空间,多么建的目标是根据实际运用,
    如:现有10个运用用户,每个用户是一个自力子细碎(如:商业进销存MIS细碎中的财政、收款、库存、人事、总经理等)
    尤其大型商场中收款机众多,同时造访历程很多,常常到达50-100个历程同时造访,
    多么,经过设立树立多个用户表空间、索引表空间,把各个用户鉴识建在分歧的表空间里(多个用户表空间放在分歧的物理磁盘上),
    增添了用户之间的I/O竞争、读写数据与写读索引的竞争(用户表空间、索引表空间也鉴识放在分歧的物理磁盘上)

    狡计:
    C:盘、NT细碎,Oracle细碎
    D:盘、数据表空间1(3GB、主动扩展)、回滚表空间1(1GB、主动扩展)
    E:盘、数据表空间2(3GB、主动扩展)、回滚表空间2(1GB、主动扩展)
    F:盘、索引表空间1(2GB、主动扩展)、临时表空间1(0.5GB、不主动扩展)
    G:盘、索引表空间2(2GB、主动扩展)、临时表空间2(0.5GB、不主动扩展)

    注:这只是一个简单的狡计,实际狡计要依细碎需求来定,虽然即便增添I/O竞争

    完成:
    1、首先查抄细碎有哪些回滚段及其形态。

    SQL> col owner format a20
    SQL> col status format a10
    SQL> col segment_name format a20
    SQL> col tablespace_name format a20

    SQL> SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M
      2  FROM DBA_SEGMENTS
      3  WHERE SEGMENT_TYPE='ROLLBACK'
      4  GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME
      5  /

    OWNER                SEGMENT_NAME         TABLESPACE_NAME              M
    -------------------- -------------------- -------------------- ---------
    SYS                  RB1                  ROLLBACK_DATA        .09765625
    SYS                  RB10                 ROLLBACK_DATA        .09765625
    SYS                  RB11                 ROLLBACK_DATA        .09765625
    SYS                  RB12                 ROLLBACK_DATA        .09765625
    SYS                  RB13                 ROLLBACK_DATA        .09765625
    SYS                  RB14                 ROLLBACK_DATA        .09765625
    SYS                  RB15                 ROLLBACK_DATA        .09765625
    SYS                  RB16                 ROLLBACK_DATA        .09765625
    SYS                  RB2                  ROLLBACK_DATA        .09765625
    SYS                  RB3                  ROLLBACK_DATA        .09765625
    SYS                  RB4                  ROLLBACK_DATA        .09765625
    SYS                  RB5                  ROLLBACK_DATA        .09765625
    SYS                  RB6                  ROLLBACK_DATA        .09765625
    SYS                  RB7                  ROLLBACK_DATA        .09765625
    SYS                  RB8                  ROLLBACK_DATA        .09765625
    SYS                  RB9                  ROLLBACK_DATA        .09765625
    SYS                  RB_TEMP              SYSTEM               .24414063
    SYS                  SYSTEM               SYSTEM                .1953125

    查询到18记录.

    SQL> SELECT SEGMENT_NAME,OWNER,
      2         TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS
      3         FROM DBA_ROLLBACK_SEGS
      4  /

    SEGMENT_NAME         OWNER                TABLESPACE_NAME      SEGMENT_ID   FILE_ID STATUS
    -------------------- -------------------- -------------------- ---------- --------- ----------
    SYSTEM               SYS                  SYSTEM                        0         1 ONLINE
    RB_TEMP              SYS                  SYSTEM                        1         1 OFFLINE
    RB1                  PUBLIC               ROLLBACK_DATA                 2         3 ONLINE
    RB2                  PUBLIC               ROLLBACK_DATA                 3         3 ONLINE
    RB3                  PUBLIC               ROLLBACK_DATA                 4         3 ONLINE
    RB4                  PUBLIC               ROLLBACK_DATA                 5         3 ONLINE
    RB5                  PUBLIC               ROLLBACK_DATA                 6         3 ONLINE
    RB6                  PUBLIC               ROLLBACK_DATA                 7         3 OFFLINE
    RB7                  PUBLIC               ROLLBACK_DATA                 8         3 OFFLINE
    RB8                  PUBLIC               ROLLBACK_DATA                 9         3 OFFLINE
    RB9                  PUBLIC               ROLLBACK_DATA                10         3 OFFLINE
    RB10                 PUBLIC               ROLLBACK_DATA                11         3 OFFLINE
    RB11                 PUBLIC               ROLLBACK_DATA                12         3 OFFLINE
    RB12                 PUBLIC               ROLLBACK_DATA                13         3 OFFLINE
    RB13                 PUBLIC               ROLLBACK_DATA                14         3 OFFLINE
    RB14                 PUBLIC               ROLLBACK_DATA                15         3 OFFLINE
    RB15                 PUBLIC               ROLLBACK_DATA                16         3 OFFLINE
    RB16                 PUBLIC               ROLLBACK_DATA                17         3 OFFLINE

    查询到18记录.

    2、批改代码如下,可把以下代码存入一.sql文件,如cg_sys.sql,然后以SQL> @cg_sys.sql调用实行。

    --详细:各个硬盘上要事前建好oradata目次

    --批改现有回滚段,使之失效,下线
    alter rollback segment rb1 offline;
    alter rollback segment rb2 offline;
    alter rollback segment rb3 offline;
    alter rollback segment rb4 offline;
    alter rollback segment rb5 offline;
    alter rollback segment rb6 offline;
    alter rollback segment rb7 offline;
    alter rollback segment rb8 offline;
    alter rollback segment rb9 offline;
    alter rollback segment rb10 offline;
    alter rollback segment rb11 offline;
    alter rollback segment rb12 offline;
    alter rollback segment rb13 offline;
    alter rollback segment rb14 offline;
    alter rollback segment rb15 offline;
    alter rollback segment rb16 offline;

    --删除原有回滚段
    drop rollback segment rb1;
    drop rollback segment rb2;
    drop rollback segment rb3;
    drop rollback segment rb4;
    drop rollback segment rb5;
    drop rollback segment rb6;
    drop rollback segment rb7;
    drop rollback segment rb8;
    drop rollback segment rb9;
    drop rollback segment rb10;
    drop rollback segment rb11;
    drop rollback segment rb12;
    drop rollback segment rb13;
    drop rollback segment rb14;
    drop rollback segment rb15;
    drop rollback segment rb16;

    --建数据表空间1
    --收款、库存、订货、远程通信
    create tablespace USER_DATA1 datafile
    'd:oradatauser1_1.ora' size 512M,
    'd:oradatauser1_2.ora' size 512M,
    'd:oradatauser1_3.ora' size 512M,
    'd:oradatauser1_4.ora' size 512M,
    'd:oradatauser1_5.ora' size 512M,
    'd:oradatauser1_6.ora' size 512M
    AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
    default storage (initial 128K next 2M pctincrease 0);
    --initial 128K,因为,用户建在表空间上,而表建在用户里,为用户所拥有,
    --用户秉承数据表空间的存储参数,表秉承用户的存储参数
    --假定initial设的过大,如:5M,则每建一个空表就要占用5M的空间,即便一条记录也没有
    --AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,设置数据文件主动扩展,每一次扩展添加5M,最大空间不受限

    --建数据表空间2
    --物价、人事、结算、财政、总经理、公约、统计
    create tablespace USER_DATA2 datafile
    'e:oradatauser2_1.ora' size 512M,
    'e:oradatauser2_2.ora' size 512M,
    'e:oradatauser2_3.ora' size 512M,
    'e:oradatauser2_4.ora' size 512M,
    'e:oradatauser2_5.ora' size 512M,
    'e:oradatauser2_6.ora' size 512M
    AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
    default storage (initial 128K next 2M pctincrease 0);

    --建索引表空间1
    create tablespace INDEX_DATA1 datafile
    'f:oradataindex1_1.ora' size 512M,
    'f:oradataindex1_2.ora' size 512M,
    'f:oradataindex1_3.ora' size 512M,
    'f:oradataindex1_4.ora' size 512M
    AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
    default storage (initial 128K next 2M pctincrease 0);

    --建索引表空间2
    create tablespace INDEX_DATA2 datafile
    'g:oradataindex2_1.ora' size 512M,
    'g:oradataindex2_2.ora' size 512M,
    'g:oradataindex2_3.ora' size 512M,
    'g:oradataindex2_4.ora' size 512M
    AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
    default storage (initial 128K next 2M pctincrease 0);

    --建回滚表空间1
    --设置初始值40M(initial 40M),则每在这个表空间中建一个回滚段,
    --此回滚段主动秉承此回滚表空间的存储参数,也即默许文件为40M
    create tablespace ROLLBACK_DATA1 datafile
    'd:oradataroll1_1.ora' size 512M,
    'd:oradataroll1_2.ora' size 512M
    AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
    default storage (initial 40M next 5M pctincrease 0);

    --建回滚表空间2
    create tablespace ROLLBACK_DATA2 datafile
    'e:oradataroll2_1.ora' size 512M,
    'e:oradataroll2_2.ora' size 512M
    AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
    default storage (initial 40M next 5M pctincrease 0);

    --建临时表空间1
    create tablespace TEMPORARY_DATA1 datafile
    'f:oradatatemp1_1.ora' size 512M
    default storage (initial 10M next 3M pctincrease 0);

    --建临时表空间2
    create tablespace TEMPORARY_DATA2 datafile
    'g:oradatatemp2_1.ora' size 512M
    default storage (initial 10M next 3M pctincrease 0);

    --使其真正成为临时的
    alter tablespace TEMPORARY_DATA1 temporary;
    alter tablespace TEMPORARY_DATA2 temporary;

    --设立树立新的回滚段,每个都一样大,分歧大小的回滚段没有什么意义,细碎是随机选择的。
    --建几何个,根据并发造访用户的几何,
    --假定你们公司每天有50-100个人员运用Oracle细碎开辟的办理软件,应该20个以上

    create public rollback segment rb01 tablespace rollback_data1;
    create public rollback segment rb02 tablespace rollback_data1;
    create public rollback segment rb03 tablespace rollback_data1;
    create public rollback segment rb04 tablespace rollback_data1;
    create public rollback segment rb05 tablespace rollback_data1;
    create public rollback segment rb06 tablespace rollback_data1;
    create public rollback segment rb07 tablespace rollback_data1;
    create public rollback segment rb08 tablespace rollback_data1;
    create public rollback segment rb09 tablespace rollback_data2;
    create public rollback segment rb10 tablespace rollback_data2;
    --前8个建在回滚表空间1中,后8个在回滚表空间2
    create public rollback segment rb11 tablespace rollback_data2;
    create public rollback segment rb12 tablespace rollback_data2;
    create public rollback segment rb13 tablespace rollback_data2;
    create public rollback segment rb14 tablespace rollback_data2;
    create public rollback segment rb15 tablespace rollback_data2;
    create public rollback segment rb16 tablespace rollback_data2;
    create public rollback segment rb17 tablespace rollback_data2;
    create public rollback segment rb18 tablespace rollback_data2;
    create public rollback segment rb19 tablespace rollback_data2;
    create public rollback segment rb20 tablespace rollback_data2;

    --使回滚段online,即有用
    alter rollback segment rb01 online;
    alter rollback segment rb02 online;
    alter rollback segment rb03 online;
    alter rollback segment rb04 online;
    alter rollback segment rb05 online;
    alter rollback segment rb06 online;
    alter rollback segment rb07 online;
    alter rollback segment rb08 online;
    alter rollback segment rb09 online;
    alter rollback segment rb10 online;
    alter rollback segment rb11 online;
    alter rollback segment rb12 online;
    alter rollback segment rb13 online;
    alter rollback segment rb14 online;
    alter rollback segment rb15 online;
    alter rollback segment rb16 online;
    alter rollback segment rb17 online;
    alter rollback segment rb18 online;
    alter rollback segment rb19 online;
    alter rollback segment rb20 online;

    --查抄现有回滚段及其形态
    col segment format a30
    SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

    --查抄数据文件及其地点表空间、大小、形态
    col file_name format a40
    col tablespace_name format a20
    select file_name,file_id,tablespace_name,bytes,status from dba_data_files;


    至此,表空间从新狡计终了,这里讲的比力简易,尚有很多几何参数值得设置,可以大概把Oracle设置到最优的境地,
    表空间设置完了,下面,就该好好的整顿一下Oracle的内存区了,
    Oracle很居心思,内存越大,效果越光鲜明显,所以有需求好好调停一下SGA区,也就是首要设置装备安插ininorcl.ora参数文件。




    版权声明: 原创作品,许可转载,转载时请务必以超链接体式技俩标明文章 原始出处 、作者信息和本声明。否则将清查执法责任。

  • 相关阅读:
    vscode 自动换行 关闭
    QGIS geojson 矢量操作 稀释 抽稀 压缩 边界抽稀
    vscode 关闭烦人的 tooltip 提示
    sql函数汉字转拼音
    sql 排名函数
    客户端文件下载 download.js
    Oracle 创建 DBLink 的方法
    RDLC分组序号
    sql server中使用函数验证身份证号码是否合法
    oracle导出数据库exp时报错,ORA12154 TNS: 无法解析指定的连接标识符
  • 原文地址:https://www.cnblogs.com/zgqjymx/p/1975851.html
Copyright © 2020-2023  润新知