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) 固定长度的字符串, 不规定长度默认值为1

    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;

表空间已删除。
阅读更多

扫码向博主提问

去开通我的Chat快问

u013412772

非学,无以致疑;非问,无以广识
版权声明:DemoTransfer https://blog.csdn.net/u013412772/article/details/52733825
文章标签: 数据库 oracle
个人分类: 数据库
上一篇Oracle数据库(权限、用户、角色、基本知识介绍)
下一篇Oracle数据库(Oracle存储结构、Oracle运行机制、日期相关的函数、序列、大对象数据类型、表的修改与约束、事务)
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭