oracle数据库基础知识

oracle数据库

概 述

• Oracle数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置)
• 服务器与客户机是软件概念, 它们与计算机硬件不存在一一对应的关系. 即: 同一台计算机既可以充当服务器又可以充当客户机, 或者, 一台计算机只充当服务器或只充当充当客户机。

Oracle 数据库体系结构简介

• 平常所说的 Oracle 或 Oracle 数据库指的是 Oracle 数据库管理系统. Oracle 数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system).
  • 它由 Oracle 数据库和 Oracle 实例(instance)构成.
    Oracle Server = 实例 + 数据库 (Instance和Database是相互独立的)

数据库 = 数据文件 + 控制文件 +日志文件
实例 = 内存池 + 后台进程
所以可以细分为： Oracle Server = 内存池 + 后台进程 + 数据文件 + 控制文件 + 日志文件

• Oracle 数据库:

  一个相关的操作系统文件(即存储在计算机硬盘上的文件)集合,

  这些文件组织在一起, 成为一个逻辑整体, 即为 Oracle 数据库.

• Oracle 用它来存储和管理相关的信息. Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。

文件类型

1.1数据文件(.DBF)

数据文件是一个二进制文件，是用于保存用户应用程序数据和 Oracle 系统内部数据的 文件，这些文件在操作系统中就是普通的操作系统文件。Oracle 在创建表空间的同时会创建 数据文件。

1.2控制文件(.CTL)

控制文件是一个二进制文件，它主要记录数据库的名称、数据库的数据文件存放位置等 信息。一个控制文件只能属于一个数据库。如果控制文件丢失，这数据库就无法操作。

1.3日志文件(.LOG)

日志文件在 Oracle 数据库中分为重做日志(RedoLogFile)文件和归档日志文件两种。重 做日志文件是 Oracle 数据库正常运行不可缺少的文件。重做日志文件主要记录了数据库操 作过程。用于备份和还原数据库，以达到数据库的最新状态。

Oralce进程结构

数据字典

数据字典(Data dictionary)是一种用户可以访问的记录数据库和应用程序元数据的目录。

• 在oracle里为了能记录所有对象的信息，提供了数据字典的概念。
• 用户级别的数据字典：user_开头_
• 管理员级别数据字典：dba_开头_
• 全级别：all开头

查询用户

select username from dba_users; -- 只有管理员权限的用户才能查询
select username from all_users; -- 当前或任何用户都可使用
-- 查看当前用户的默认表空间
select username, default_tablespace from user_users;
--当前用户角色
select * from user_role_privs;
-- 当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

查询 表空间 (拥有DBA权限的用户才能查询)

select * from dba_data_files;
select * from dba_tablespaces; --表空间
select tablespace_name, sum(bytes), sum(blocks) from dba_free_space group by tablespace_name; --空闲表空间
select * from dba_data_files where tablespace_name='USERS'; -- 表空间对于的数据文件
select * from dba_segments where tablespace_name='USERS';
--查询用户模式对象所使用过的正在使用空间大小
select name, type, source_size, code_size from user_object_size;

查询数据库对象(拥有DBA权限的用户才能查询)

select * from dba_objects
select * from dba_objects where object_type = upper('package body');
select * from dba_objects where OBJECT_TYPE='TABLE' and OWNER='SCOTT'

可根据拥有者查询下列对象类型(object_type)

cluster 　　 databaselink
　　　　function　　 index
　　　　library　　 package
　　　　package body　　 procedure
　　　　sequence 　　synonym
　　　　table　　 trigger
　　　　type　　 undefined
　　　　view

查询表

select * from dba_tables;

select extent_id, bytes from dba_extents where segment_name='CUSTOMERS' and segment_type='TABLE' order by extent_id;

-- 查看回滚段的空间分配信息列信息 SELECT * FROM user_tab_columns;

select distinct table_name from user_tab_columns where column_name='ID';

-- 查看当前用户下所有的表

select * from user_tables;

--查看名称包含log字符的表

select object_name, object_id from user_objects where instr(object_name, 'LOG') > 0;

-- 查看某表的创建时间

select object_name, created from user_objects where object_name = upper('&table_name');

-- 查看某表的大小

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name = upper('&table_name');

-- 查看放在oracle的内存的表

select table_name, cache from user_tables where instr(cache, 'Y') > 0;

实例、表空间、用户、表之间关系

https://blog.csdn.net/liuchaoxuan/article/details/78966355

• 数据库：

  Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。

  其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

• **实例： **

  一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。

  一个数据库可以有n个实例。（实例有点类似其他数据库中库的概念）

• 用户：

  用户是在实例下建立的。不同实例可以建相同名字的用户。

• **表空间：

  表空间是一个用来管理数据存储逻辑概念**

  表空间只是和数据文件（ORA或者DBF文件）发生关系，数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。

• **数据文件（dbf、ora）：

  ** 数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真 正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注：

表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

逻辑上的关系

使用

• 安装Oracle后会有默认的实例，即ORCL。一般不创建多个实例，在默认实例下创建表空间和用户等。

• 运行CMD进入DOS界面，首先输入：sqlplus，回车；再输入：sys/sys as sysdba，回车，即进入“SQL〉”操作状态。

• 创建表空间（表空间名称，表空间数据文件，内存相关属性的设置）

• 创建用户,并且授予相关权限（dos界面下创建的用户不具有connect权限）设置默认表空间

  • DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

  • RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

  • CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

    • 对于普通用户：授予connect, resource权限。

    • 对于DBA管理用户：授予connect，resource, dba权限。

• 创建表

  • 表空间就像仓库，表就像物品，每个用户都有自己默认的表空间，在创建表的时候若无特定声明则放入自己默认的表空间下。

• 插入数据

• 增删改查

实际操作

• 借助控制台，在图形化界面操作了
• 创建表空间

CREATE SMALLFILE TABLESPACE "CHAT" DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\chat' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

• 创建用户

CREATE USER "CHATADMIN" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "CHAT" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT "CONNECT" TO "CHATADMIN"

• 创建表

CREATE TABLE IM_USER 
(
  ID NUMBER(10) NOT NULL 
, USER_ID NUMBER(10) NOT NULL 
, SEX NUMBER(1) NOT NULL 
, NIKE_NAME VARCHAR2(32) NOT NULL 
, PASSWORD VARCHAR2(64) NOT NULL 
, PHONE_NUMBER VARCHAR2(20) 
, EMAIL VARCHAR2(20) 
, AVATAR VARCHAR2(1024) 
, STATUS NUMBER(1) 
, SIGN_INFO VARCHAR2(225) 
)

1.为表中增加约束，语法：
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段)[选项];

范例：为member表增加主键约束
ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid);