Oracle数据库（数据字典、表空间、表的创建、视图）

知识点一. 彻底卸载Oracle

2.1 DBCA删除数据库
   开始 → 程序 → Oracle → 开发与移植工具 → Database Configuration Assistant → 下一步 → 删除数据库(系统会同时删除OracleService+SID)

2.2 Oracle Universal Installer 删除Oracle一部分组件
   开始 → 程序 → Oracle →  Oracle Installation Products → Universal Installer → 卸载产品

2.3 手动删除Oracle的其他组件
    2.3.1 Oracle注册表
       运行 → regedit → 搜索（ctrl + F） → 删除以以下单词开头的文件inst_loc、ora、oracle、orcl、enumora
    2.3.2 环境变量
       path、classpath、oracle_home、oracle_sid、tns_admin等
    2.3.3 重启操作系统
    2.3.4 删除安装目录
       inst_loc 所在位置
       C:\Users\user\AppData\Local\Temp
    2.3.5 删除启动菜单

知识点二. 数据字典（Data Dactionary）

存放于system表空间，Oracle数据库的核心组成部分，主要作用是存放数据库相关的信息，存储的信息主要包括：用户信息、表空间、数据文件信息、数据库对象（表、视图、序列、存储过程等）、权限、角色信息、完整性约束信息、以及其他与数据库相关的信息。数据字典的组成部分主要有一些表和一些视图，创建数据库是Oracle会自动创建。

其中表是真正存放数据的地方。数据以加密的形式存在，数据不需要用户维护，用户也无权操作，由Oracle自己维护。

视图是在表的基础上创建的，数据来源于表，加工处理后形成自己的数据。当表中的数据信息发生改变时，视图数据会自动发生改变。下面列举一些常用的视图：

视图的名称规律如下：
    user_xxx 当前用户可以访问，当前用户相关的数据
    dba_xxx 只有dba用户可以访问，所有用户相关的数据
    all_xxx 有权限访问的用户可以访问，有权限访问的用户相关的数据

常用的视图
     user_tables 当前用户的表信息
     dba_tables 所有用户的表信息
     user_sys_privs 当前用户的系统权限
     user_role_privs 当前用户的角色
     role_sys_privs 角色权限信息
     dictionary | dict 提供了数据字典中所有视图的描述
     dba_sys_privs 所有用户的系统权限
     dba_role_privs 所有用户的角色
     user_users 当前用户的信息
     dba_users 所有用户的信息

oracle中，创建视图的基本方式是：
    create or replace view  视图名称  as  + 查询语句

用sql语句查看oracle视图创建语句：

    1.查看所有视图的名字
    select view_name from all_views; 或者select view_name from user_views;

    2.查看某视图名为“某某视图”的创建语句
    select text from all_views where view_name = '某某视图';

对象本身的信息都是存放在数据字典中，表的结构信息是放在数据字典中的，表的数据放在默认或指定的表空间下的数据文件中。

知识点三、表空间（tablespace）代码实例详见附录二

数据库指的就是一些文件，文件在硬盘上，一个数据库由若干个表空间组成，一个表空间由若干个数据文件组成，一个数据文件由若干个分区组成。其中分区（extend）为逻辑结构，人为虚构的，一个分区是数据我文件中一段连续的存储空间。数据文件（datafiles）是数据的物理载体，后缀名为.dbf，数据库中的所有数据都存放在数据文件中，数据文件不可以过大，过大会影响数据的存取性能。

表空间是逻辑结构，并不是物理分割成的，数据库创建的时候，自动创建：
系统表空间system：数据字典使用的就是该表空间
临时表空间temp：主要用于排序

    create tablespace 表空间名 datafile 'D：//myfile.dbf(数据文件路径)' size xM(文件大小),… extent management local uniform size yM(容量大小);
    datafile 用于指定创建的表空间下的数据文件
    extent manager local 用于指定表空间的管理为本地管理，要求分区，大小一致
    uniform 用于指定分区的统一大小

查找某张表使用的表空间
   select tablespace_name from user_tables where table_name = '表名';
   select tablespace_name from dba_tables where table_name = '表名';

查找默认表空间'USERS'有哪些文件
   select file_name from dba_data_files where tablespace_name = 'USERS';

查找用户的默认表空间
   select default_tablespace from user_users; 当前用户
   select default_tablespace from dba_users; 所有dba用户

扩充表空间
   alter tablespace 表空间名 add datafile '数据文件路径' size xxM(大小);

创建用户的时指定表空间
   create user username identified by password [account lock | unlock][password expire(设置密码过期)][default tablespace 表空间名];
default tablespace 表空间名：该用户创建的所有对象的数据都将存放在该表空间下
表空间中的文件
   *.dbf DatabaseFile ：数据文件 
   *.ctl Control ：控制文件
   *.log ：重做日志文件

知识点四、SQL（Structured Query Language）

SQL(Structured Query Language)即结构化查询语句，应用程序与数据库交互的接口，集数据操作、数据定义、数据控制等功能于一体，ANSI先后制定推出了SQL-89、SQL-92、SQL-99标准。
Oracle SQL 语句主要分为一下四类：
DML(Data Mannipulation Language)数据操纵语言：查询、操纵数据表资料行

SELECT : 检索数据库表或视图数据 

INSERT :  将数据行新增至数据库表或视图中

UPDATE : 修改表或视图中现有的数据行

DELETE : 删除表或视图中现有的数据行

注意：DML语句不会自动提交事务！
DDL(Data Definition Language)数据定义语言：建立、修改、删除数据库中数据表对象

CREATE TABLE : 创建表 

ALTER TABLE : 修改表

DROP TABLE : 删除表

注意：DLL语句会自动提交事务！所以：DML语句事务提交之前可以回滚，DDL语句不能回滚事务
DCL(Data Control Language)数据控制语言：用于执行权限授予与收回操作

GRANT : 给用户或角色授予权限

REVOKE : 收回用户或角色的所有权限

TCL(Transactional Control Language)事物控制语言：维护数据的一致性

COMMIT :提交已经进行的数据库改变

ROLLBACK : 回滚已经进行的数据改变

SAVEPOINT : 设置保存点，用于部分数据改变的取消

其中SQL关键字不区分大小写，对象名与列名不区分大小写，字符串值区分大小写，即''里面的内容区分大小写

注意：数据字典自动将数据转换成大写

SQL语句运行的过程：
    客户端把SQL语句发送到服务端，服务器对SQL进行编译，执行，服务器把执行结果再发挥给客户端

知识点五、表的创建

1、创建表基本语法：

创建表：
CREATE TABLE DEPT(
EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)) ;
CREATE TABLE region(
ID number(2) NOT NULL PRIMARY KEY,
postcode number(6) default '0' NOT NULL,
areaname varchar2(30) default ' ' NOT NULL);

2、创建表时的命名规则和注意事项

1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#
2)大小写不区分
3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来．
4)用和实体或属性相关的英文符号长度有一定的限制
注意事项:
1)建表时可以用中文的字段名, 但最好还是用英文的字段名
2)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
3)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引
4)一个表的最多字段个数也是有限制的，254个.

3、Oracle常用的字段类型

ORACLE常用的字段类型：

    VARCHAR2 (size) 可变长度的字符串, 必须规定长度

    CHAR(size) 固定长度的字符串, 不规定长度默认值为１

    NUMBER(p,s) 数字型p是位数总长度, s是小数的长度, 可存负数最长38位. 不够位时会四舍五入.

    DATE 日期和时间类型

    LOB 超长字符, 最大可达4G

    CLOB 超长文本字符串

    BLOB 超长二进制字符串

    BFILE 超长二进制字符串, 保存在数据库外的文件里是只读的.

    数字字段类型位数及其四舍五入的结果

    数字字段类型位数 存储的值

 Oracle内置类型

      varchar2 长度可变的字符串，且使用时必须指定长度varchar2(n),长度单位为字节，最大长度为4000字节;

      char 固定长度的字符串，默认长度为1，单位：字节，最大长度2000字节;

      number 数值类型，既可以表示整数，也可以表示浮点数，
         number(p,s)：
         p表示整个数值的长度，不包含小数点
         s表示小数占的长度
         注意：
            没有指定小数的长度，插入的数据如果带小数，则小数位四舍五入
            如果小数长度不足以保存插入的数据，在精度允许的下一位开始四舍五入
            如果指定的小数长度是负数，则表示整数，整数长度为p-s

      date 时间和日期数据：
         select to_char(列名，'yy-mm-dd hh24:mi:ss') from 表名
         timestamp 时间和日期数据，包含了上下午标识，6位的微秒，时区

附录一：

一、 Oracle常用数据字典表

1、 查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users; 
2、 查看当前用户的角色
SQL>select * from user_role_privs;
3、 查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
4、 查看用户下所有的表
SQL>select * from user_tables;
5、 查看用户下所有的表的列属性
SQL>select * from USER_TAB_COLUMNS where table_name=:table_Name;
6、 显示用户信息(所属表空间)
select default_tablespace, temporary_tablespace
  from dba_users  www.2cto.com  
 where username = 'GAME';
7、 显示当前会话所具有的权限
SQL>select * from session_privs;
8、 显示指定用户所具有的系统权限
SQL>select * from dba_sys_privs where grantee='GAME';
9、 显示特权用户
select * from v$pwfile_users;
10、 显示用户信息(所属表空间)
select default_tablespace,temporary_tablespace 
from dba_users where username='GAME';
11、 显示用户的PROFILE
select profile from dba_users where username='GAME'; 

二、表

1、 查看用户下所有的表
SQL>select * from user_tables;
2、 查看名称包含log字符的表
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
3、 查看某表的创建时间
SQL>select object_name,created from user_objects where object_name=upper('&table_name');
4、 查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
5、 查看放在Oracle的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 

三、索引

1、 查看索引个数和类别
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
2、 查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
3、 查看索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');

四、序列号

1、 查看序列号，last_number是当前值
SQL>select * from user_sequences;

五、视图

1、 查看视图的名称
SQL>select view_name from user_views;
2、 查看创建视图的select语句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name'); 

六、同义词

1、 查看同义词的名称
SQL>select * from user_synonyms; 

七、约束条件

1、 查看某表的约束条件
    SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
    from user_constraints where table_name = upper('&table_name');
    SQL>select c.constraint_name,c.constraint_type,cc.column_name
    from user_constraints c,user_cons_columns cc
    where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
    and c.owner = cc.owner and c.constraint_name = cc.constraint_name
    order by cc.position;

八、存储函数和过程

1、 查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
2、 查看函数和过程的源代码
SQL>select text from all_source where owner=user and name=upper('&plsql_name'); 

九、常用的数据字典

dba_data_files:通常用来查询关于数据库文件的信息
dba_db_links:包括数据库中的所有数据库链路，也就是databaselinks。
dba_extents:数据库中所有分区的信息
dba_free_space:所有表空间中的自由分区
dba_indexs:关于数据库中所有索引的描述
dba_ind_columns:在所有表及聚集上压缩索引的列
dba_objects:数据库中所有的对象
dba_rollback_segs:回滚段的描述
dba_segments:所有数据库段分段的存储空间
dba_synonyms:关于同义词的信息查询
dba_tables:数据库中所有数据表的描述
dba_tabespaces:关于表空间的信息
dba_tab_columns:所有表描述、视图以及聚集的列
dba_tab_grants/privs:对象所授予的权限
dba_ts_quotas:所有用户表空间限额
dba_users:关于数据的所有用户的信息
dba_views:数据库中所有视图的文本

十、常用的动态性能视图

v$datafile：数据库使用的数据文件信息
v$librarycache：共享池中SQL语句的管理信息
v$lock：通过访问数据库会话，设置对象锁的所有信息
v$log：从控制文件中提取有关重做日志组的信息
v$logfile有关实例重置日志组文件名及其位置的信息
v$parameter：初始化参数文件中所有项的值
v$process：当前进程的信息  www.2cto.com  
v$rollname：回滚段信息
v$rollstat：联机回滚段统计信息
v$rowcache：内存中数据字典活动/性能信息
v$session:有关会话的信息
v$sesstat：在v$session中报告当前会话的统计信息
v$sqlarea：共享池中使用当前光标的统计信息，光标是一块内存区域，有Oracle处理SQL语句时打开。
v$statname：在v$sesstat中报告各个统计的含义
v$sysstat：基于当前操作会话进行的系统统计
v$waitstat：出现一个以上会话访问数据库的数据时的详细情况。当有一个以上的会话访问同一信息时，可出现等待情况。
总结了一下这些，彻底区别了视图与数据字典，也不那么容易混淆。嘿嘿!!!

十一、常用SQL查询

1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status, 
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, 
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;

4、查看控制文件
select name from v$controlfile;

5、查看日志文件
select member from v$logfile;

6、查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name 
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

7、查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

8、查看数据库的版本　
Select version FROM Product_component_version 
Where SUBSTR(PRODUCT,1,6)='Oracle';

9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database; 

10、捕捉运行很久的SQL
column username format a12 
column opname format a16 
column progress format a8 
select username,sid,opname, 
round(sofar*100 / totalwork,0) || '%' as progress, 
time_remaining,sql_text 
from v$session_longops , v$sql 
where time_remaining <> 0 
and sql_address = address 
and sql_hash_value = hash_value 

11、查看数据表的参数信息
SELECT   partition_name, high_value, high_value_length, tablespace_name,
pct_free, pct_used, ini_trans, max_trans, initial_extent,
next_extent, min_extent, max_extent, pct_increase, FREELISTS,
freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
last_analyzed
FROM dba_tab_partitions
--WHERE table_name = :tname AND table_owner = :towner
ORDER BY partition_position

12、查看还没提交的事务
select * from v$locked_object;
select * from v$transaction

C:\Users\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 10月 5 11:23:58 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

请输入用户名:  scott
输入口令:

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect sys/root as sysdba;
已连接。
SQL> show user;
USER 为 "SYS"
SQL> select username from dba_users;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
ZHANGSAN
SCOTT
TEST
TEST_USER
ROBINSON
OUTLN

USERNAME
------------------------------
MDSYS
ORDSYS
EXFSYS
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS

USERNAME
------------------------------
TSMSYS
BI
PM
MDDATA
IX
SH
DIP
OE
HR

已选择31行。

SQL> --上述是利用dba_users数据字典
SQL> drop user mary cascade;
drop user mary cascade
          *
第 1 行出现错误:
ORA-01918: 用户 'MARY' 不存在


SQL> create user jack identified by jack;

用户已创建。

SQL> frant dba to jack;
SP2-0734: 未知的命令开头 "frant dba ..." - 忽略了剩余的行。
SQL> grant dba to jack;

授权成功。

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
FUND

已选择7行。

SQL> select tablespaces_name from user_tablespaces;
select tablespaces_name from user_tablespaces
       *
第 1 行出现错误:
ORA-00904: "TABLESPACES_NAME": 标识符无效


SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
FUND

已选择7行。

SQL> create tablespace test1_tablespace datafile 'test1file.dbf' size 10M;

表空间已创建。

SQL> --上面是创建永久表空间
SQL> --下面是创建临时表空间
SQL> create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10M;

表空间已创建。

SQL> select file_name from dba_dta_files where tablespace_name 'TEST1_TABLESPACE';
select file_name from dba_dta_files where tablespace_name 'TEST1_TABLESPACE'
                                                          *
第 1 行出现错误:
ORA-00920: 无效的关系运算符


SQL> select file_name from dba_data_files where tablespace_name = 'TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1FILE.DBF

SQL> select file_name from dba_temp_file where tablespace_name = 'TEMPTEST1_TABLESPACE';
select file_name from dba_temp_file where tablespace_name = 'TEMPTEST1_TABLESPACE'
                      *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> select file_name from dba_temp_files where tablespace_name = 'TEMPTEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEMPFILE1.DBF

SQL> alter tablespace test1_tablespace read only;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';

STATUS
---------
READ ONLY

SQL> alter tablespace test1_tablespace read write;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';

STATUS
---------
ONLINE

SQL> alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10M;

表空间已更改。

SQL> select file_name from dba_date_files where tablespace_name = 'TEST_TABLESPACE';
select file_name from dba_date_files where tablespace_name = 'TEST_TABLESPACE'
                      *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> select file_name from dba_data_files where tablespace_name = 'TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1FILE.DBF
E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST2_FILE.DBF

SQL> alter tablespace tablespace_name drop datefile 'test2_file.dbf';
alter tablespace tablespace_name drop datefile 'test2_file.dbf'
                                      *
第 1 行出现错误:
ORA-00905: 缺失关键字


SQL> alter tablespace tablespace_name drop datafile 'test2_file.dbf';
alter tablespace tablespace_name drop datafile 'test2_file.dbf'
*
第 1 行出现错误:
ORA-00959: 表空间 'TABLESPACE_NAME' 不存在


SQL> alter tablespace test1_tablespace drop datafile 'test2_file.dbf';

表空间已更改。

SQL> select file_name from dba_data_files where tablespace_name = 'TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1FILE.DBF

SQL> drop tablespace test1_tablespace including contents;

表空间已删除。