Oracle 用户与表空间关系

oracle用户与表空间关系

用户=商家

表=商品

表空间=仓库

1. 1个商家能有很多商品，1个商品只能属于一个商家

2. 1个商品可以放到仓库A，也可以放到仓库B，但不能同时放入A和B

3. 仓库不属于任何商家

4. 商家都有一个默认的仓库，如果不指定具体仓库，商品则放到默认的仓库中

oracle中用户的所有数据都是存放在表空间中的，很多个用户可以共用一个表空间，也可以指定一个用户只用某一个表空间。

表空间：创建表空间会在物理磁盘上建立一个数据文件，作为数据库对象（用户、表、存储过程等等）的物理存储空间；

用户：创建用户必须为其指定表空间，如果没有显性指定默认表空间，则指定为users表空间；创建用户后，可以在用户上，创建表、存储过程等等其他数据库对象；

表：是数据记录的集合；

创建过程： 表空间--->用户--->表;

所属关系： 表空间 包含 用户 包含 表；

1.首先是ORACLE的整体结构。

oracle中的一个数据库就是一个实例.

oracle的一个用户就是一个Schema（即方案）.

oracle的结构是===

实例->用户->表（用户属于数据库实例,表属于某个用户）

所以在oracle下建立 建表空间，建用户，设置用户的默认表空间，在用户下建表；

--创建数据表空间

create tablespace CICI

logging

datafile 'D:\oraclexe\app\oracle\oradata\CICI\CICI.DBF'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

--创建用户并指定表空间

CREATE USER cici IDENTIFIED BY cici

PROFILE DEFAULT

DEFAULT TABLESPACE CICI 

ACCOUNT UNLOCK;

-- 为用户赋予权限

GRANT connect, resource TO cici;

grant create session to cici;

-- 登录用户

sql>conn

请输入用户名 cici

请输入密码 XXXXXX

---建立表

create table aa(name varchar2(100),age number(4));

insert into aa values('wo',29);

-- 查询表

select * from cici.A;

select * from session_privs;

查看系统权限和对象权限

select * from dba_sys_privs;

select * from dba_tab_privs;

改自己的密码,不需要dba的权限，但要使用旧密码进行验证，用以下语句即可

alter user hr identified by 123456 replace zxcasd;

select table_name from user_tables;

desc jobs

set pagesize 200

select * from jobs;

desc locations

set linesize 200

select * from locations;

############################

rac上创建表空间

create tablespace MONITOR datafile '+DATA1/baoka/datafile/monitor.dbf' size 4096m autoextend on;

select * from v$tablespace;

select * from v$datafile;

drop tablespace MONITOR including contents and datafiles;

create tablespace monitor logging 

datafile '+DATA1/baoka/datafile/monitor.dbf'

size 100m

autoextend on

next 50m maxsize 2048m

extent management local;

select * from v$tablespace;

select * from v$datafile;

create user monitor identified by "lCp_mon1"

default tablespace monitor;

grant connect,resource to monitor;

############################

下面是单实例上创建表空间

select * from v$tablespace;

select * from v$datafile;

三步走

create tablespace monitor

logging

datafile '/u01/app/oracle/oradata/testdb/monitor.dbf'

size 100m 

autoextend on 

next 50m maxsize 20480m 

extent management local;

create user monitor identified by "lCp_mon1"

default tablespace monitor;

grant connect,resource to monitor;

以用户monitor通过sqldeveloper连接库，执行monitor.sql脚本即可，将表空间名字替换即可USERS->monitor

#####################################

查看当前会话的session  ID 有如下三种方法：

desc v$mystat

select * from v$mystat where rownum=1;

select userenv('sid') from dual;

userenv('sessionid') 返回的是session audit id.其对应v$session 的audsid字段。

在session 连接到数据库的时候，会从SYS.AUDSES$序列中获取一个audid 分配给session。

select sid from v$session where audsid=userenv('sessionid'); 

这里返回了3个值，我们直接查看userenv('sessionid')  值：

select userenv('sessionid') from dual;

对于internal用户（’/as sysoper’ 和 ‘/as sysdba’）和后台进程，其对应的AUDID 为0. 

在Oracle 10g中，如果AUDID的值为0，表明是internal 用户，如果AUDID 值是4294967295，那么就表明是用SYS 用户直接连接的。 

我们这里返回三个结果是把所有SYS 用户的session都返回了，所以这种方法有时的准确性并不高。

################################

sessions=(1.1*process+5)

show parameter processes

show parameter sessions

alter system set processes=1000 scope=spfile;

shutdown immediate;

startup;

查询数据库当前进程的连接数：

select count(*) from v$process;

查看数据库当前会话的连接数：

select count(*) from v$session;

查看数据库的并发连接数：

select count(*) from v$session where status='ACTIVE';

查看当前数据库建立的会话情况：

select sid,serial#,username,program,machine,status from v$session;

查询数据库允许的最大连接数：

select value from v$parameter where name = 'processes';

##################################

undo表空间管理

你可以建立多个undo表空间，但是当前在用undo表空间只能是其中一个。rac除外

只能是一个,但是可以新建另外一个,把原来的失效,再指向新UNDO表空间

修改默认的undo表空间为新创建的

show parameter undo;

alter system set undo_tablespace=undo1;

undo表空间用于存放undo数据，当执行DML操作（insert、update、delete）时，oracle会将这些操作的旧数据写入到undo段。

知道updata语句执行会产生undo信息，将老的数据保存到undo表空间中。

那么select语句会产生undo信息吗？产生什么信息呢？select会将什么保存的表空间中呢？

insert呢？又会产生undo信息吗？将什么保存到undo表空间呢？

新建一个表，插入十万数据，执行:

sql>select addr, used_ublk from v$transaction;

ADDR USED_UBLK

-------- ----------

3B7D6984 87

产生87块，产生undo信息了吗？表是新建的，没有任何信息，也就是没有老的数据，那么会产生undo信息吗？在undo表空间存入什么呢？

还原段用途：

事务处理回退——当某事务处理修改表中某行时，被修改的列的旧映像（要还原的数据）将存储在还原段中。如果将该事务处理回退，则Oracle 服务器通过将还原段中的值写回到该行来恢复原始值。

事务处理恢复——如果例程在事务处理正在进行时失败，那么Oracle 服务器需要在数据库再次打开时还原所有未提交的更改。这种回退操作是事务处理恢复的一部分。之所以有可能恢复事务处理，原因在于对还原段所做的更改同样受重做日志文件的保护。

读一致性——在事务处理正在进行时，数据库中的其他用户不应看到这些事务处理所做的任何未提交更改。此外，也不应从某条语句中看到该语句开始执行后所提交的任何更改。还原段中的旧值（要还原的数据）也可用于为读者提供给定语句的一致映像。

oracle中undo是用来存放回滚数据的。

撤销（Undo）数据是反转DML语句结果所需的信息。撤销数据通常被称为“回滚数据”，在过去的Oracle版本中，“回滚数据”和“撤销数据”可以交替使用，但从 9i版本开始，这两个术语有所不同：功能相同，但管理方式不同。只要某个事务修改了数据，那么更新前的原有数据就会被写入一个回滚段或撤销段。回滚段在 11g版本中依然存在，但从9i版本开始，Oracle数据库引入了可供选择的撤销段。Oracle强烈建议所有数据库都应当使用撤销段，回滚段只被保留用于向后兼容 。

undo表空间：

一个数据库可以存在多个撤销表空间，但是在任意给定时刻都只能使用一个撤销表空间。撤销表空间必须被创建为持久的、本地管理的并且能够自动扩展分配空间的表空间。

事务与undo段：

在某个事务启动时，Oracle会为其指派一个撤销段。任何一个事务都只能受一个撤销段保护，一个事务生成的撤销数据无法被分配到多个撤销段中。

在某个事务更新表和索引数据块时，回滚该变化所需的信息会被写入指定撤销表空间的数据块。撤销数据在提交后过期的事实意味着可以采用循环方式使用撤销段。如果使用原有的、人工管理的回滚段，那么调整的重要环节是控制具体事务分别受哪些回滚段保护。

oracle会将没有commit或rollback的数据放入undo表空间

update\insert\delete 都会使用undo表空间，

select 应该不会，会使用temp表空间

你不是插入10万的数据了吗，在执行 commit或rollback 之前 会使用undo表空间

########################

temp表空间的管理

临时表空间的主要作用： 索引CREATE或REBUILD； ORDER BY 或 GROUP BY； DISTINCT 操作； UNION 或 INTERSECT 或 MINUS； SORT - MERGE JOINS； ANALYZE.

SELECT 

SE.USERNAME,

SE.SID,

SE.SERIAL#,

SE.SQL_ADDRESS,

SE.MACHINE,

SE.PROGRAM,

SU.TABLESPACE,

SU.SEGTYPE,

SU.CONTENTS

FROM V$SESSION SE,

V$SORT_USAGE SU

WHERE SE.SADDR = SU.SESSION_ADDR;