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

知识点引用：

http://www.2cto.com/database/201207/142874.html
http://blog.csdn.net/haiross/article/details/11772847

知识点一. 彻底卸载Oracle

方式1、重装操作系统

方式2、

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 = '某某视图';

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

附录一：Oracle语句练习

知识点三、表空间（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进行编译，执行，服务器把执行结果再发挥给客户端

知识点五、表的创建

Oracle数据库中创建表语句(Create table)语法详解及示例详见：http://blog.csdn.net/haiross/article/details/11772847

1、创建表基本语法：
create table 表名(列定义列表) [tablespace 表空间名];
列表定义： 至少要有一列定义（列名 类型）

创建表：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、 显示用户的PROFILEselect profile from dba_users where username='GAME'; 二、表1、 查看用户下所有的表SQL>select * from user_tables;2、 查看名称包含log字符的表SQL>select object_name,object_id from user_objectswhere 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_segmentswhere 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_segmentswhere 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_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;2、查看表空间物理文件的名称及大小select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;3、查看回滚段名称及大小select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere 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_spacegroup 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 CWHERE 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、捕捉运行很久的SQLcolumn 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_analyzedFROM dba_tab_partitions--WHERE table_name = :tname AND table_owner = :townerORDER BY partition_position12、查看还没提交的事务select * from v$locked_object;select * from v$transaction; 

附录二、

C:\Users\Administrator>sqlplusSQL*Plus: Release 10.2.0.1.0 - Production on 星期三 10月 5 11:23:58 2016Copyright (c) 1982, 2005, Oracle.  All rights reserved.请输入用户名:  scott输入口令:连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> connect sys/root as sysdba;已连接。SQL> show user;USER 为 "SYS"SQL> select username from dba_users;USERNAME------------------------------MGMT_VIEWSYSSYSTEMDBSNMPSYSMANZHANGSANSCOTTTESTTEST_USERROBINSONOUTLNUSERNAME------------------------------MDSYSORDSYSEXFSYSDMSYSWMSYSCTXSYSANONYMOUSXDBORDPLUGINSSI_INFORMTN_SCHEMAOLAPSYSUSERNAME------------------------------TSMSYSBIPMMDDATAIXSHDIPOEHR已选择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------------------------------SYSTEMUNDOTBS1SYSAUXTEMPUSERSEXAMPLEFUND已选择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------------------------------SYSTEMUNDOTBS1SYSAUXTEMPUSERSEXAMPLEFUND已选择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.DBFSQL> 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.DBFSQL> alter tablespace test1_tablespace read only;表空间已更改。SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';STATUS---------READ ONLYSQL> alter tablespace test1_tablespace read write;表空间已更改。SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';STATUS---------ONLINESQL> 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.DBFE:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST2_FILE.DBFSQL> 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.DBFSQL> drop tablespace test1_tablespace including contents;表空间已删除。