精华内容
下载资源
问答
  • Oracle数据库备份与恢复数据_包(命名空间) 包头与包体的书写案例

    备份与恢复数据_包(命名空间)

     

    Ø  备份与还原

     

    此命令不能在sqlplus里面执行,要在cmd下面执行,并且备份数据之前要将路径切换到Oracle的bin目录下。

     

    导出数据:exp userid=cz/123456@orcl tables=(emp)file=E:\A.DMP

     

    导入数据:  imp userid=cz/123456@orcl file=E:\A.DMP(恢复已经删除的数据)

     

    ignore=y(如果删除的只是数据,不是一张表的时候要用到)

     

     

    Ø  Oracle里面的包(也就是命名空间)

     

    C#中的名称空间,Java里面的包

    Java里面的包:Import 包

    C#中的名称空间: 名称空间

     

     

    包的作用:防止名称重复

    把一些相同的功能放到一个包里面去

     

     

    包:包的申明和包体

     

     

    包头:申明不是具体的实现

    createorreplacepackage dmbs_outputm

    is

        unction put_line(p nvarchar2)returnnvarchar2;

        unction put_line(p nvarchar2,p2 nvarchar2)returnnvarchar2;

    end dmbs_outputm;

     

    包体:具体的实现

    createorreplacepackagebody dmbs_outputm

    is

     function put_line(p nvarchar2)

       returnnvarchar2

       is

       begin

         return p;

       end;

     

     function put_line(p nvarchar2,p2 nvarchar2)

       returnnvarchar2

       is

       begin

         return p||p2;

       end;

    end dmbs_outputm;

     

     

    包体要在包头执行后立马执行

    展开全文
  • oracle创建表空间

    千次阅读 2021-01-27 09:10:12
    不同于mysql有多个数据库, oracle新增了表空间和用户的概念,表空间是虚拟的,并且只有一个数据库,为了方便维护,一般一个表空间上只创建一个用户; 这里的用户名类似于mysql的数据库名,oracle的用户名.表名等价...

    转至:https://liujianshiwo.iteye.com/blog/1988094

    不同于mysql有多个数据库, oracle新增了表空间和用户的概念,表空间是虚拟的,并且只有一个数据库,为了方便维护,一般一个表空间上只创建一个用户;

    这里的用户名类似于mysql的数据库名,oracle的用户名.表名等价于mysql的数据库名.表名;因此在写兼容mysql和oracle的语句时可以用到。

    oracle安装完数据库的一般流程:先建表空间(分区)–>再建用户(分配相应的表空间和用户权限)–>登陆用户,建表

    1. 创建表空间
      create tablespace xin_datafile
      datafile ‘C:\kingstar\orcltb\xin_tb1.dbf’ size 500M --存储地址 初始大小500M
      autoextend on next 10M maxsize 200M --每次扩展10M,无限制扩展 unlimited
      extent management local autoallocate
      segment space management auto;

    –创建临时表空间
    create temporary tablespace user_temp
    tempfile ‘D:\oracle\oradata\Oracle9i\user_temp.dbf’ size 50m
    autoextend on next 50m maxsize 20480m
    extent management local;

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

    –表空间属性

    每块的字节大小:8192

    表空间自增量:increment_by8192 byte ; 换算: increment_by8192/1024/1024 M 或者 increment_by/128M

    注意*:若表空间初始化大小为500M,阀值为1G,自增量为300M,自增一次后的表空间大小为800M,不会受自动扩展容量的限制,最终会扩展为与阀值相等。

    –查询表空间信息

    select * from dba_data_files t order by tablespace_name;

    –建立UNDO表空间

    CREATE UNDO TABLESPACE UNDOTBS02
    DATAFILE ‘/oracle/oradata/db/UNDOTBS02.dbf’ SIZE 50M
    #注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间:
    ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

    –改变表空间状态

    1.使表空间脱机
    ALTER TABLESPACE game OFFLINE;

    如果是意外删除了数据文件,则必须带有RECOVER选项
    ALTER TABLESPACE game OFFLINE FOR RECOVER;

    2.使表空间联机
    ALTER TABLESPACE game ONLINE;

    3.使数据文件脱机
    ALTER DATABASE DATAFILE 3 OFFLINE;

    4.使数据文件联机
    ALTER DATABASE DATAFILE 3 ONLINE;

    5.使表空间只读
    ALTER TABLESPACE game READ ONLY;

    6.使表空间可读写
    ALTER TABLESPACE game READ WRITE;

    –扩展表空间

    首先查看表空间的名字和所属文件
    select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

    1.增加数据文件
    ALTER TABLESPACE game ADD DATAFILE ‘/oracle/oradata/db/GAME02.dbf’ SIZE 1000M;

    2.手动增加数据文件尺寸
    ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf’ RESIZE 4000M;

    3.设定数据文件自动扩展
    ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;

    设定后查看表空间信息
    SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES100)/A.BYTES “% USED”,(C.BYTES100)/A.BYTES “% FREE”
    FROM SYS.SM T S A V A I L A , S Y S . S M TS_AVAIL A,SYS.SM TSAVAILA,SYS.SMTS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

    2.创建用户

    create user test identified by test

    default tablespace SIRM2

    temporary tablespace TEMP
    profile DEFAULT;

    –创建用户并指定表空间
    create user username identified by password
    default tablespace user_data
    temporary tablespace user_temp;

    –删除用户命令
    drop user user_name cascade;

    3.授权

    grant dba to test;
    grant connect to test;
    grant resource to test;

    –给用户授予权限
    grant connect,resource to username;
    撤权:
    revoke 权限… from 用户名;

    1. SYS用户在CMD下以DBA身份登陆:
      在CMD中打sqlplus /nolog
      然后再 conn / as sysdba

    附加1:

    oarcle数据库真正存放数据的是数据文件(data files),Oarcle表空间(tablespaces)实际上是一个逻辑的概念,他在物理上是并不存在的,那么把一组data files 捻在一起就成为一个表空间。(https://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html)

    表空间属性:

    一个数据库可以包含多个表空间,一个表空间只能属于一个数据库

    一个表空间包含多个数据文件,一个数据文件只能属于一个表空间

    表这空间可以划分成更细的逻辑存储单元

    Oracle数据库的存储结构:

    从逻辑的角度来看,一个数据库(database)下面可以分多个表空间(tablespace);一个表空间下面又可以分多个段(segment);一个数据表要占一个段(segment),一个索引也要占一个段(segment )。 一个段(segment)由多个 区间(extent)组成,那么一个区间又由一组连续的数据块(data block)组成。这连续的数据块是在逻辑上是连续的,有可能在物理磁盘上是分散。

    那么从物理的角度上看,一个表空间由多个数据文件组成,数据文件是实实在在存在的磁盘上的文件。这些文件是由oracle数据库操作系统的block 组成的。

    Segment(段) :段是指占用数据文件空间的通称,或数据库对象使用的空间的集合;段可以有表段、索引段、回滚段、临时段和高速缓存段等。

    Extent (区间):分配给对象(如表)的任何连续块叫区间;区间也叫扩展,因为当它用完已经分配的区间后,再有新的记录插入就必须在分配新的区间(即扩展一些块);一旦区间分配给某个对象(表、索引及簇),则该区间就不能再分配给其它的对象.

    附加2:oracle 表空间创建 格式及属性说明:http://blog.sina.com.cn/s/blog_3cae66560102w0vk.html

    一、创建表空间完整的格式

    CREATE [UNDO|TEMPORARY] TABLESPACE tablespace_name

     DATAFILE 'path/filename' [SIZE INTEGER[K| M]]  [REUSE] 
    
     [AUTOEXTEND [OFF | ON ] ]
    
     [NEXT INTEGER[K | M]]
    
     [MAXSIZE [UNLIMITED | INTEGER[K | M]]]
    
     [MINIMUM EXTENT INTEGER[K | M]]
    
     [BLOCKSIZE integer [k]]
    
     [DEFAULT STORAGE storage_clause]
    
     [ONLINE | OFFLINE]
    
     [LOGGING | NOLOGGING]
    
     [FORCE LOGGING]
    
     [EXTENT MANAGEMENT [DICTIONARY | LOCAL]]
    
     [AUTOALLOCATE | UNIFORM [SIZE INTEGER[K | M]]]
    
     [SEGMENT SPACE MANAGEMENT [AUTO | MANUAL]]
    

    二、表空间格式说明

    1、UNDO

      说明系统将创建一个回滚表空间。
    
       在9i中数据库管理员可以不必管理回滚段,只有建立了undo表空间,系统就会自动管理回滚段的分配,回收的工作。当然,也可以创建一般的表空间,在上         面创建回滚段.不过对于用户来说,系统管理比自己管理要好很多.如果需要自己管理,请参见回滚段管理的命令详解.
    
       当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。
    

    2、tablespace_name表空间名满足oracle的命名规范即可。

    3、DATAFILE ‘path/filename’ [SIZE INTEGER[K| M]] [REUSE]

      指定一个或多个数据文件路径和文件名,[SIZE INTEGER[K| M]]用来指定表空间在数据文件中所占的空间大小,K|M是说大小的单位是K还是M。
    
      [REUSE] 如果创建表空间指定的数据文件已经存在,则需要使用reuse注明,否则会报错。
    
      ERROR at line 1:
    

    ORA-01537: cannot add data file ‘E:\oracle\oradata\10.1.0\ZRPTEST\TB_TEST1.dbf’

    • file already part of database

    4、[AUTOEXTEND [OFF | ON ] ]

      禁止或允许自动扩展数据文件,默认情况下为OFF
    

    5、[NEXT INTEGER[K | M]]

      指定当需要更多盘区时分配给数据文件的磁盘空间,以K或M为单位。
    

    6、[MAXSIZE [UNLIMITED | INTEGER[K | M]]]

      指定允许分配给数据文件的最大磁盘空间,当为UNLIMITED时表示只有当磁盘空间满时,才不允许再扩展数据文件。
    

    7、[MINMUM EXTENT INTEGER[K | M]]

      指定盘区大小是指定大小的整数倍。即initial和next盘区大小应为minimum extent的整数倍,这个参数可以减小空间碎片,保证表空间的范围是这个值的整数倍。
    
      【注:minextents 用于指定在创建时分配给段的盘区总数。使用这个参数,即使可用空间是不连续的,在创建对象时也能分配很大的空间。默认                    值和最小值都是1。如果minextents>1,盘区大小以next和pctincrease为基础计算。】
    

    8、[BLOCKSIZE integer [k]]

        这个参数可以设定一个不标准的块的大小。如果要设置这个参数,必须设置db_block_size,至少一个db_nk_block_size,
    
        并且声明的integer的值必须等于db_nk_block_size.
    
       注意:在临时表空间不能设置这个参数。
    

    9、 [DEFAULT STORAGE storage_clause]

       表示为在该表空间创建的全部对象指定缺省的存储参数,没有指定时oracle将会为所有对象指定一些系统默认的存储参数。
    
       storage_clause的语法格式如下:
    
       STORAGE(
    
            INITIAL integer[K|M]       --为段分配第一个区的大小
    
            NEXT     integer[K|M]       --为第一个扩展分区的大小
    
            MINEXTENTS integer|UMLIMITED      --创建段时分配的最小总区间数
    
            MAXEXTENTS integer|UMLIMITED     --创建段时分配的最大总区间数
    
            PCTINCREASE integer                           --每次扩展增量的大小
    
            FREELISTS integer                                  --模式对象中每一个自由列表组中自由列表的数量,表、族或索引的每个空闲列表组中的列表数量。
    
            FREELISTS GROUPS integer                  --指定表、族或索引的每个空闲列表组数量。
    
         )
    

    10、[ONLINE | OFFLINE]

      ONLINE是指在创建表空间之后使该表空间立即对授权访问该表空间的用户可用。
    
      OFFLINE是指offline以后,未完成的事物可以提交或回滚,但不能发起新的事物,也不能进行查询。OFFLINE的四种模式
    
       (1)NORMAL:做检查点 
    
                 alter tablespace users  offline;           alter tablespace users online;
    
       (2)TEMPORARY:可以在数据文件损坏的情况下offline tablespace
    
                 alter tablespace users offline temporary;   alter tablespace users online;
    
       (3)IMMEDIATE:不做检查点,只有在归档模式下才可以offline tablespace,online时需要recover
    
                 alter tablespace users offline immediate;    recover tablespace users;       alter tablespace users online;
    
        (4)FOR RECOVER:在归档模式下才可以offonline,在基
    

    11、[LOGGING | NOLOGGING]

      指定日志属性,它表示将来的表、索引等是否需要进行日志处理。默认值为LOGGING
    

    12、[FORCE LOGGING]

       使用这个子句指出表空间进入强制日志模式。此时,系统将记录表空间上对象的所有改变,除了临时段的改变。这个参数高于对象的nologging选项。
    
       注意:设置这个参数指数据库不可以open并且进入读写模式。而且,在临时表空间和回滚表空间中不能使用这个选项。
    

    13、[PERMANENT | TEMPORARY]

       指出表空间的属性,是永久表空间还是临时表空间。永久表空间存放的是永久对象,临时表空间存放的是session生命期中存在的临时对象。
    
       这个参数生成的临时表空间创建后一直都是字典管理,不能使用extent management local选项。
    
       如果要创建本地管理表空间,必须使用create temporary tablespace
    
       注意,声明了这个参数后,不能声明block size
    

    14、[EXTENT MANAGEMENT [DICTIONARY | LOCAL]]

        [AUTOALLOCATE | UNIFORM [SIZE INTEGER[K | M]]]
    
        这是最重要的子句,说明了表空间如何管理范围。一旦你声明了这个子句,只能通过移植的方式改变这些参数。
    
        如果希望表空间本地管理的话,声明local选项。本地管理表空间是通过位图管理的。autoallocate说明表空间自动分配范围,用户不能指定范围的大小。
    
        只有9.0以上的版本具有这个功能。uniform说明表空间的范围的固定大小,缺省是1m。
    
         不能将本地管理的数据库的system表空间设置成字典管理。oracle公司推荐使用本地管理表空间。
    
         如果没有设置extent_management_clause,oracle会给他设置一个默认值。如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。
    
          如果大于9.0.0,那么按照如下设置:如果没有指定default storage_clause,oracle创建一个自动分配的本地管理表空间。
    
         否则,如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果满足以上的条件,oracle创建一个本地管理
    
          表空间,extent size是initial.如果不满足以上条件,那么oracle将创建一个自动分配的本地管理表空间。
    
          如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。表示满足oracle创建一个本地管理表空间并指定uniform。
    
           否则oracle将创建一个自动分配的本地管理表空间。
    
          注意:本地管理表空间只能存储永久对象。如果你声明了local,将不能声明default storage_clause,mininum extent、temporary.
    

    15、[SEGMENT SPACE MANAGEMENT [AUTO | MANUAL]]

       指定 表空间段的管理方式,AUTO为系统自动管理,MANUAL为手工管理。默认为AUTO。
    

    附加3:Oracle中分区表中表空间属性:https://blog.51cto.com/hbxztc/1929585

    展开全文
  • Oracle创建表空间和表

    2019-11-26 09:13:24
    创建表空间和表 ...存放数据总是需要空间Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式,所以一盘一个数据表空间包含一个以上的物理文件 数据表 在仓库,我们可能...

    创建表空间和表
    ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的
    oracle中的表就是一张存储数据的表。表空间是逻辑上的划分。方便管理的。
    数据表空间 (Tablespace)
    存放数据总是需要空间, Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式,所以一盘一个数据表空间包含一个以上的物理文件
    数据表
    在仓库,我们可能有多间房子,每个房子又有多个货架,每架又有多层。 我们在数据库中存放数据,最终是数据表的单元来存储与管理的。
    数据文件
    以上几个概念都是逻辑上的, 而数据文件则是物理上的。就是说,数据文件是真正“看得着的东西”,它在磁盘上以一个真实的文件体现
    1、创建表空间:
    格式: create tablespace 表间名 datafile ‘数据文件名’ size 表空间大小
    create tablespace data_test datafile ‘e:\oracle\oradata\test\data_1.dbf’ size 2000M;
    create tablespace idx_test datafile ‘e:\oracle\oradata\test\idx_1.dbf’ size 2000M;
    (*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆)
    2、建好tablespace, 就可以建用户了
    格式: create user 用户名 identified by 密码 default tablespace 表空间表;
    create user study identified by study default tablespace data_test;
    (*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.)
    (*缺省表空间表示 用户study今后的数据如果没有专门指出,其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中)

    创建用户并指定表空间
    CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;
    create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;
    授权给新用户
    GRANT connect, resource TO cici;
    grant create session to cici;
    4. 授权给新用户
    grant connect,resource to study;
    –表示把 connect,resource权限授予study用户
    grant dba to study;
    –表示把 dba权限授予给 study
    5. 创建数据表
    在上面,我们已建好了用户 study 我们现在进入该用户
    sqlplusw study/study@test 然后就可以在用户study中创建数据表了
    格式: create table 数据表名

    oracle命令建立主键外键

    1、创建一张学生表
    create table t_stu(
    stuid number(10) primary key,
    stuname varchar2(20) not null,
    stusex varchar2(2) default ‘男’ check(stusex in(‘男’,‘女’))
    );

    2、创建一张课程表
    create table t_couse(
    couseid number(10) primary key,
    cousename varchar2(20) not null,
    cousetype varchar2(4)
    );

    3、创建一张学生课程成绩表(包括主外键)
    create table t_score(
    scoreid number(10) primary key,
    stuid number(10) references t_stu(stuid),
    couseid number(10),
    constraint fk_couseid foreign key(couseid)
    references t_couse(couseid)
    on delete cascade
    );

    CREATE TABLE log(
    log_id int(10) unsigned NOT NULL auto_increment,
    log_time datetime NOT NULL,
    log_user varchar(30) NOT NULL,
    log_title varchar(30) default NULL,
    log_content text default NULL,
    PRIMARY KEY(log_id));
    orale表管理:

    Oracle创建表同SQL Server一样,使用CREATE TABLE命令来完成。创建约束则使用如下命令:

    语法格式:alter table命令

    alter table 表名 add constraint 约束名 约束内容。

    不论创建表还是约束,与SQL Server基本相同,注:在Oracle中default是一个值,而SQL Server中default是一个约束,因此Oracle的default设置可以在建表的时候创建。

    案例1:创建一个学生信息(INFOS)表和约束

    Oracle创建表和约束

    create table INFOS(

    STUID varchar2(7) not null, --学号 学号=‘S’+班号+2位序号

    STUNAME varchar2(10) not null, --姓名

    GENDER varchar2(2) not null, --性别

    AGE number(2) not null, --年龄

    SEAT number(2) not null, --座号

    ENROLLDATE date, --入学时间

    STUADDRESS varchar2(50) default ‘地址不详’, --住址

    CLASSNO varchar2(4) not null, --班号 班号=学期序号+班级序号

    );

    / ①

    alter table INFOS add constraint pk_INFOS primary key(STUID) ②

    /

    alter table INFOS add constraint ck_INFOS_gender check(GENDER = ‘男’ or GENDER = ‘女’) ③

    /

    alter table INFOS add constraint ck_INFOS_SEAT check(SEAT >=0 and SEAT <=50) ④

    /

    alter table INFOS add constraint ck_INFOS_AGE check(AGE >=0 and AGE<=100) ⑤

    /

    alter table INFOS add constraint ck_INFOS_CLASSNO check((CLASSNO >=‘1001’ and CLASSNO<=‘1999’) or

    (CLASSNO >=‘2001’ and CLASSNO<=‘2999’)) ⑥

    /

    alter table INFOS add constraint un_STUNAME unique(STUNAME) ⑦

    /

    代码解析:

    ① 在Oracle代码中,“/”执行缓存区中的语句,由于缓冲区中只存储一条刚刚保存过语句,由于每条语句没有用分号结尾,只是保存在缓冲区,因此每条语句后面都有单独一行“/”。

    ② 创建一个主键约束。

    ③ 与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束,表示该列值是唯一的,列中的值不能重复。

    Oracle中创建外键约束与SQL Server相同。比如:现有成绩表定义如下:

    案例2:创建一个成绩表(SCORES)表和约束

    Oracle创建表和约束

    create table scores(

    ID number, --ID ①

    TERM varchar2(2), --学期 S1或S2

    STUID varchar2(7) not null, --学号

    EXAMNO varchar2(7) not null, --考号 E+班号+序号

    WRITTENSCORE number(4,1) not null, --笔试成绩

    LABSCORE number(4,1) not null, --机试成绩

    );

    ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = ‘S1’ OR TERM =‘S2’)

    /

    ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ②

    /

    代码解析:

    ① SQL Server中可以使用identify创建自动增长列,但是Oracle中的自动增长需要借助序列(Sequence)完成,在后面章节中讲解。

    ② Oracle中的外键约束定义。

    注意:表名,字段名要大写!

    oracle建表、建主键、外键基本语法
    -创建表格语法:

    create table 表名(

    字段名1 字段类型(长度) 是否为空,

    字段名2 字段类型 是否为空 );

    -增加主键

    alter table 表名 add constraint 主键名 primary key (字段名1);

    -增加外键:

    alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2);

    在建立表格时就指定主键和外键

    create table T_STU(

    STU_ID char(5) not null,

    STU_NAME VARCHAR2(8) not null,

    constraint PK_T_STU primary key (STU_ID));

    主键和外键一起建立:

    create table T_SCORE(

    EXAM_SCORE number(5,2),

    EXAM_DATE date,

    AUTOID number(10) not null,

    STU_ID char(5),

    SUB_ID char(3),

    constraint PK_T_SCORE primary key (AUTOID),

    constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID));

    orale数据类型:

    类型

    含义

    CHAR(length)

    存储固定长度的字符串。参数length指定了长度,如果存储的字符串长度小于length,用空格填充。默认长度是1,最长不超过2000字节。

    VARCHAR2(length)

    存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1,最长不超过4000字符。

    NUMBER(p,s)

    既可以存储浮点数,也可以存储整数,p表示数字的最大位数(如果是小数包括整数部分和小数部分和小数点,p默认是38为),s是指小数位数。可存负数

    DATE

    存储日期和时间,存储纪元、4位年、月、日、时、分、秒,存储时间从公元前4712年1月1日到公元后4712年12月31日。

    TIMESTAMP

    不但存储日期的年月日,时分秒,以及秒后6位,同时包含时区。

    CLOB

    存储大的文本,比如存储非结构化的XML文档

    BLOB

    存储二进制对象,如图形、视频、声音等。

    1. 创建表时给字段加默认值 和约束条件

    创建表时可以给字段加上默认值 例如 : 日期字段 DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间

    例如:IS_SEND NUMBER(1) default 1 --是否已发

    创建表时可以给字段加上约束条件 例如: 非空 NOT NULL ,不允许重复 UNIQUE ,关键字 PRIMARY KEY ,按条件检查 CHECK (条件), 外键 REFERENCES 表名(字段名)

    1. 创建表的例子

    create table DEPT(
    DNAME varchar2(14),
    LOC varchar2(6),
    EPTNO number(2) constraint PK_DEPT primary KEY,
    );

    create table region(
    ID number(2) not null primary KEY,
    postcode number(6) default ‘0’ not null,
    areaname varchar2(30) default ‘’ not null,
    );

    1. 创建表时的命名规则和注意事项

    1)表名和字段名的命名规则:必须以字母开头,可以含符号A-Z,a-z,0-9,_,$,#

    2)大小写不区分

    3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来

    4)用和实体或属性相关的英文符号长度有一定的限制

    5)约束名的命名规则和语法 约束名的命名规则约束名如果在建表的时候没有指明,系统命名规则是SYS_Cn(n是数字) 约束名字符串的命名规则同于表和字段名的命名规则

    6)使用约束时的注意事项 约束里不能用系统函数,如SYSDATE和别的表的字段比较 可以用本表内字段的比较

    注意事项:

    1)建表时可以用中文的字段名, 但最好还是用英文的字段名

    2)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面

    3)建表时如果有唯一关键字或者唯一的约束条件,建表时自动建了索引

    4)一个表的最多字段个数也是有限制的,254个.

    想在事务处理后, 做约束的

    检查 SQL> alter session set constraints deferred.

    1. 由实体关系图到创建表的例子 s_dept 前提条件:已有region表且含唯一关键字的字段id SQL> CREATE TABLE s_dept (id NUMBER(7) CONSTRAINT s_dept_id_pk PRIMARY KEY, name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL, region_id NUMBER(7) CONSTRAINT s_dept_region_id_fk REFERENCES region (id), CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));

    2. 较复杂的创建表例子 SQL> CREATE TABLE s_emp (id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY, last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2(25), userid VARCHAR2(8) CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE, start_date DATE DEFAULT SYSDATE, comments VARCHAR2(25), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id), salary NUMBER(11,2), commission_pct NUMBER(4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN(10,12.5,15,17.5,20)));

    3. 通过子查询建表 通过子查询建表的例子 SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date FROM s_emp WHERE dept_id = 41;

    SQL> CREATE TABLE A as select * from B where 1=2; 只要表的结构.

    1. 用子查询建表的注意事项 1)可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。 2)用子查询方式建立的表,只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来. 3)根据需要,可以用alter table add constraint ……再建立其它的约束条件,如primary key等.

    2. Foreign Key的可选参数ON DELETE CASCADE 在创建Foreign Key时可以加可选参数: ON DELETE CASCADE它的含义是如果删除外键主表里的内容,子表里相关的内容将一起被删除. 如果没有ON DELETE CASCADE参数,子表里有内容,父表里的主关键字记录不能被删除掉.

    3. 如果数据库表里有不满足的记录存在,建立约束条件将不会成功.

    4. 给表创建和删除同义词的例子 SQL> CREATE SYNONYM d_sum 2 FOR dept_sum_vu;

    SQL> CREATE PUBLIC SYNONYM s_dept 2 FOR alice.s_dept;

    SQL> DROP SYNONYM s_dept;

    ORACLE之新建表
    创建一个名为INSURES的表
    create table INSURES

    (

    INSURE_NO CHAR(18) not null, --医保号

    GETSURE_UNIT_NO CHAR(9) not null, --经办机构号

    INSURE_NAME VARCHAR2(10) not null, --姓名

    INSURE_SEX CHAR(1) not null, --性别

    ID_CARD_NO CHAR(18) not null, --身份证号

    );

    创建/修改主键,唯一性约束和外键 这里INSURE_NO, GETSURE_UNIT_NO唯一性约束

    alter table INSURES

    add constraint UNQ_INSURES unique (INSURE_NO, GETSURE_UNIT_NO)

    alter table TWN_SEED

    add constraint UNQ_INSURES primary key (。。。。)

    创建索引

    create index IDX_INSURES on INSURES (GETSURE_UNIT_NO, SONSURE_UNIT_NO, UNIT_NO, FAMILY_NO, HOSPS_NO)

    创建一个PK的时候,是自动创建一个与之对应的唯一索引的。 如果不特别指定,那么这个索引的表空间和表格的空间是一样的,但是我们不建议放在一起。

    create table testone(

    name varchar2(10 char))

    TABLESPACE1;

    ALTER TABLE TESTONE ADD CONSTRAINT PK_TESTONE1 PRIMARY KEY(NAME) USING INDEX TABLESPACE TABLESPACE2;

    作为一种好习惯,不要把索引和表格的数据存在在同一个表空间中

    Oracle创建表语法 - create
    –创建Oracle表(使用create关键字)
    – ******
    (1)创建新表 use 数据库(在那个数据库中建表) create table 表名
    ( 字段名1(列名) 数据类型 列的特征,
    字段名2(列名) 数据类型 列的特征(NOT NULL),
    … )

    (2)创建带有主键约束的表语法 create table 表名 (
    字段名1(列名) 数据类型 列的特征,
    字段名2 数据类型 列的特征(NOT NULL),
    … primary key(主键列字段))

    (3)利用现有的表创建表 – 注意:仅复制Oracle数据表结构:采用的是子查询方式 create table 新表 as select * from 旧的表 where 1=2
    (4)利用现有的表的结构创建新表 – 注意:仅复制Oracle数据表结构:采用的是子查询方式 create table 新表 select 字段1,字段2… from 旧的表 where 条件(旧的表字段满足的条件)
    (5)利用现有的表的结构创建新表 – 注意:复制Oracle数据表数据 create table 新表 as select * from 旧的表 where 1=1
    (6)利用现有的表的结构创建新表 – 注意:复制Oracle数据表数据 create table 新表 as select 字段1,字段2… from 旧的表 where 条件(旧的表字段满足的条件)(7)将查询结果插入另一张表 insert into 另一张表 select * from 要查询的表 where 条件(要查询的表的列符合什么条件)

    oracle create table(转)

    //建测试表
    create table dept(
    deptno number(3) primary key,
    dname varchar2(10),
    loc varchar2(13)
    );
    create table employee_info(
    empno number(3),
    deptno number(3),
    ename varchar2(10),
    sex char(1),
    phone number(11),
    address varchar2(50),
    introduce varchar2(100)
    );

    重命名
    重命名表:rename dept to dt;
    rename dt to dept;

    重命名列:alter table dept rename column loc to location;
    alter table dept rename column location to loc;
    添加约束

    1. primary key
      alter table employee_info add constraint pk_emp_info primary key(empno);

    2. foreign key
      alter table employee_info add constraint fk_emp_info foreign key(deptno)
      references dept(deptno);

    3. check
      alter table employee_info add constraint ck_emp_info check
      (sex in (‘F’,‘M’));

    4. not null
      alter table employee_info modify phone constraint not_null_emp_info not null;

    5. unique
      alter table employee_info add constraint uq_emp_info unique(phone);

    6. default
      alter table employee_info modify sex char(2) default ‘M’;
      添加列
      alter table employee_info add id varchar2(18);
      alter table employee_info add hiredate date default sysdate not null;

    删除列
    alter table employee_info drop column introduce;
    修改列
    1.修改列的长度
    alter table dept modify loc varchar2(50);

    1. 修改列的精度
      alter table employee_info modify empno number(2);

    2. 修改列的数据类型
      alter table employee_info modify sex char(2);

    3. 修改默认值
      alter table employee_info modify hiredate default sysdate+1;

    5.禁用约束
    alter table employee_info disable constraint uq_emp_info;

    6.启用约束
    alter table employee_info enable constraint uq_emp_info;
    7.延迟约束
    alter table employee_info drop constraint fk_emp_info;
    alter table employee_info add constraint fk_emp_info foreign key(deptno)
    references dept(deptno)
    deferrable initially deferred;
    8.向表中添加注释
    comment on table employee_info is ‘information of employees’;

    9.向列添加注释
    comment on column employee_info.ename is ‘the name of employees’;
    comment on column dept.dname is ‘the name of department’;
    10.清除表中所有数据
    truncate table employee_info; (DELETE FROM table_name或DELETE * FROM table_name)
    11.删除表
    drop table employee_info;

    //下面来看看刚刚才我们对表dept和表employee_info所做的更改
    //user_constraints视图里面包含了刚刚才我们创建的所有约束,以及其他信息,
    //你可以用desc user_constraints命令查看其详细说明
    select constraint_name,constraint_type,status,deferrable,deferred
    from user_constraints
    where table_name=‘EMPLOYEE_INFO’;

    CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED


    PK_EMP_INFO P ENABLED NOT DEFERRABLE IMMEDIATE
    FK_EMP_INFO R ENABLED DEFERRABLE DEFERRED
    NOT_NULL_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
    SYS_C005373 C ENABLED NOT DEFERRABLE IMMEDIATE
    UQ_EMP_INFO U ENABLED NOT DEFERRABLE IMMEDIATE
    CK_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
    //我们可以通过user_cons_columns视图查看有关列的约束信息;
    select owner,constraint_name,table_name,column_name
    from user_cons_columns
    where table_name=‘EMPLOYEE_INFO’;

    OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME


    YEEXUN PK_EMP_INFO EMPLOYEE_INFO EMPNO
    YEEXUN CK_EMP_INFO EMPLOYEE_INFO SEX
    YEEXUN NOT_NULL_EMP_INFO EMPLOYEE_INFO PHONE
    YEEXUN SYS_C005373 EMPLOYEE_INFO HIREDATE
    YEEXUN UQ_EMP_INFO EMPLOYEE_INFO PHONE
    YEEXUN FK_EMP_INFO EMPLOYEE_INFO DEPTNO
    //我们将user_constraints视图与user_cons_columns视图连接起来
    //查看约束都指向哪些列
    column column_name format a15;
    select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status
    from user_constraints uc,user_cons_columns ucc
    where uc.table_name=ucc.table_name and
    uc.constraint_name=ucc.constraint_name and
    ucc.table_name=‘EMPLOYEE_INFO’;

    COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS


    EMPNO PK_EMP_INFO P ENABLED
    DEPTNO FK_EMP_INFO R ENABLED
    PHONE NOT_NULL_EMP_INFO C ENABLED
    HIREDATE SYS_C005373 C ENABLED
    PHONE UQ_EMP_INFO U ENABLED
    SEX CK_EMP_INFO C ENABLED

    //这里有个constraint_type,他具体指下面几种类型:
    //C:check,not null
    //P:primary key
    //R:foreign key
    //U:unique
    //V:check option
    //O:read only

    //我们可以通过user_tab_comments视图获得对表的注释
    select * from user_tab_comments
    where table_name=‘EMPLOYEE_INFO’;
    TABLE_NAME TABLE_TYPE COMMENTS


    EMPLOYEE_INFO TABLE information of employees

    //我们还可以通过user_col_comments视图获得对表列的注释:
    select * from user_col_comments
    where table_name=‘EMPLOYEE_INFO’;

    TABLE_NAME COLUMN_NAME COMMENTS


    EMPLOYEE_INFO EMPNO
    EMPLOYEE_INFO DEPTNO
    EMPLOYEE_INFO ENAME the name of employees
    EMPLOYEE_INFO SEX
    EMPLOYEE_INFO PHONE
    EMPLOYEE_INFO ADDRESS
    EMPLOYEE_INFO ID
    EMPLOYEE_INFO HIREDATE

    select * from user_col_comments
    where table_name=‘EMPLOYEE_INFO’ and
    comments is not null;

    TABLE_NAME COLUMN_NAME COMMENTS


    EMPLOYEE_INFO ENAME the name of employees

    //最后我们来查看一下修改后的表:
    desc employee_info;
    Name Type Nullable Default Comments


    EMPNO NUMBER(2)
    DEPTNO NUMBER(3) Y
    ENAME VARCHAR2(10) Y the name of employees
    SEX CHAR(2) Y ‘M’
    PHONE NUMBER(11)
    ADDRESS VARCHAR2(50) Y
    ID VARCHAR2(18) Y
    HIREDATE DATE sysdate+1

    desc dept;
    Name Type Nullable Default Comments


    DEPTNO NUMBER(3)
    DNAME VARCHAR2(10) Y the name of department
    LOC VARCHAR2(50) Y

      create table test_user (               no          number(5)      not null , --pk               username    varchar2(30)    not null , --用户名               passpord    varchar2(30)    not null , --密码               constraint pk_connectdb primary key(no)           )storage (initial 10k next 10k pctincrease 0);
          *下面讲解上面命令的各方面的含义           create table test_user --创建数据表               no              number(5)            not null ,        --pk               (列名或字段名) 数据类型(数据长度) 该数据列不能为空 ,是列之间的分隔符 --后的内容是注释                   constraint pk_connectdb primary key(no)           (约束)    约束名      (主键)    (列名)    意思就是 在数据表 test_user中所有行数据 no的值不能相同(这就是主键的含义)
    
          storage (initial 10k next 10k pctincrease 0); 这个说起来比较复杂, 反正如果某个数据表要存放大量数据,就把initial和next后的值设置大一点, 否则设置小一点.
    
          既然上面在创建数据表中没有特别指定 表空间,当然该表就存放在study缺省表空间data_test了.
    

    create tablespace data_phonepos datefile ‘d:\install\OracleXpdb\datafilephonepos.dbf’ size 8000M; create user phonepos identified by phonepos default tablespace data_phonepos;

    grant connect, resource to phonepos; grant dba to phonepos;

    权限的查询

    5.1 查询某个用户授予其他用户在当前用户模式下的对象权限 select * from user_tab_privs_made --假如当前用户为WENZI,那么查询结果就是由WENZI授权,在WENZI模式下的权限记录

    5.2 查询某个用户授予其他用户在该用户模式对象及其他模式对象上的对象权限 select * from all_tab_privs_made – 假如当前登录用户为WENZI,那么查询结果就是所有由WENZI授予的权限的记录

    修改用户

    ALTER USER avyrros IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA 100M ON data_ts QUOTA 0 ON test_ts PROFILE clerk;

    删除用户 DROP USER username [CASCADE] --CASECADE 选项会删除该用户模式下的所有对象,建议在删除前,先确认是否有其他的依赖关系存在。

    查询属于用户的对象 select owner,object_name,object_type,status from dba_objects where owner=‘WENZI’

    5.3 查询为某个用户授予的,在其他模式对象上的权限 select * from user_tab_privs_recd --假如当前登录用户为WENZI,那么查询结果就是WENZI在其他模式对象上的权限

    5.4 查询为某个用户授予的,在该用户模式对象与其他模式对象上的权限 select * from all_tab_privs_recd --假如当前用户为wenzi,则查询结果为wenzi在整个数据库中拥有权限的对象

    角色管理

    创建口令文件 orapwd file=’…\pwd{SID}.ora’ password=’***(sys的密码)’ tntries=10(口令文件最大的用户数量)

    要使某个用户可以使用口令文件,必须为其授予SYSDBA权限,系统会自动将其加入到口令文件中。 grant sysdba to wenzi 当收回SYSDBA权限时,系统将对应的用户从口令文件中删除。 revoke sysdba from wenzi

    查看口令文件管理的用户 select * from v$pwfile_users

    创建步骤:
    SYS用户在CMD下以DBA身份登陆:
    [user@root ~]$ sqlplus /nolog
    SQL> conn sys/h1w2D3B4 as sysdba

    SQL> startup

    [user@root ~]$ lsnrctl start

    // 分为四步

    //第1步:创建临时表空间

    create temporary tablespace user_temp

    tempfile ‘D:\oracle\oradata\Oracle9i\user_temp.dbf’

    size 50m

    autoextend on

    next 50m maxsize 20480m

    extent management local;

    //第2步:创建数据表空间

    create tablespace user_data

    logging

    datafile ‘D:\oracle\oradata\Oracle9i\user_data.dbf’

    size 50m

    autoextend on

    next 50m maxsize 20480m

    extent management local;

    //第3步:创建用户并指定表空间

    create user username identified by password

    default tablespace user_data

    temporary tablespace user_temp;

    //第4步:给用户授予权限

    grant connect,resource to username;


    //以后以该用户登录,创建的任何数据库对象都属于user_temp 和user_data表空间,

    这就不用在每创建一个对象给其指定表空间了

    撤权:

    revoke 权限… from 用户名;

    删除用户命令

    drop user user_name cascade;

    建立表空间

    CREATE TABLESPACE data01

    DATAFILE ‘/oracle/oradata/db/DATA01.dbf’ SIZE 500M

    UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定,区尺寸默认为64k

    删除表空间

    DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

    一、建立表空间

    CREATE TABLESPACE data01

    DATAFILE ‘/oracle/oradata/db/DATA01.dbf’ SIZE 500M

    UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定,区尺寸默认为64k

    二、建立UNDO表空间

    CREATE UNDO TABLESPACE UNDOTBS02

    DATAFILE ‘/oracle/oradata/db/UNDOTBS02.dbf’ SIZE 50M

    #注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间:

    ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

    三、建立临时表空间

    CREATE TEMPORARY TABLESPACE temp_data

    TEMPFILE ‘/oracle/oradata/db/TEMP_DATA.dbf’ SIZE 50M

    四、改变表空间状态

    1.使表空间脱机

    ALTER TABLESPACE game OFFLINE;

    如果是意外删除了数据文件,则必须带有RECOVER选项

    ALTER TABLESPACE game OFFLINE FOR RECOVER;

    2.使表空间联机

    ALTER TABLESPACE game ONLINE;

    3.使数据文件脱机

    ALTER DATABASE DATAFILE 3 OFFLINE;

    4.使数据文件联机

    ALTER DATABASE DATAFILE 3 ONLINE;

    5.使表空间只读

    ALTER TABLESPACE game READ ONLY;

    6.使表空间可读写

    ALTER TABLESPACE game READ WRITE;

    五、删除表空间

    DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

    六、扩展表空间

    首先查看表空间的名字和所属文件

    select tablespace_name, file_id, file_name,

    round(bytes/(1024*1024),0) total_space

    from dba_data_files

    order by tablespace_name;

    1.增加数据文件

    ALTER TABLESPACE game

    ADD DATAFILE ‘/oracle/oradata/db/GAME02.dbf’ SIZE 1000M;

    2.手动增加数据文件尺寸

    ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf’

    RESIZE 4000M;

    3.设定数据文件自动扩展

    ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf

    AUTOEXTEND ON NEXT 100M

    MAXSIZE 10000M;

    设定后查看表空间信息

    SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

    (B.BYTES100)/A.BYTES “% USED”,(C.BYTES100)/A.BYTES “% FREE”

    FROM SYS.SM T S A V A I L A , S Y S . S M TS_AVAIL A,SYS.SM TSAVAILA,SYS.SMTS_USED B,SYS.SM$TS_FREE C

    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

    创建数据表:

    Oracle中建表和指定表空间

    –建一个表 create table HH2(

    tid number primary key ,–主键设定

    tname varchar2(20)

    );

    –删除表 drop table HH;

    –表空间(相当于一个数据库)(DBA权限) create tablespace test datafile ‘D:test.dbf’ size 10M autoextend on next 10M maxsize 100M

    –指定表在那个表空间里面(默认在USERS表空间里) create table HH(tid number primary key) tablespace test; select * from tabs;

    –删除 表空间 drop tablespace test including contents and datafiles --连带物理文件和表空间中的数据也一起删除

    –建表建约束 create table student1(

    sid number primary key,

    sname varchar2(20) not null,

    sage number,

    ssex char(2),

    saddress varchar2(100),

    cid number references tclass(cid)–建立外键关系 );

    create table tclass (

    cid number primary key,

    cname varchar2(20) );

    –唯一unique 检查 check 默认值 modify 添加外键关系 添加列

    alter table student1 add constraint UQ_student1_sname unique(sname);

    alter table student1 add constraint CK_student1_agae check(sage between 19 and 70);

    alter table student1 modify ssex default ‘男’;

    alter table student1 add constraint FK_student1_cid foreign key(cid) references tclass(cid);

    alter table student1 add dt date;

    –删除约束 alter table student1 drop constraint UQ_student1_sname ;

    1.创建oracle数据表

    创建oracle数据表的语法如下:

    create table命令用于创建一个oracle数据表;括号内列出了数据表应当包含的列及列的数据类型;tablespace则指定该表的表空间。

    创建数据表students。

    在该创建语句中,依次定义了student_id、student_name、student_age、status及version等列;tablespace users表示将表创建于表空间users中。

    通过视图user_tables可以获得当前用户所拥有的表信息,利用如下SQL语句可以查看表student的表空间信息。

    在oracle的SQL命令行下,可以利用describe命令来查看已有数据表的表结构,如下所示。

    2.数据表的相关操作

    数据表创建之后,由于某些原因,例如,设计时的考虑不足,往往需要对其进行结构上的调整。常见的调整包括,增加新列、修改已有列、删除、重命名已有列。另外,还可以转移数据表的表空间。

    修改数据表结构应当使用alter table命令。例如,在表student中,增加新列class_id(班级ID)的SQL语句如下图所示。

    alter table student 用于修改表student的结构;add用于增加列,注意此处没有collumn关键字;小括号内是列及列的数据类型;用户可以一次性为表增加多个列,各列之间使用逗号进行分隔。

    在修改成功之后,表student的结构如下所示。

    同样,可以利用alter命令修改和删除已有列。

    利用alter命令将class_id的数据类型修改为varchar2(20)。

    modify(class_id varchar2(20))用于修改表student中的已有列class_id,实际相当于重新定义。该列新的数据类型为varchar2(20)。此时表student的结构如下:

    通过modify选项可以将列的类型重新定义,而通过drop选项则删除已有列,如下所示。

    drop column class_id用于删除已有列class_id;需要注意的是,此处必须添加column选项,才能表示删除的目标是一个列。此时,表student的结构已经修改如下:

    对于数据表的列,除了增、删、改操作之外,还可以进行重命名操作。重命名一个列,应该使用rename选项。例如,为了与其他数据表进行统一,需要将表student的列student_id重命名为id,则可以利用如下SQL语句。

    rename column student_id to id用于将列student_id重命名为id。此时,表student的结构如下所示。

    对于调整数据表结构来说,要特别注意严谨性。列的数据类型的修改,有可能会影响应用程序对数据库进行存取;而列的删除和重命名更需要检查应用程序是否会出现关联性错误。

    如果数据表创建时,选择了错误的表空间,那么可以利用alter table命令,结合move tablespace选项转移表空间,如下图所示。

    将表student转移至表空间users中。

    alter table student用于修改表student的属性;move tablespace users用于将表student从当前表空间转移至表空间users。可以通过查询视图user_tables获得表student转移之后的表空间信息,如下所示。

    分析查询结果可知,利用move tablespace选项已经成功实现了表空间的转移。

    删除数据表

    利用drop table命令删除数据表

    有时,由于某些约束的存在,例如,当前表的主键被其他表用作外键,会导致无法成功删除。利用cascade constraints选项可以将约束同时删除,从而保证drop table命令一定能够成功执行。

    特殊的数据表dual

    dual表实际属于系统用户sys,具有了数据库基本权限的用户,均可查询该表的内容,如下所示:

    或者

    分析查询结果可知,dual表仅含有一行一列。该表并非为了存储数据而创建的,其存在的意义在于提供强制的数据源。

    在oracle中,所有查询语句必须满足select column_name from table_name的格式。但是,在某些场景下,数据源table_name并不明确。例如,函数sysdate()用于返回当前日期,那么在SQL命令行下调用该函数时,很难有明确的数据源,此时即可使用dual表。

    利用dual表提供数据源,以获得当前日期。

    同样,对于各种数学运算,同样可以利用dual表作为数据源,来打印和查看输出结果。

    dual表提供了一行一列的数据格式,从而使各种表达式、函数运算在以其为数据源时,能够输出单行单列的形式。

    由于dual表的所有者为系统用户sys,因此,只有用户利用该身份登录数据库才可以修改该表。但是,修改该表的内容或者结构都应该被禁止。

    oracle create tablespace

    create tablespace ZGECM
    logging datafile ‘G:\oracle\product\10.2.0\ZGECM.DBF’
    size 500M autoextend on
    next 10M maxsize 500M extent management local

    create user sms2 identified by sms2 default tablespace ECM2
    temporary tablespace temp profile default;

    –6 授权给新建用户
    grant connect,resource to sms2;
    grant dba to sms2;

    exp xuner_ecm/xuner_ecm@192.168.1.139/SMS FILE=E:/xunerecm0910_server.DMP OWNeR=xuner_ecm

    imp scjt/scjt@SMS fromuser=ecm touser=sms2 file=‘G:\sms.dmp’

    imp SMS/SMS@LOCAL_SMS fromuser=SMS touser=SMS file=‘G:\sms.dmp’

    select userenv(‘language’) from dual; //查询oracle服务器端的字符

    select nls_charset_name(to_number(‘0354’,‘0354’))
    from dual;

    CREATE DATABASE LINK sms CONNECT TO sms IDENTIFIED BY sms
    USING ‘SMS’;

    insert into XSJBXXB select * from sms.xsjbxxb@zhang2

    insert into XSJBXXB select * from sms.xsjbxxb@to_test

    –创建dblink
     create database link to_test
      connect to sms identified by sms
      using ‘ZHANG’;

    ‘(DESCRIPTION =(
    ADDRESS_LIST =(
    ADDRESS =(
    PROTOCOL = TCP)(
    HOST = 192.168.0.100)(PORT = 1521)))
    (CONNECT_DATA =(SERVICE_NAME = AAA)))’

    –查询约束
    select owner,constraint_name,table_name from user_constraints
    where constraint_name=‘SYS_C00119759’


    /*分为四步 */
    /*第1步:创建临时表空间 */
    create temporary tablespace user_temp
    tempfile ‘D:\oracle\oradata\Oracle9i\user_temp.dbf’
    size 50m
    autoextend on
    next 50m maxsize 20480m
    extent management local;

    /*第2步:创建数据表空间 */
    create tablespace user_data
    logging
    datafile ‘D:\oracle\oradata\Oracle9i\user_data.dbf’
    size 50m
    autoextend on
    next 50m maxsize 20480m
    extent management local;

    /*第3步:创建用户并指定表空间 */
    create user username identified by password
    default tablespace user_data
    temporary tablespace user_temp;

    /*第4步:给用户授予权限 */
    grant connect,resource,dba to username;

    –Error dropping MEM_GENINF:
    –ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
    –Record is locked by another user

    –1.查看锁
    select t2.username,t2.sid,t2.serial#,t2.logon_time
    from v l o c k e d o b j e c t t 1 , v locked_object t1,v lockedobjectt1,vsession t2 where t1.session_id=t2.sid ;

    –2、Kill
    alter system kill session ‘sid,serial#’;
    alter system kill session ‘151,14678’;

    展开全文
  • Oracle空间

    千次阅读 2016-04-25 23:04:34
    Oracle磁盘空间管理中的最高逻辑层是表空间(TABLESPACE),它的下一层是段(SEGMENT),并且一个段只能驻留在一个表空间内。段的下一层就是盘区,一个或多个盘区(EXTENT)可以组成一个段,并且每个盘区只能驻留在...

    Oracle磁盘空间管理中的最高逻辑层是表空间(TABLESPACE),它的下一层是段(SEGMENT),并且一个段只能驻留在一个表空间内。段的下一层就是盘区,一个或多个盘区(EXTENT)可以组成一个段,并且每个盘区只能驻留在一个数据文件中。如果一个段跨越多个数据文件,它就只能由多个驻留在不同数据文件中的盘区构成。盘区的下一层就是数据块,它也是磁盘空间管理中逻辑划分的最底层,一组连续的数据块可以组成一个盘区。
    这里写图片描述
    如果要查询表空间与对应的数据文件的相关信息,可以DBA_DATA_FILES数据字典获得,如下

    col tablespace_name for a10;
    col file_name for a50;
    col bytes for 999,999,999;
    select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;

    这里写图片描述
    从查询所列的结果来看,一个数据库包括多个表空间,比如,SYSTEM表空间、USERS表空间等等。而每一个表空间又包含一个或多个数据文件,比如,USERS表包括一个数据文件USERS01.DBF,表空间可以看成是Oracle数据库的逻辑结构,而数据文件可以看成是Oracle数据库的物理结构。
    Oracle 11g的默认表空间
    1. SYSTEM表空间
    Oracle数据库的每个版本都使用SYSTEM表空间存放内部数据和数据字典,SYSTEM表空间主要存放SYS用户的各个对象和其他用户的少量对象。用户可以从DBA_SEGMENTS数据字典中查询到某个表空间所存放的数据对象及其类型(如,索引、表、簇等)和拥有者。
    查询USERS表空间内存放的数据对象及其类型和拥有者,代码如下:

    col owner for a10;
    col segment_name for a30;
    col segment_type for a20;
    select segment_type,segment_name,owner from dba_segments where tablespace_name='USERS';

    这里写图片描述
    USERS表空间存放了SCOTT用户的表和索引,以及OE用户的大对象索引、索引等数据对象。

    1. SYSAUX表空间

    SYSTEM表空间主要用于存放Oracle系统内部的数据字典,而SYSAUX表空间充当SYSTEM的辅助表空间,主要用于存储除数据字典以外的其他数据对象,它在一定程度上降低了SYSTEM表空间的负荷。
    下面通过DBA_SEGMENTS数据字典来查询SYSAUX表空间的相关信息。

    select owner as 用户,count(segment_name) as 对象数量 from dba_segments where tablespace_name='SYSAUX' group by owner;

    注意:用户可以对SYSAUX表空间进行增加数据文件和监视等操作,但不能对其执行删除、重命名或设置只读(READ ONLY)等操作。

    1. 创建表空间
    CREATE [SMALLFILE/BIGFILE] TABLESPACE tablespace_name
    DATAFILE ‘/path/filename’ SIZE num[k/m] REUSE
    [,’/path/filename’ SIZE num[k/m] REUSE]
    [,…]
    [AUTOEXTEND [ON | OFF] NEXT num[k/m]
    [MAXSIZE [UNLIMITED | num[k/m]]]]
    [MININUM EXTENT num[k/m]]
    [DEFAULT STORAGE storage]
    [ONLINE | OFFLINE]
    [LOGGING | NOLOGGING]
    [PERMANENT | TEMPORARY]
    [EXTENT MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM SIZE num[k/m]]]]

    在上面的语法中出现了大量的关键字和参数
    1.语法中的关键字
    - SMALLFILE/BIGFILE:表示创建的是小文件表空间还是大文件表空间。
    - AUTOEXEND [ON | OFF] NEXT:表示数据文件为自动扩展(ON)或非自动扩展(OFF),如果是自动扩展,则需要设置NEXT的值。
    - MAXSIZE:表示当数据文件自动扩展时,允许数据文件扩展的最大长度字节数,如果指定UNLIMITED关键字,则不需要指定字节长度。
    - MINIMUN EXTENT:指定最小的长度,由操作系统和数据库的块决定。
    - ONLINE | OFFLINE:创建表空间时可以指定为在线或离线。
    - PERMANENT|TEMPORARY:指定创建的表空间是永久表空间或临时表空间,默认为永久性表空间。
    - LOGGING |NOLOGGING:指定该表空间内的表在加载数据时是否产生日志,默认为产生日志(LOGGING)。即使设置为NOLOGGING,但在进行INSERT,UPDATE和DELETE操作时,Oracle仍会将操作信息记录到RedoLog Buffer中。
    - EXTENT MANAGEMENT DICTIONARY |LOCAL:指定表空间的扩展方式是使用数据字典管理还是本地化管理,默认为本地化管理。Oracle不推荐使用数据字典管理表空间。
    - AUTOALLOCATE | UNIFORM SIZE:如果采用本地化管理表空间,在表空间扩展时,指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。若是按照等同大小进行,则默认每次扩展的大小为1MB。
    2.语法中的参数
    - tablespace_name:该参数表示要创建的表空间的名称。
    - /path/filename’:该参数表示数据文件的路径与名字;REUSE表示若该文件存在,则清除该文件在重新建立该文件,若该文件不存在,则创建该文件。
    - DEFAULT STORAGE storage:指定以后要创建的表、索引及簇的存储参数值,这些参数将影响以后表等的存储参数值。

    通过本地化管理方式创建表空间
    本地化表空间管理使用位图跟踪表空间所对应的数据文件的自由空间和块的使用状态,位图中的每个单元对应一个块或一组块。当分配或释放一个扩展时,Oracle会改变位图的值以指示该块的状态。这些位图值的改变不会产生回滚信息,因为它们不更新数据字典的任何表。所以,本地管理表空间具有以下优点。
    使用本地化的扩展管理功能(包括自动大小和等同大小两种),可以避免发生重复的空间管理操作。
    本地化管理的自动扩展(AUTOALLOCATE)能够跟踪临近的自由空间,这样可以消除结合自由空间的麻烦。本地化的扩展大小可以由系统自动确定(AUTOALLOCATE),也可以选择所有扩展有同样的大小(UNIFORM)。通常使用EXTENT MANAGEMENT LOCAL子句创建本地化的可变表空间。
    通过本地化管理方式(LOCAL)创建一个表空间,其扩展大小为等同的256k,代码及运行结果如下。

    SQL> create tablespace tbs_test_1 datafile 'D:\OracleFiles\OracleData\datafile1.dbf'
      2  size 10m
      3  extent management local uniform size 256K;

    通过本地化管理方式(LOCAL)创建一个表空间,其扩展大小为自动管理

    SQL> create tablespace tbs_test_2 datafile 'D:\OracleFiles\OracleData\datafile2.dbf'
      2  size 10m
      3  extent management local autoallocate;
    

    当创建扩展大小等同的表空间时,使用uniform关键字,并指定每次扩展时的大小;当创建扩展大小为自动管理时,使用autoallocate关键字,并且不需要指定扩展时的大小。
    通过段空间管理方式创建表空间
    段空间管理方式是建立在本地化空间管理方式基础之上的,即只有本地化管理方式的表空间,才能在其基础上进一步建立段空间管理方式,它使用“SEGMENT SPACE MANAGEMENT MANUAL/LOCAL”语句,段空间管理又可分为手工段和自动段两种空间管理方式。
    1.手工段空间管理方式
    手工段空间管理方式是为了往后兼容而保留的,它使用自由块列表和PCT_FREE与PCT_USED参数来标识可供插入操作使用的数据块。
    在每个INSERT或UPDATE操作后,数据库都会比较该数据块中的剩余自由空间与该段的PCT_FREE设置。如果数据块的剩余自由空间少于PCT_FREE自由空间(也就是说剩余空间已经进入系统的下限设置),则数据库就会从自由块列表上将其取下,不再对其进行插入操作。剩余的空余空间保留给可能会增大该数据块中行大小的UPDATE操作。
    而在每个UPDATE操作或DELETE操作后,数据库会比较该数据块中的已用空间与PCT_USED设置,如果已用空间少于PCT_USED已用空间(也就是已用空间未达到系统的上限设置),则该数据块会被加入到自由列表中,供INSERT操作使用,下面来看一个实例。

    ---通过本地化管理方式(LOCAL)创建一个表空间,其扩展大小为自动管理,其段空间管理方式为手工
    create tablespace tbs_test_3 datafile 'E:\OracleFiles\OracleData\datafile3.dbf'
    size 10m
    extent management local autoallocate;
    segment space management manual;

    2.自动段空间管理方式
    如果采用自动段空间管理方式,那么数据库会使用位图而不是自由列表来标识哪些数据块可以用于插入操作,哪些数据块需要从自由块列表上将其取下。此时,表空间段的PCT_FREE和PCT_USED参数会被自动忽略。
    由于自动段空间管理方式比手工段空间管理方式具有更好的性能,所以它是创建表空间时的首选方式,下面来看一个实例。

    ----通过本地化管理方式(LOCAL)创建一个表空间,其扩展大小为自动管理,其段空间管理方式为自动
      create tablespace tbs_test_4 datafile 'D:\OracleFiles\OracleData\datafile4.dbf'
     size 20m
      extent management local autoallocate
     segment space management auto;
    • 自动段空间管理方式不能用于创建临时表空间和系统表空间。
    • Oracle本身推荐使用自动段空间管理方式管理永久表空间,但其默认情况下却是MANUAL(手工)管理方式,所以在创建表空间时需要明确指定为AUTO。

    创建非标准块表空间
    在Oracle数据库中,通常的块大小为8192字节,即8KB,但Oracle 11g允许创建块大小与基本块不同的表空间,块大小可由创建表空间时的blocksize参数指定,这样有利于存储不同大小的对象,但用户需要注意以下三点:
    - 表空间的非标准块的大小为基本块的倍数。比如,大小为16KB,64KB,128KB。
    - Oracle 11g通常使用SGA自动共享内存管理,因此需要设置初始化参数db_16k_cache_size=16K。
    - 这种块较大的表空间通常用来存放大对象(LOB)类型。
    创建一个非标准块的表空间,块的大小为标准块的2倍

    SQL> alter system set db_16k_cache_size = 16M scope=both; 
    SQL> create tablespace tbs_test_5 datafile 'D:\OracleFiles\OracleData\datafile5.dbf'
      2  size 64m reuse
      3  autoextend on next 4m maxsize unlimited
      4  blocksize 16k
      5  extent management local autoallocate
      6  segment space management auto;

    说明:若不设置初始化参数“db_16k_cache_size”,则Oracle会显示“ORA-29339: 表空间块大小 16384与配置的块大小不匹配”这样的提示信息。
    建立大文件表空间
    从Oracle 11g版本开始,引进了一个新的表空间类型——大文件(BIGFILE)。与以前版本的最多可由1022个文件组成的表空间不同;大文件表空间存放在一个单一的数据文件中,并且它需要更大的磁盘容量来存放数据;大文件表空间可以根据选择的块的大小变化,从32TB增至128TB。
    大文件表空间是为超大型数据库而设计的。当一个超大型数据库具有上千个读/写数据文件时,必须更新数据文件头部(比如检查点)的操作可能会花费相当长的时间。如果降低数据文件的数量,那么,这些操作完成起来就可能会快的多。创建一个大文件表空间,只需要在CREATE语句中使用BIGFILE关键字即可,下面来看一个例子。
    创建一个大文件表空间,指定一个数据文件,并且数据文件的大小为2g

    SQL> create bigfile tablespace tbs_test_big datafile 'D:\OracleFiles\OracleData\datafilebig.dbf'
      2  size 2g;

    注意:在创建大文件表空间时,由于指定的数据文件都比较大,所以通常这个创建过程都比较慢一些,用户要耐心等待,不要急于结束操作。
    说明:大文件(BIGFILE)表空间主要被使用在存储区域网络上(SAN)、磁盘阵列上、自动存储管理(ASM)上和类似的提供禁止数据访问多设备的存储解决方案上。
    由于大文件(BIGFILE)表空间只有一个数据文件,所以,当需要重新设置其大小时不需要标识数据文件的具体路径和名称,只需要使用ALTER TABLESPACE命令指定大文件表空间的名称,即可很方便地修改其大小。另外,需要注意的是:在创建表空间的语法中使用size来标识数据文件的大小,而在修改表空间时,要使用resize关键字来重置数据文件的大小。修改大文件表空间tbs_test_big,将其空间大小由2g改变为1g

    SQL> alter tablespace tbs_test_big resize 1g;
    与大文件表空间不同的是,传统表空间可能包含多个数据文件,如果要改变其大小,则需要在ALTER DATABASE语句后面指定完整的操作系统路径名或内部文件号辨别每个数据文件,然后再重新设置他们的大小。 把数据文件datafile3.dbf(其所属的表空间是tbs_test_3)的大小由原来的20m修改为100m,代码及运行结果如下。
    

    SQL> alter database datafile ‘D:\OracleFiles\OracleData\datafile3.dbf’
    2 resize 100m;

    维护表空间与数据文件
    设置默认表空间
    在Oracle数据库中创建用户(使用CREATE USER语句)时,如果不指定表空间,则默认的临时表空间是TEMP,默认的永久表空间是SYSTEM,这样就导致应用系统与Oracle系统竞争使用SYSTEM表空间,会极大地影响Oracle系统的执行效率。为此,Oracle建议将非SYSTEM表空间设置为应用系统的默认永久表空间,并且将非TEMP临时表空间设置为应用系统的临时表空间。这样有利于数据库管理员根据应用系统的运行情况适时调整默认表空间和临时表空间。
    更改默认临时表空间需要使用ALTER DATABASE DEFAULT TEMPRORY TABLESPACE 语句,更改默认永久表空间需要使用ALTER DATABASE DEFAULT TABLESPACE 语句

           ----将临时表空间temp_1设置为默认的临时表空间
    SQL>alter database default temprory tablespace temp_1
          -----将表空间tbs_example设置为默认的永久表空间
    SQL>alter database default tablespace tbs_example

    更改表空间的状态
    表空间有只读和可读写两种状态,若设置某个表空间为只读状态,则用户就不能够对该表空间中的数据进行DML操作(INSERT、UPDATE、DELETE),但对某些对象的删除操作还是可以进行的,比如,索引和目录就可以被删除掉;若设置某个表空间为可读写状态,则用户就可以对表空间中的数据进行任何正常的操作,这也是表空间的默认状态。
    设置表空间为只读状态,可以保证表空间数据的完整性。通常在进行数据库的备份、恢复及历史数据的完整性保护时,可将指定的表空间设置成只读状态。但设置表空间为只读并不是可以随意进行的,必须要满足下列条件。

    • 该表空间必须为ONLINE状态。
    • 该表空间不能包含任何回滚段。
    • 该表空间不能在归档模式下。

      更改表空间的读写状态需要使用ALTER TABLESPACE…READ|READ WRITE语句

      -----修改tbs_test_3表空间为只读状态,代码及运行结果如下。
    SQL> alter tablespace tbs_test_3 read only;
       ------ 修改tbs_test_3表空间为可读写状态,代码及运行结果如下。
    SQL> alter tablespace tbs_test_3 read write;

    重命名表空间
    Oracle 11g提供了对表空间进行重命名的新功能,这对于一般的管理和移植来说是非常方便的。
    但要注意的是:数据库管理员只能对普通的表空间进行更名,不能够对SYSTEM和SYSAUX表空间进行重命名,也不能对已经处于OFFLINE状态的表空间进行重命名。
    重命名表空间需要使用ALTER TABLESPACE…RENAME TO语句

    -----把tbs_test_3表空间重命名为tbs_test_3_new,代码及运行结果如下。
    SQL> alter tablespace tbs_test_3 rename to tbs_test_3_new;

    说明:在修改完表空间名称之后,原表空间中所存放的数据库对象(表、索引、簇等)会被存放到新表空间名下。

    删除表空间
    当某个表空间中的数据不再需要时,或者新创建的表空间不符合要求时,可以考虑删除这个表空间。若要删除表空间,则需要用户具有DROP TABLESPACE权限。
    在默认情况下,Oracle系统不采用Oracle Managed Files方式管理文件,这样删除表空间实际上仅是从数据字典和控制文件中将该表空间的有关信息清除掉,并没有真正删除该表空间包含的所有物理文件。因此,要想彻底删除表空间来释放磁盘空间,那么在执行删除表空间的命令之后,还需要手工删除该表空间包含的所有物理文件。
    当Oracle系统采用Oracle Managed Files方式管理文件时,删除某个表空间后,Oracle系统将自动删除该表空间包含的所有物理文件。删除表空间需要使用DROP TABLESPACE命令,其语法格式如下:

    DROP TABLESPACE tbs_name[INCLUDING CONTENTS] [CASCADE CONSTRAINTS]

    参数说明如下:

    • tbs_name:表示要删除的表空间名称。
    • INCLUDING DONTENTS:表示删除表空间的同时删除表空间中的数据。如果不指定INCLUDING
      CONTENTS参数,而该表空间又存在数据时,则Oracle会提示错误。
    • CASCADE CONSTRAINTS:表示当删除当前表空间时也删除相关的完整性限制。完整性限制包括主键及唯一索引等。如果完整性存在,而没有CASCADE CONSTRAINTS参数,则Oracle会提示错误,并且不会删除该表空间。

    删除表空间tbs_test_2及其包含的所有内容。

    SQL> drop tablespace tbs_test_1
      2  including contents
      3  cascade constraints;

    在上面的代码中,不但删除了表空间tbs_test_1,而且也删除了表空间中的数据(including contents)和完整性约束(cascade constraints)。

    维护表空间中的数据文件
    维护表空间中的数据文件主要包括向表空间中添加数据文件、从表空间中删除数据文件和对表空间中的数据文件进行自动扩展设置
    1.向表空间中添加数据文件
    当某个非自动扩展表空间的扩展能力不能满足新的扩展需求,数据库管理员就需要向表空间中添加新的数据文件(比如,添加一个能够自动扩展的表空间),以满足数据对象的扩展需要。下面来看一个向表空间添加新数据文件的例子。
    向users表空间中添加一个新的数据文件users02.dbf,该文件支持自动扩展,扩展能力为每次扩展5m,并且该文件的最大空间不受限制

    SQL> alter tablespace users add datafile 'e:\app\Administrator\oradata\orcl\users02.dbf'
      2  size 10m autoextend on next 5m maxsize unlimited;

    2.从表空间中删除数据文件
    在Oracle 11g R2以前的版本中,Oracle系统一直只允许增加数据文件到表空间,而不允许从表空间中删除数据文件。从Oracle 11g R2开始,允许从表空间中删除无数据的数据文件。要实现从表空间中删除数据文件,需要使用ALTER TABLESPACE…DROP DATAFILE语句
    删除users表空间中的users02.dbf数据文件

    SQL> alter tablespace users drop datafile 'e:\app\Administrator\oradata\orcl\users02.dbf';

    3.对数据文件的自动扩展设置
    Oracle数据库的数据文件可以设置成具有自动扩展的功能,当数据文件剩余的自由空间不足时,它会按照设定的扩展量自动扩展到指定的值。这样可以避免由于剩余表空间不足而导致数据对象需求空间扩展失败的现象。
    可以使用AUTOEXTEND ON命令使数据文件在使用中能根据需求自动扩展。用户可以通过以下4种方式设置数据文件的自动扩展功能。

    • 在CREATE DATABASE语句中设置。
    • 在ALTER DATABASE语句中设置。
    • 在CREATE TABLESPACE语句中设置。
    • 在ALTER TABLESPACE语句中设置。

      对于Oracle 数据库管理员来说,主要是用后三种命令修改数据文件是否为自动扩展,因为数据库实例已经创建完成,所以不再需要使用CREATE DATABASE命令。
      首先查询TBS_TEST_2表空间中的数据文件是否为自动扩展,若不是自动扩展,然后修改为自动扩展,扩展量为10m,并且最大扩展空间不受限制

        SQL> col file_name for a50;
    SQL> select file_name ,autoextensible from dba_data_files where tablespace_name = 'TBS_TEST_2';
    
    FILE_NAME                                           AUT
    ------------------------------------------------------------------------------  ---
    D:\ORACLEFILES\ORACLEDATA\DATAFILE2.DBF         NO

    从上面的运行结果中可以看出,DATAFILE2.DBF数据文件不自动扩展(autoextensible属性值为NO),然后使用alter databas语句修改该数据文件为自动扩展。

    SQL> alter database datafile 'D:\OracleFiles\OracleData\datafile2.dbf'
      2  autoextend on next 10m maxsize unlimited;

    管理撤销表空间
    撤销表空间,通常也称为UNDO表空间;UNDO表空间中的段也称为撤销段或UNDO段;撤销段中存放的数据就是“撤销信息”,这些“撤销信息”也称为撤销数据或UNDO数据,可见撤销段是最直接管理“撤销信息”的逻辑层,下面将对撤销段的几种作用进行讲解和分析。
    使读写一致
    在不同的进程或用户模式下检索数据时,Oracle只能给用户提供被提交的数据,这样可以确保数据的一致性。例如,在SCOTT模式下,执行了UPDATE emp SET sal=5500 WHERE empno=7788语句,这样旧的数据记录会被存放到UNDO段中,而新数据则会存放到emp段,假定此时该数据尚未提交(比如没有执行commit命令,也没有退出SQL*Plus环境);然后用户在SYSTEM模式下执行SELECT sal FROM scott.emp WHERE empno=7788语句,此时用户将取得“旧的工资数据”,而不是新数据5500,而该数据正是从UNDO段中读取的。
    可以回退事务
    当执行修改(UPDATE)数据操作时,旧的数据(即UNDO数据)被存放到UNDO段,而新的数据则被存放到数据段中。如果在修改操作中事务提交出现错误,就需要回退事务,从而取消数据的更改。比如,当用户使用UPDATE语句修改员工的工资时,发现,原本打算修改某个人的工资,但由于误操作,而导致修改了全公司员工的工资(比如,没有使用where条件语句)。这样,用户就可以通过执行ROLLBACK语句来取消事务修改。当执行ROLLBACK语句时,Oracle会将UNDO段的UNDO数据(即旧的员工工资)全部写回到数据段中。
    事务恢复
    事务恢复是例程恢复的一部分,它是由Oracle服务器自动完成的,如果在数据库运行过程中出现例程失败(如断电、内存故障等),那么当重启Oracle 服务器时,后台进程SMON会自动执行例程恢复。执行例程恢复时,Oracle会重新处理所有未提交的数据记录,回退未提交事务。
    闪回操作
    Oracle 11g新增了强大的闪回功能,其中,很多闪回技术都是基于UNDO段实现的,比如,闪回表,闪回事务查询,闪回版本查询等等。

    撤销表空间的初始化参数
    Oracle 11g可以通过设置初始化参数来控制UNDO表空间管理撤销数据,与UNDO表空间有关的参数有以下几种:
    UNDO_TABLESPACE该初始化参数用于指定例程所要使用的UNDO表空间,使用自动UNDO管理模式时,通过配置该参数可以指定例程所要使用的UNDO表空间。
    UNDO_MANAGEMENT该初始化参数用于指定UNDO数据的管理模式,如果为AUTO,则为自动撤销管理模式,如果为MANUAL,则为回滚段管理模式。需要注意的是,使用自动撤销管理模式时,如果没有配置初始化参数UNDO_TABLESPACE,Oracle会自动选择第一个可用的UNDO表空间存放UNDO数据;如果没有可用的UNDO表空间,Oracle会使用SYSTEM回滚段存放UNDO记录,并在ALTER文件中记载警告。
    UNDO_RETENTION该初始化参数用于控制UNDO数据的最大保留时间,默认为900秒,从9i版本开始,通过配置该初始化参数,可以指定UNDO数据的保留时间,从而也决定了基于UNDO数据的闪回操作能够闪回的最早时间点。

    撤销表空间的基本操作
    对撤销表空间的基本操作包括创建、修改、删除和切换等,下面分别进行介绍。
    1.创建UNDO表空间
    创建UNDO表空间需要使用CREATE UNDO TABLESPACE 语句,来看下面的例子。

    ---创建一个撤销表空间,并指定数据文件大小为100m
    SQL> create undo tablespace undo_tbs_1
      2  datafile 'D:\OracleFiles\OracleData\undotbs1.dbf'
      3  size 3g;

    在创建表空间时,需要注意以下两个方面:

    • UNDO表空间对应的数据文件大小通常由DML操作可能产生的最大数据量来确定,通常该数据文件的大小至少应在1G以上;
    • 由于UNDO表空间只用于存放撤销数据,所以不要在UNDO表空间内建立任何数据对象(如,表、索引等)。

    2.修改UNDO表空间
    与修改普通的永久性表空间比较类似,修改UNDO表空间也使用ALTER TABLESPACE语句。当事务用尽了UNDO表空间后,可以使用ALTER TABLESPACE…ADD DATAFILE语句添加新的数据文件;当UNDO表空间所在的磁盘填满时,可以使用ALTER TABLESPACE…RENAME DATAFILE语句将数据文件移动到其它磁盘上;当数据库处于ARCHIVELOG模式时,可以使用ALTER TABLESPACE…BEGIN BACKUP/END BACKUP语句备份UNDO表空间。

    管理临时表空间
    临时表空间是一个磁盘空间,主要用于内存排序区不够而必须将数据写到磁盘的那个逻辑区域,由于该空间在排序操作完成后可以由Oracle系统自动释放,所以也称作临时表空间。
    临时表空间主要用于临时段,而临时段是由数据库根据需要创建、管理和删除的,这些临时段的生成通常与排序之类的操作有关,下面的几种操作经常会用到临时表空间。
    - SELECT DISTINCT不重复检索
    - UNION联合查询
    - MINUS计算
    - ANALYZE分析
    - 连接两个没有索引的表

    通常使用CREATE TEMPORARY TABLESPACE语句来创建临时表空间,-

    --- 创建一个临时表空间,空间大小为300m
    SQL> create temporary tablespace temp_01 tempfile 'D:\OracleFiles\tempfiles\temp_01.tpf' size 300m;

    在临时表空间创建完毕之后,可以通过alter database命令修改默认的临时表空间为新创建的临时表空间,这样Oracle系统就会使用新创建的临时表空间来存放临时数据

    ------把新创建的临时表空间temp_01设置为系统默认的临时表空间
    SQL> alter database default temporary tablespace temp_01;

    临时表空间是用临时文件而不是数据文件来创建的。另外,临时表空间不需要备份。临时表空间中的数据的修改也不会被记录到重做日志当中去。
    说明:关于临时表空间的其它操作,比如,重命名表空间、删除表空间,这些与永久性表空间的操作基本相同,这里不再赘述。
    查询临时表空间的信息
    这里写图片描述

    关于临时表空间组
    在Oracle 11g中,可以创建多个临时表空间,然后把它们组成一个临时表空间组,这样,应用系统中的数据在排序时就可以使用组里的多个临时表空间,在一个临时表空间组里至少有一个临时表空间,其最大个数没有限制,但是组的名字不能和其中某个临时表空间的名字相同。用户使用临时表空间组来管理临时数据具有以下作用:

    • 避免因大量的排序数据而导致单一临时表空间不足。
    • 当一个用户同时有多个会话时,可以使得它们使用组中的不同临时表空间。
    • 使并行的服务器在单节点上能够使用多个临时表空间。

    用户可以在创建临时表空间的同时来创建临时表空间组,如果删除组中的全部临时表空间,那么该组也将消失。另外,用户也可以将一个临时表空间从一个组移动到另一个组,或是向组里添加新的表空间,下面来看一下关于管理临时表空间的几种情况:
    (1)创建临时表空间组
    创建临时表空间组主要使用group子句

    ---创建一个临时表空间组,并向其中添加两个临时表空间
    SQL> create temporary tablespace tp1 tempfile 'D:\OracleFiles\tempfiles\tp1.tpf' size 10m tablespace group group1;
    SQL> create temporary tablespace tp2 tempfile 'D:\OracleFiles\tempfiles\tp2.tpf' size 20m tablespace group group1;

    (2)转移临时表空间到另外一个组
    转移临时表空间到另外一个组主要使用alter tablespace语句

    --创建临时表空间组group3,将组group1中的临时表空间tp1转移到group3中
    SQL> create temporary tablespace tp3 tempfile 'D:\OracleFiles\tempfiles\tp3.tpf' size 10m tablespace group group3;
    SQL> alter tablespace tp1 tablespace group group3;

    在将组group1中的临时表空间tp1转移到组group3中之后,用户可以通过查询dba_tablespace­groups数据字典来查看组group3中的临时表空间。

    综合实例——在新建的表空间中创建数据表
    (1)打开SQL*Plus,以system身份登录。登录成功后,在SQL*Plus中输入如下代码创建表空间

    SQL> create tablespace test datafile 'E:\database\test_data.dbf' size 20M autoextend on next 5M;

    (2)在新建的表空间中创建教师表,代码及运行结果如下:

    SQL> create table teacher(
      2  teaid number primary key,
      3  teaname varchar2(10),
      4  teaage number,
      5  teasubject varchar2(20),
      6  teasex varchar(5))
      7  tablespace test
      8  /
    展开全文
  • oracle空间

    2012-03-17 20:59:41
    空间实质是组织数据文件的一种途径,Oracle就是通过表空间这个数据库对象完成对数据的组织的。在将数据插入Oracle数据库之前,必须首先建立表空间,然后将数据插入表空间的一个对象中。解释数据库、表空间、数据...
  • oracle空间
  • ORACLE空间

    2013-09-09 15:57:07
    空间(TABLESPACE)是ORACLE数据库中最大的逻辑结构。ORACLE数据库是由一个或多个表空间组成的。它在物理上与磁盘上的数据文件相对应(一个表空间由一个或多个数据文件组成,但一个数据文件只能属于一个表空间)。从...
  • 创建一个命名为undotbs01.dbf的undo表空间,并设置代号为UNDOTBS1,设置起始大小为10M,最大最大为20G(最高不能超过30G);, create undo tablespace UNDOTBS1 datefile '/u02/rmts/oradata/rmts/undotbs01.dbf' size ...
  • 1. 创建表空间 create tablespace SIRM2  datafile 'D:\oracle\product\10.2.0\oradata\orcl\SIRM2.dbf' size 1024M --存储地址 初始大小1G autoextend on next 10M maxsize unlimited --每次扩展10M,无限制...
  • 在CMD中打sqlplus /nolog 然后再 conn / as sysdba //创建临时表空间  ...create temporary tablespace user_temp  ...tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m  autoextend
  • 首先需要建立表空间 1.打开Navicat for Oracle,输入相关的的连接信息。如下图: 注意:连接名:可随意取 主机:要链接的服务器的IP 用户名和密码:oracle的用户名和密码 测试连接:点击一下,看看是否连接...
  • oracle,表空间,用户授权
  • oracle修改数据库表空间
  • 多个表空间的优势: 1.能够将数据字典与用户数据分离出来,避免由于字典对象和用户对象保存在同一个数据文件中而产生的I/O冲突 2.能够将回退数据与用户数据分离出来,避免由于硬盘损坏而导致永久性的数据丢失 3.能够...
  • oracle空间和表

    2016-10-19 09:17:53
    创建表空间和表ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的oracle中的表就是一张存储数据的表。表空间是逻辑上的划分。方便管理的。数据表空间 (Tablespace) 存放数据总是需要空间, ...
  • Oracle12c使用Oracle11g/Oracle10g用户创建语句会报[ORA-65096: invalid common user or role name]错误,解决方法如下: 方法一(不推荐):在用户名前加C##前缀 CREATE USER <C##user_name> IDENTIFIED BY &...
  • 数据库经常会由于规划不好或者一些其他原因导致用户的表建在system表空间的情况,这样会导致system表空间急剧膨胀而且无法缩小,并引发性能上的问题。因此建议对客户的所有数据库进行检查,并把数据表都挪到别的表...
  • Oracle 归档与非归档的切换

    千次阅读 2012-01-11 15:01:22
    --强制系统进行日志切换,可马上观察到归档日志的产生 归档模式转化为非归档模式 在这里所有的操作要在数据库的那台机器上操作,用SYS用户进行登录,首先设置归档进程关闭。 alter system set log_archive...
  • Oracle数据库归档模式的切换  在Oracle数据库的开发环境和测试环境中,数据库的日志模式和自动归档模式一般都是不设置的,这样有利于系统应用的调整,也免的生成大量的归档日志文件将磁盘空间大量的消耗。但在...
  • linux下创建oracle用户表空间2

    千次阅读 2014-01-15 14:27:04
    Oracle安装完后,其中有一个缺省的数据库,除了这个缺省的数据库外,我们还可以创建自己的数据库。 为了避免麻烦,可以用’Database Configuration Assistant’向导来创建数据库(这步一定要创建好,因为这里没有做好...
  • oracle数据库归档模式的切换

    千次阅读 2012-01-11 16:18:20
    Oracle数据库的开发环境和测试环境中,数据库的日志模式和自动归档模式一般都是不设置的,这样有利于系统应用的调整,也免的生成大量的归档日志文件将磁盘空间大量的消耗。但在系统上线,成为生产环境时,将其设置...
  • Oracle 归档与非归档的切换https://blog.csdn.net/tianlesoftware/article/details/4693470版权声明: https://blog.csdn.net/tianlesoftware/article/details/4693470 首先查看数据库现有模式可使用以下语句 ...
  • oracle用户下 export ORACLE_SID=[b]emn[/b](数据库sid) 启动监听(在oracle用户下):lsnrctl start 关闭:lsnrctl stop 查看:lsnrctl status 关闭数据库: SQL>shutdown 其参数 :shutdown 四个...
  • oracle学习笔记 存储结构_表空间 我们接着讲oracle存储结构里面的表空间 一)存储 oracle的三类文件 控制文件、数据文件和redolog一定要放在存储上 以前出现一种情况 一个银行用户,它数据库一个集群 切换以后发现...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 13,283
精华内容 5,313
关键字:

oracle切换命名空间