下面为数据库实验的一些报告(oracle)(第四—六周)

可直接下载资源:https://download.csdn.net/download/qq_42785226/14622190

这些报告每次大概是一节课的时间,由于课程原因,很多内容都简化了,知识内容也可能相对片面一些,并不全面,只作练习参考使用。

csdn不允许文字长度过长,这里没办法只好拆成三份。

数据库实验报告

数据库实验报告 1

数据库实验第四周 5

一、准备工作: 5

SQL*Plus创建公共用户C##scott: 5

二、 实验阶段(SQL题目练习): 7

1、列出至少有一个员工的所有部门。 8

2、列出薪金比“SMITH”多的所有员工。 8

3、列出所有员工的姓名及其直接上级的姓名。 9

4、 列出受雇日期早于其直接上级的所有员工。 10

5、 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 10

6、列出所有“CLERK”(办事员)的姓名及其部门名称。 11

7、列出最低薪金大于1500的各种工作。 12

8、列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。 12

9、列出薪金高于公司平均薪金的所有员工。 12

10、列出与“SCOTT”从事相同工作的所有员工。 13

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 13

12、 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 13

14、列出所有员工的姓名、部门名称和工资。 16

15、列出所有部门的详细信息和部门人数。 17

16、列出各种工作的最低工资。 17

17、列出各个部门的MANAGER(经理)的最低薪金。 17

数据库实验第五周 18

Dual操作(DESC,查询内容,日期(格式),作计算器等): 18

临时变量&: 19

模拟merge并查看结果: 20

扩展update(Returning): 21

%rowtype: 22

%type: 23

Select   ..as..: 23

Dual查询user、日期(带特定格式)、生成随机数: 24

定义(declare)(常量、变量)、赋值(:=)操作练习: 24

IF、 ELSIF、 ENDIF练习(注意为:ELSIF): 25

Case语句练习: 26

循环语句(LOOP——EXIT;)练习: 26

数据库实验第六周 27

导入表:SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp; 27

黑屏下查询归档文件信息:SQL> conn /as sysdba; SQL> ArCHIVE Log list; 28

查询表信息:SQL> select * from cat; 28

查询用户信息:SQL> select * from dba_users; 33

查询数据文件信息dba_data_files:select * from dba_data_files; 36

select * from v$datafile; 37

查询归档文件信息:select name,log_mode from v$database; 38

查询控制文件信息:select * from v$controlfile; 38

查询日志文件信息:select * from v$log; 38

select * from v$logfile; 39

按组添加日志文件LOGFILE并作出相关查询:ALTER DATABASE ADD LOGFILE GROUP 4('log1a.rdo','log2a.rdo')size 5000k; 39

按成员MEMBER添加日志文件LOGFILE到组GROUP并作出相关查询:ALTER DATABASE ADD LOGFILE MEMBER 'log3a.log' TO Group 4; 40

按成员删除日志文件:ALTER DATABASE DROP LOGFILE MEMBER 'log3a.log'; 40

按组删除日志文件:ALTER DATABASE DROP LOGFILE  Group 4; 41

创建表空间: 41

数据库实验第八周 42

查询数据块大小:SHOW PARAMETER db_block_size; 42

查询用户分区信息:select * from user_extents; 42

归档模式与非归档模式: 42

ARCHIVE LOG LIST;黑屏模式下进行归档模式查询 42

SQL> SELECT name,log_mode FROM v$database; 43

以下四步将数据库从非归档模式转化为归档模式(黑屏下): 43

可能出现的错误: 43

数据库实验第九周 45

Command: 45

/*分支结构1*/ 45

/*分支结构2*/ 45

/*带临时变量的多分支结构*/ 46

/*多分支case语句*/ 47

/* 47

1.简单型case 47

2.搜索性case 47

3.嵌入到select语句执行复杂任务的case 47

4.嵌入到PL/SQL程序语句(如赋值语句)的case 47

*/ 47

/*简单型case*/ 47

/*等值比较的case语句*/ 48

/*搜索case表达式*/ 48

/*嵌入到select语句执行复杂任务的case*/ 48

/*将上述例子的结果以表的形式保存起来*/ 49

/*case的select练习*/ 49

/*对于学生借阅的图书信息,检验图书是否过期 49

1.过期 49

2.没过期 49

3.没有借阅图书*/ 49

--日期简单练习: 49

select to_char(months_between(sysdate,to_date('20151001','yyyymmdd'))) from dual; 50

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL; 50

select trunc(sysdate-to_date('20181001','yyyymmdd'))天数 from dual; 50

/*嵌入到pl/sql程序语句(如赋值语句)的case*/ 50

实现: 50

数据库实验第十周 57

创建分区表: 57

查询分区表: 58

SQL> select * from part_book1; 58

SQL> select * from part_book1 partition(part1); 58

SQL> select * from part_book1 partition(part2); 58

SQL>select * from dba_part_tables; 58

SQL> select * from dba_part_tables where table_name='PART_BOOK1'; 59

修改分区表: 60

SQL> Alter table part_book1 add partition part4 values('北京邮电出版社') tablespace system; 60

Command: 60

查询图书是否过期及应缴金额(select--case查询) 60

查询优良等级(select--case查询) 61

循环练习——exit when+for逆序循环(for count in reserve count_1..count_10) 61

while和for循环练习(1+2+3=6) 62

实现: 62

数据库实验第十一周 68

创建表空间: 68

查询数据文件: 70

在删除时将表空间中的内容和数据文件全部删除: 74

创建临时表空间tmptbs: 74

创建大文件表空间: 74

创建撤销表空间: 74

表空间和数据文件的维护: 74

将表空间test1设置为脱机状态: 74

更改表空间名字: 75

在现有表空间基础上添加一数据文件: 75

对已创建的表空间中已有的数据文件的管理: 75

移动表空间中的数据文件: 75

读写状态修改: 76

将表空间table2设置为只读表空间 76

将表空间table2设置为可读写状态: 76

数据库实验第十二周 76

不带参数的显式游标举例: 76

游标的%isopen 属性练习: 77

带 return和参数传递的游标: 77

一、利用while循环检索游标 79

二、利用for循环检索游标 79

使用游标分别遍历xs表中的xh,zxf: 80

利用游标WHILE循环统计并输出scott.emp表各个部门的平均工资, 若平均工资大于2000,则输出“该部门平均工资较高。” 81

利用FOR循环统计并输出各个部门的平均工资。 82

数据库实验第十三周 83

带update的游标,loop EXIT WHEN -end loop;Scott.emp表,利用游标,给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新” 83

for update写法: 84

for循环  FOR v_emp IN c_emp LOOP -END LOOP;修改scott.emp表员工的工资,如果员工的部门号为10,工资提高100;部门号为20,工资提高150;部门号为30,工资提高200;否则工资提高250。 85

修改emp表的工资,工资不足1000的,调整为1500,工资高于1000的,调整为原来工资的1.5倍,调整后,若工资〉10000,则设其为10000。 86

游标变量: 87

数据库实验第十四周 88

存储过程样例: 88

存储过程练习: 89

数据库实验第十五周 92

创建触发器(在删除xs表中的数据时进行备份): 92

将对xs表的操作记录(insert,delete,update)在sql_info表中记录下来: 92

创建一个触发器,输出对scott.emp表的操作(插入后员工号和员工名,更新后员工工资,删除的员工号和员工名。): 93

当执行插入操作时,统计操作后员工人数;当执行更新工资操作时,统计更新后员工平均工资;当执行删除操作时,统计删除后各个部门剩余的人数(游标): 94

数据库实验第十六周 95

创建一个存储过程,以部门号为该存储过程的in类型参数,查询该部门的平均工资,并输出该部门中比平均工资高的员工号、员工名。(for 循环遍历  游标): 95

用存储过程进行模糊查找,如查找ename中包含L的雇员信息: 96

rownum练习: 96

补充:见存储过程需要注意的问题,异常处理与触发器补充 97

补充内容 98

循环结构: 98

求10的阶乘: 98

水仙花数: 102

触发器补充: 103

设置系统触发器: 103

创建logon触发器,在登录的时候进行记录: 103

创建logoff触发器,在退出登录的时候进行记录(注意此时的触发器时间上不要采用after,因为采用after客户机注销进程终止,不能进行其他操作): 105

设置触发器:作用为禁止在休息日(周六、周天)改变scott.emp雇员信息(包括添加删除和修改)。 107

启动和关闭触发器 108

存储过程需要注意的一些问题 108

异常处理(系统异常处理以及自定义异常处理) 113

 

数据库实验第四周

一、准备工作:

电脑上安装的oracle版本为12c企业版和第三方工具PL/SQL Develop,12c似乎没有支持scott用户(但是安装目录下面确实存在scott.sql,有些疑惑),执行conn scott/tiger验证失败,经验证并非是加锁原因(alert user scott account unlock;),而是不存在该用户,管理员查询scott.emp,也查询不到表,执行@H:\Oracle\oracle\product\12.2.0\dbhome_1\rdbms\admin\scott.sql导入命令也没有成功,于是重新建立c##scott用户(这里建立公共用户,12c版本之后要在公共用户之后添加C##或者c##),在该用户下建立新的数据表。

 

SQL*Plus创建公共用户C##scott:

SQL*Plus: Release 12.2.0.1.0 Production on 星期日 9月 27 09:13:38 2020

 

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

 

SQL> conn /as sysdba

已连接。

SQL> CREATE USER scott IDENTIFIED BY tiger ;

CREATE USER scott IDENTIFIED BY tiger

            *

第 1 行出现错误:

ORA-65096: 公用用户名或角色名无效

 

SQL> CREATE USER c##scott IDENTIFIED BY tiger ;

 

用户已创建。

 

SQL> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO c##scott CONTAINER=ALL ;

 

授权成功。

 

SQL> ALTER USER c##scott DEFAULT TABLESPACE USERS;

 

用户已更改。

 

SQL> ALTER USER c##scott TEMPORARY TABLESPACE TEMP;

 

用户已更改。

 

登录scott用户将H:\Oracle\oracle\product\12.2.0\dbhome_1\rdbms\admin\scott.sql下的sql语句放到SQLDevelop下执行一遍,成功获取数据:

 

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

Connected as system@ORCL

 

SQL> show user;

User is "SYSTEM"

 

SQL> select * from C##scott.emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

 

14 rows selected

 

 

SQL> conn C##scott/tiger

SQL>

SQL> conn C##scott/tiger

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

Connected as C##scott

 

SQL> select * from emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

 

14 rows selected

 

1、列出至少有一个员工的所有部门。

SQL> select deptno, count(*) from emp group by DEPTNO having count(*) >1;

 

DEPTNO   COUNT(*)

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

    30          6

    20          5

    10          3

 

2、列出薪金比“SMITH”多的所有员工。

SQL> select * from emp where SAL > (select SAL from emp where ename='SMITH');

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

 

13 rows selected

 

 

3、列出所有员工的姓名及其直接上级的姓名。

SQL> select e0.ENAME, e1.ename from emp e0,emp e1 where e0.MGR=e1.empno;

 

ENAME      ENAME

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

FORD       JONES

SCOTT      JONES

TURNER     BLAKE

ALLEN      BLAKE

WARD       BLAKE

JAMES      BLAKE

MARTIN     BLAKE

MILLER     CLARK

ADAMS      SCOTT

BLAKE      KING

JONES      KING

CLARK      KING

SMITH      FORD

 

13 rows selected

 

  1. 列出受雇日期早于其直接上级的所有员工。

分析:上述早于即小于(时间)

用(+)来实现, 这个+号可以这样来理解: + 表示补充,即哪个表有加号,这个表就是匹配表。如果加号写在右表,左表就是全部显示,所以是左连接。

这里以e0为员工,e1为其直接上级:

SQL> select e0.*,e1.* from emp e0,emp e1 where e0.mgr=e1.empno(+) and e0.hiredate<e1.hiredate;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20  7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30  7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30  7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30  7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20  7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10  7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20  7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 

7 rows selected

 

  1. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

分析:同时列出那些没有员工的部门,即列出所有部门,我们采用左外连接:

SQL> select d.dname,e.* from dept d,emp e where d.deptno=e.deptno(+);

 

DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

ACCOUNTING      7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

ACCOUNTING      7839 KING       PRESIDENT       1981/11/17    5000.00               10

ACCOUNTING      7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

RESEARCH        7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

RESEARCH        7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

RESEARCH        7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

RESEARCH        7369 SMITH      CLERK      7902 1980/12/17     800.00               20

RESEARCH        7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

SALES           7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

SALES           7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

SALES           7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

SALES           7900 JAMES      CLERK      7698 1981/12/3      950.00               30

SALES           7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

SALES           7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

OPERATIONS                                                                      

 

15 rows selected

 

6、列出所有“CLERK”(办事员)的姓名及其部门名称。

SQL> select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK';

 

ENAME      DNAME

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

MILLER     ACCOUNTING

SMITH      RESEARCH

ADAMS      RESEARCH

JAMES      SALES

 

上述采用的是外连接,也可以采用多表查询等方式:

select ename,dname from emp, dept where emp.job='CLERK' and emp.deptno=dept.deptno;

 

7、列出最低薪金大于1500的各种工作。

SQL> select job from emp group by job having min(sal) > 1500;

 

JOB

---------

PRESIDENT

MANAGER

ANALYST

 

8、列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。

SQL> select e.ename from emp e, dept d where d.dname = 'SALES' and e.deptno(+) = d.deptno;

 

ENAME

----------

WARD

TURNER

ALLEN

JAMES

BLAKE

MARTIN

 

6 rows selected

 

9、列出薪金高于公司平均薪金的所有员工。

SQL> select * from emp where sal > (select avg(sal) from emp);

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 

6 rows selected

 

10、列出与“SCOTT”从事相同工作的所有员工。

SQL> select * from emp where job = (select job from emp where ename = 'SCOTT');

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

SQL> select * from emp where sal in (select sal from emp where deptno = 30);

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 

6 rows selected

 

  1. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

方式一:

SQL> select * from emp where sal > all (select sal from emp where deptno = 30);

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

方式二:

上述实现也可以采用如下方式(首先获取部门为30的最高工资(利用desc降序排列和rownum获取最高工资)):

SQL> select * from emp where sal >

  2   (select sal from

  3      (select sal from emp where deptno=30 order by sal desc)

  4        where rownum<=1);

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 

(注意:ASC是升序排列,在rownum那里要进行嵌套查询,order by要写到where子句之后,否则会报ORA-00933: SQL 命令未正确结束错误,group by也要写到where子句之后):

SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

例如:

SQL> select sal,deptno from emp

  2  order by sal desc

  3  where deptno=30

  4   ;

select sal,deptno from emp

order by sal desc

where deptno=30

 

ORA-00933: SQL 命令未正确结束

 

 

SQL> select count(empno) 人数, avg(sal) 平均5261工资4102, avg(EXTRACT(year FROM sysdate) - EXTRACT(year FROM emp.hiredate)) 平均雇佣期限1653 from dual, emp group by deptno;

 

        人数 平均5261工资4102 平均雇佣期限1653

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

         6 1566.6666666         39

         5         2175      796.8

         3 2916.6666666 38.6666666

 

SQL> select e.ename, d.dname, e.sal from emp e, scott.dept d  where e.deptno = d.deptno(+);

select e.ename, d.dname, e.sal from emp e, scott.dept d  where e.deptno = d.deptno(+)

 

ORA-00942: 表或视图不存在

 

上述rownum是oracle中的top子句(取前number行数据):

top子句:

 

SQLServer用法:

SELECT TOP number|percent column_name(s)

FROM table_name

例(percent百分数):

SELECT TOP 2 * FROM Persons

从上面的 "Persons" 表中选取 50% 的记录:

SELECT TOP 50 PERCENT * FROM Persons

 

MySQL 和 Oracle 中的 SQL SELECT TOP 是等价的:

MySQL 语法:

SELECT column_name(s)

FROM table_name

LIMIT number

Oracle 语法:

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number

例(MySQL(limit)):

Oracle为rownum,这里不再多说。

需要注意的一点就是这几个top子句都是符合不同数据库的语法,不能混用:

例(在oracle中使用mysql中的limit top子句则行不通):

同时在oracle中和mysql中使用SqlServer中的top子句也不行。

14、列出所有员工的姓名、部门名称和工资。

SQL> select e.ename, d.dname, e.sal from emp e, dept d  where e.deptno = d.deptno(+);

 

ENAME      DNAME                SAL

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

CLARK      ACCOUNTING       2450.00

KING       ACCOUNTING       5000.00

MILLER     ACCOUNTING       1300.00

SMITH      RESEARCH          800.00

JONES      RESEARCH         2975.00

SCOTT      RESEARCH         3000.00

ADAMS      RESEARCH         1100.00

FORD       RESEARCH         3000.00

ALLEN      SALES            1600.00

WARD       SALES            1250.00

MARTIN     SALES            1250.00

BLAKE      SALES            2850.00

TURNER     SALES            1500.00

JAMES      SALES             950.00

 

14 rows selected

 

15、列出所有部门的详细信息和部门人数。

SQL> select d.*, t.count from dept d, (select deptno, count(empno) count from emp group by deptno) t where d.deptno = t.deptno(+);

 

DEPTNO DNAME          LOC                COUNT

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

    10 ACCOUNTING     NEW YORK               3

    20 RESEARCH       DALLAS                 5

    30 SALES          CHICAGO                6

    40 OPERATIONS     BOSTON        

16、列出各种工作的最低工资。

SQL> select job, min(sal) from emp group by job;

 

JOB         MIN(SAL)

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

CLERK            800

SALESMAN        1250

PRESIDENT       5000

MANAGER         2450

ANALYST         3000

17、列出各个部门的MANAGER(经理)的最低薪金。

SQL> select deptno, min(sal) from emp where job = 'MANAGER' group by deptno;

 

DEPTNO   MIN(SAL)

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

    30       2850

    20       2975

    10       2450

 

 

 

数据库实验第五周

Dual操作(DESC,查询内容,日期(格式),作计算器等):

SQL> SELECT * FROM DUAL;

 

DUMMY

-----

X

 

SQL> SELECT 1+1 FROM DUAL;

 

       1+1

----------

         2

 

SQL> SELECT SYSDATE FROM DUAL;

 

SYSDATE

-----------

2020/10/2 1

 

SQL> SELECT TO_CHAR(SYSDATE,'DY')FROM DUAL;

 

TO_CHAR(SYSDATE,'DY')

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

星期五

 

SQL> DESC DUAL;

Name  Type        Nullable Default Comments

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

DUMMY VARCHAR2(1) Y                        

 

 

 

 

临时变量&:

 

SQL> SELECT &a1+1 FROM DUAL;

 

       1+1

----------

         2

 

SQL> SELECT &a1+1 FROM DUAL;

 

       2+1

----------

         3

 

SQL> SELECT &&a1+1 FROM DUAL;

 

       5+1

----------

         6

 

SQL> SELECT &a1+1 FROM DUAL;

 

       5+1

----------

         6

 

 

 

 

SQL> select * from xs;

 

XH     XM     ZYM    XB CSSJ        ZXF BZ

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

061101 王林   计算机 男 1986/2/10    50

101112 李明   计算机 男 1986/1/30    36

001    张琼   计算机                 45 三好学生

121112 王小二 计算机 男 1986/1/30    36

 

SQL> select * from kc;

 

KCH KCM              KKXQ  XS XF

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

001 001                 1  11  1

001 001                 1  11  1

 

SQL> select * from xs_kc;

 

XH     KCH                                         CJ  XF

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

061101 101                                         80

061101 102                                         78

061101 206                                         76

061103 101                                         82

061103 102                                         82

061103 206                                         83

061104 101                                         90

061107 101                                         98

061107 102                                         80

 

9 rows selected

 

模拟merge并查看结果:

SQL> SET ServerOutput ON;

SQL> DECLARE

  2      /*定义变量类型,注意:=*/

  3      v_xm varchar2(8):='Jame';

  4      v_zym varchar2(10):='计算机';

  5      v_zxf number(2):=45;

  6  BEGIN

  7      UPDATE XS SET zxf=v_zxf WHERE xm=v_xm;

  8      IF SQL%NOTFOUND THEN --SQL%NOTFOUND用于判断update是否执行成功执行成功则sql found即SQL%NOTFOUND返回false,反之即执行失败返回true

  9        dbms_output.put_line('没有该人,需要插入该人');

 10        INSERT INTO XS(XH,XM,ZYM,ZXF)VALUES('007',v_xm,v_zym,v_zxf);

 11      END IF;

 12  END;

 13  /

 

没有该人,需要插入该人

 

PL/SQL procedure successfully completed

 

 

SQL> DECLARE

  2      /*定义变量类型,注意:=*/

  3      v_xm varchar2(8):='Jame';

  4      v_zym varchar2(10):='计算机';

  5      v_zxf number(2):=45;

  6  BEGIN

  7      UPDATE XS SET zxf=v_zxf WHERE xm=v_xm;

  8      IF SQL%NOTFOUND THEN --SQL%NOTFOUND用于判断update是否执行成功执行成功则sql found即SQL%NOTFOUND返回false,反之即执行失败返回true

  9        dbms_output.put_line('没有该人,需要插入该人');

 10        INSERT INTO XS(XH,XM,ZYM,ZXF)VALUES('007',v_xm,v_zym,v_zxf);

 11      END IF;

 12  END;

 13  /

 

PL/SQL procedure successfully completed

 

SQL> SELECT * FROM XS;

 

XH     XM     ZYM    XB CSSJ        ZXF BZ

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

007    Jame   计算机                 45

061101 王林   计算机 男 1986/2/10    50

101112 李明   计算机 男 1986/1/30    36

001    张琼   计算机                 45 三好学生

121112 王小二 计算机 男 1986/1/30    36

 

 

 

 

扩展update(Returning):

SQL> SET ServerOutput ON;

SQL> DECLARE

  2      row_id ROWID;

  3      info VARCHAR2(100);

  4  BEGIN

  5        UPDATE scott.dept SET deptno=90 WHERE DNAME='RESEARCH'

  6               RETURNING rowid,dname||':'||to_char(deptno)||':'||loc

  7                        INTO row_id,info;

  8        dbms_output.put_line('ROWID:'||row_id);

  9        dbms_output.put_line(info);

 10  END;

 11  /

 

ROWID:AAAMgxAAEAAAAAQAAB

RESEARCH:90:DALLAS

 

PL/SQL procedure successfully completed

 

 

 

SQL> SET ServerOutput ON;

SQL> DECLARE

  2      --row_id ROWID;

  3      info VARCHAR2(100);

  4  BEGIN

  5        UPDATE scott.dept SET deptno=90 WHERE DNAME='RESEARCH'

  6               RETURNING dname||':'||to_char(deptno)||':'||loc

  7                        INTO info;

  8        --dbms_output.put_line('ROWID:'||row_id);

  9        dbms_output.put_line(info);

 10  END;

 11  /

 

RESEARCH:90:DALLAS

 

PL/SQL procedure successfully completed

 

 

%rowtype:

SQL> SET ServerOutput ON;

SQL> DECLARE

  2     one_emp scott.emp%rowtype;

  3  BEGIN

  4        select * into one_emp from scott.emp where empno=7900;

  5        dbms_output.put_line('该员工的职位为:'||one_emp.job);

  6        dbms_output.put_line('该员工的工资为:'||one_emp.sal);

  7  END;

  8  /

 

该员工的职位为:CLERK

该员工的工资为:950

 

PL/SQL procedure successfully completed

 

 

%type:

SQL> SET ServerOutput ON;

SQL> DECLARE

  2     emp_number constant number(4):=7900;

  3     emp_name scott.emp.ename%type;

  4     emp_job scott.emp.job%type;

  5     emp_sal scott.emp.sal%type;

  6  BEGIN

  7        select ename,job,sal into emp_name,emp_job,emp_sal from scott.emp where empno=emp_number;

  8        dbms_output.put_line('查询的员工号为:'||emp_number);

  9        dbms_output.put_line('该员工的姓名为:'||emp_name);

 10        dbms_output.put_line('该员工的职位为:'||emp_job);

 11        dbms_output.put_line('该员工的工资为:'||emp_sal);

 12  END;

 13  /

 

查询的员工号为:7900

该员工的姓名为:JAMES

该员工的职位为:CLERK

该员工的工资为:950

 

PL/SQL procedure successfully completed

 

 

SQL> show user;

User is "SYSTEM"

 

Select   ..as..:

 

SQL> select xh xuehao,xm from xs;

 

XUEHAO XM

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

Dual查询user、日期(带特定格式)、生成随机数:

 

SQL> select user from dual;

 

USER

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

SYSTEM

 

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')

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

2020-10-03 21:55:35

 

SQL> select to_char(sysdate,'mm-dd hh24:mi:ss') from dual;

 

TO_CHAR(SYSDATE,'MM-DDHH24:MI:SS')

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

10-03 21:56:06

 

 

SQL> select dbms_random.random from dual;

 

    RANDOM

----------

-496286255

 

SQL> select dbms_random.random from dual;

 

    RANDOM

----------

1745332824

 

定义(declare)(常量、变量)、赋值(:=)操作练习:

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

  2    conVersion constant VARCHAR2(20) := '1.0.0.1';

  3  BEGIN

  4    dbms_output.put_line(conVersion);

  5  END;

  6  /

1.0.0.1

 

PL/SQL procedure successfully completed

 

 

SQL>

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

  2    Database VARCHAR2(50) := 'Oracle 11g';

  3  BEGIN

  4    dbms_output.put_line(Database);

  5  END;

  6  /

Oracle 11g

 

PL/SQL procedure successfully completed

 

 

SQL>

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

  2    Database VARCHAR2(50);

  3  BEGIN

  4    Database:= 'Oracle 11g';

  5    dbms_output.put_line(Database);

  6  END;

  7  /

Oracle 11g

 

PL/SQL procedure successfully completed

 

 

IF、 ELSIF、 ENDIF练习(注意为:ELSIF):

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

  2    Num INTEGER :=- 11;

  3  BEGIN

  4    IF Num <0 THEN

  5       dbms_output.put_line('负数');

  6    ELSIF Num > 0 THEN

  7         dbms_output.put_line('正数');

  8    ELSE

  9         dbms_output.put_line('0');

 10    END IF;

 11  END;

 12  /

负数

 

PL/SQL procedure successfully completed

 

Case语句练习:

SQL>

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

  2    varDAY INTEGER := 3;

  3    Result VARCHAR2(20);

  4  BEGIN

  5    Result := CASE varDAY

  6           WHEN 1 THEN '星期一'

  7           WHEN 2 THEN '星期二'

  8           WHEN 3 THEN '星期三'

  9           WHEN 4 THEN '星期四'

 10           WHEN 5 THEN '星期五'

 11           WHEN 6 THEN '星期六'

 12           WHEN 7 THEN '星期七'

 13           ELSE '数据越界'

 14    END;

 15    dbms_output.put_line(Result);

 16  END;

 17  /

星期三

 

PL/SQL procedure successfully completed

 

循环语句(LOOP——EXIT;)练习:

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

  2    v_Num INTEGER := 1;

  3    v_Sum INTEGER := 0;

  4  BEGIN

  5    LOOP

  6      v_Sum := v_Sum + v_Num;

  7      dbms_output.put_line(v_Num);

  8      IF v_Num = 3 THEN

  9        EXIT;

 10      END IF;

 11      dbms_output.put_line('+');

 12      v_Num := v_Num+1;

 13    END LOOP;

 14    dbms_output.put_line('=');

 15    dbms_output.put_line(v_Sum);

 16  END;

 17  /

1

+

2

+

3

=

6

 

PL/SQL procedure successfully completed

 

数据库实验第六周

导入表:SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp;

SQL*Plus: Release 10.2.0.3.0 - Production on 星期五 10月 9 16:04:33 2020

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

 

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp;

 

Import: Release 10.2.0.3.0 - Production on 星期五 10月 9 16:06:14 2020

 

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

 

 

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SYSTEM 的对象导入到 SYSTEM

. 正在将 SYSTEM 的对象导入到 SYSTEM

. . 正在导入表                            "XS"导入了           4 行

. . 正在导入表                            "KC"导入了           2 行

. . 正在导入表                         "XS_KC"导入了           9 行

IMP-00017: 由于 ORACLE 错误 1502, 以下语句失败:

 "ANALYZE  TABLE "XS"  ESTIMATE STATISTICS "

IMP-00003: 遇到 ORACLE 错误 1502

ORA-01502: 索引 'SYSTEM.XM_IDX' 或这类索引的分区处于不可用状态

成功终止导入, 但出现警告。

 

SQL>

黑屏下查询归档文件信息:SQL> conn /as sysdba; SQL> ArCHIVE Log list;

SQL> ArCHIVE Log list;

ORA-01031: 权限不足

SQL> conn /as sysdba;

已连接。

SQL> archive log list;

数据库日志模式             非存档模式

自动存档             禁用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     8

当前日志序列           10

查询表信息:SQL> select * from cat;

 

TABLE_NAME                     TABLE_TYPE

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

SYSCATALOG                     SYNONYM

CATALOG                        SYNONYM

TAB                            SYNONYM

COL                            SYNONYM

TABQUOTAS                      SYNONYM

SYSFILES                       SYNONYM

PUBLICSYN                      SYNONYM

MVIEW$_ADV_WORKLOAD            TABLE

MVIEW$_ADV_BASETABLE           TABLE

MVIEW$_ADV_SQLDEPEND           TABLE

MVIEW$_ADV_PRETTY              TABLE

MVIEW$_ADV_TEMP                TABLE

MVIEW$_ADV_FILTER              TABLE

MVIEW$_ADV_LOG                 TABLE

MVIEW$_ADV_FILTERINSTANCE      TABLE

MVIEW$_ADV_LEVEL               TABLE

MVIEW$_ADV_ROLLUP              TABLE

MVIEW$_ADV_AJG                 TABLE

MVIEW$_ADV_FJG                 TABLE

MVIEW$_ADV_GC                  TABLE

 

TABLE_NAME                     TABLE_TYPE

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

MVIEW$_ADV_CLIQUE              TABLE

MVIEW$_ADV_ELIGIBLE            TABLE

MVIEW$_ADV_OUTPUT              TABLE

MVIEW$_ADV_EXCEPTIONS          TABLE

MVIEW$_ADV_PARAMETERS          TABLE

MVIEW$_ADV_INFO                TABLE

MVIEW$_ADV_JOURNAL             TABLE

MVIEW$_ADV_PLAN                TABLE

MVIEW$_ADVSEQ_GENERIC          SEQUENCE

MVIEW$_ADVSEQ_ID               SEQUENCE

MVIEW_WORKLOAD                 VIEW

MVIEW_FILTER                   VIEW

MVIEW_LOG                      VIEW

MVIEW_FILTERINSTANCE           VIEW

MVIEW_RECOMMENDATIONS          VIEW

MVIEW_EVALUATIONS              VIEW

MVIEW_EXCEPTIONS               VIEW

AQ$_QUEUE_TABLES               TABLE

AQ$_QUEUES                     TABLE

AQ$_SCHEDULES                  TABLE

AQ$_INTERNET_AGENTS            TABLE

 

TABLE_NAME                     TABLE_TYPE

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

AQ$_INTERNET_AGENT_PRIVS       TABLE

DEF$_AQCALL                    TABLE

DEF$_AQERROR                   TABLE

AQ$_DEF$_AQCALL_F              VIEW

AQ$DEF$_AQCALL                 VIEW

AQ$_DEF$_AQERROR_F             VIEW

AQ$DEF$_AQERROR                VIEW

DEF$_ERROR                     TABLE

DEF$_DESTINATION               TABLE

DEF$_CALLDEST                  TABLE

DEF$_DEFAULTDEST               TABLE

DEF$_LOB                       TABLE

DEF$_TEMP$LOB                  TABLE

DEF$_PROPAGATOR                TABLE

DEF$_ORIGIN                    TABLE

DEF$_PUSHED_TRANSACTIONS       TABLE

OL$                            TABLE

OL$HINTS                       TABLE

OL$NODES                       TABLE

LOGMNR_SESSION_EVOLVE$         TABLE

LOGMNR_EVOLVE_SEQ$             SEQUENCE

 

TABLE_NAME                     TABLE_TYPE

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

LOGMNR_SEQ$                    SEQUENCE

LOGMNR_HEADER1$                TABLE

LOGMNR_HEADER2$                TABLE

LOGMNR_UIDS$                   SEQUENCE

LOGMNR_UID$                    TABLE

LOGMNRC_DBNAME_UID_MAP         TABLE

LOGMNR_DICTSTATE$              TABLE

LOGMNR_DICTIONARY$             TABLE

LOGMNR_OBJ$                    TABLE

LOGMNR_USER$                   TABLE

LOGMNRC_GTLO                   TABLE

LOGMNRC_GTCS                   TABLE

LOGMNRC_GSII                   TABLE

LOGMNR_TAB$                    TABLE

LOGMNR_COL$                    TABLE

LOGMNR_ATTRCOL$                TABLE

LOGMNR_TS$                     TABLE

LOGMNR_IND$                    TABLE

LOGMNR_TABPART$                TABLE

LOGMNR_TABSUBPART$             TABLE

LOGMNR_TABCOMPART$             TABLE

 

TABLE_NAME                     TABLE_TYPE

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

LOGMNR_TYPE$                   TABLE

LOGMNR_COLTYPE$                TABLE

LOGMNR_ATTRIBUTE$              TABLE

LOGMNR_LOB$                    TABLE

LOGMNR_CDEF$                   TABLE

LOGMNR_CCOL$                   TABLE

LOGMNR_ICOL$                   TABLE

LOGMNR_LOBFRAG$                TABLE

LOGMNR_INDPART$                TABLE

LOGMNR_INDSUBPART$             TABLE

LOGMNR_INDCOMPART$             TABLE

LOGMNRP_CTAS_PART_MAP          TABLE

LOGMNRT_MDDL$                  TABLE

LOGMNR_LOG$                    TABLE

LOGMNR_PROCESSED_LOG$          TABLE

LOGMNR_SPILL$                  TABLE

LOGMNR_AGE_SPILL$              TABLE

LOGMNR_RESTART_CKPT_TXINFO$    TABLE

LOGMNR_ERROR$                  TABLE

LOGMNR_RESTART_CKPT$           TABLE

LOGMNR_FILTER$                 TABLE

 

TABLE_NAME                     TABLE_TYPE

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

LOGMNR_PARAMETER$              TABLE

LOGMNR_SESSION$                TABLE

LOGSTDBY$PARAMETERS            TABLE

LOGSTDBY$EVENTS                TABLE

LOGSTDBY$APPLY_PROGRESS        TABLE

LOGSTDBY$APPLY_MILESTONE       TABLE

LOGSTDBY$SCN                   TABLE

LOGSTDBY$PLSQL                 TABLE

LOGSTDBY$SKIP_TRANSACTION      TABLE

LOGSTDBY$SKIP                  TABLE

LOGSTDBY$SKIP_SUPPORT          TABLE

LOGSTDBY$HISTORY               TABLE

REPCAT$_REPCAT                 TABLE

REPCAT$_FLAVORS                TABLE

REPCAT$_FLAVORS_S              SEQUENCE

REPCAT$_FLAVOR_NAME_S          SEQUENCE

REPCAT$_REPSCHEMA              TABLE

REPCAT$_SNAPGROUP              TABLE

REPCAT$_REPOBJECT              TABLE

REPCAT$_REPCOLUMN              TABLE

REPCAT$_KEY_COLUMNS            TABLE

 

TABLE_NAME                     TABLE_TYPE

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

REPCAT$_GENERATED              TABLE

REPCAT$_REPPROP                TABLE

REPCAT$_REPPROP_KEY            SEQUENCE

REPCAT$_REPCATLOG              TABLE

REPCAT$_DDL                    TABLE

REPCAT$_REPGROUP_PRIVS         TABLE

REPCAT_LOG_SEQUENCE            SEQUENCE

REPCAT$_PRIORITY_GROUP         TABLE

REPCAT$_PRIORITY               TABLE

REPCAT$_COLUMN_GROUP           TABLE

REPCAT$_GROUPED_COLUMN         TABLE

REPCAT$_CONFLICT               TABLE

REPCAT$_RESOLUTION_METHOD      TABLE

REPCAT$_RESOLUTION             TABLE

REPCAT$_RESOLUTION_STATISTICS  TABLE

REPCAT$_RESOL_STATS_CONTROL    TABLE

REPCAT$_PARAMETER_COLUMN       TABLE

REPCAT$_AUDIT_ATTRIBUTE        TABLE

REPCAT$_AUDIT_COLUMN           TABLE

REPCAT$_FLAVOR_OBJECTS         TABLE

REPCAT$_TEMPLATE_STATUS        TABLE

 

TABLE_NAME                     TABLE_TYPE

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

REPCAT$_TEMPLATE_TYPES         TABLE

REPCAT$_REFRESH_TEMPLATES      TABLE

REPCAT$_REFRESH_TEMPLATES_S    SEQUENCE

REPCAT$_USER_AUTHORIZATIONS    TABLE

REPCAT$_USER_AUTHORIZATIONS_S  SEQUENCE

REPCAT$_OBJECT_TYPES           TABLE

REPCAT$_TEMPLATE_REFGROUPS     TABLE

REPCAT$_TEMPLATE_REFGROUPS_S   SEQUENCE

REPCAT$_TEMPLATE_OBJECTS       TABLE

REPCAT$_TEMPLATE_OBJECTS_S     SEQUENCE

REPCAT$_TEMPLATE_PARMS         TABLE

REPCAT$_TEMPLATE_PARMS_S       SEQUENCE

REPCAT$_OBJECT_PARMS           TABLE

REPCAT$_USER_PARM_VALUES       TABLE

REPCAT$_USER_PARM_VALUES_S     SEQUENCE

REPCAT$_TEMPLATE_SITES         TABLE

REPCAT$_TEMPLATE_SITES_S       SEQUENCE

REPCAT$_SITE_OBJECTS           TABLE

REPCAT$_TEMP_OUTPUT_S          SEQUENCE

REPCAT$_RUNTIME_PARMS          TABLE

REPCAT$_RUNTIME_PARMS_S        SEQUENCE

 

TABLE_NAME                     TABLE_TYPE

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

REPCAT$_TEMPLATE_TARGETS       TABLE

TEMPLATE$_TARGETS_S            SEQUENCE

REPCAT$_EXCEPTIONS             TABLE

REPCAT$_EXCEPTIONS_S           SEQUENCE

REPCAT$_INSTANTIATION_DDL      TABLE

REPCAT$_EXTENSION              TABLE

REPCAT$_SITES_NEW              TABLE

SQLPLUS_PRODUCT_PROFILE        TABLE

PRODUCT_PRIVS                  VIEW

PRODUCT_USER_PROFILE           SYNONYM

HELP                           TABLE

XS                             TABLE

KC                             TABLE

XS_KC                          TABLE

MVIEW$_ADV_INDEX               TABLE

MVIEW$_ADV_PARTITION           TABLE

MVIEW$_ADV_OWB                 TABLE

 

184 rows selected

 

查询用户信息:SQL> select * from dba_users;

USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE   EXPIRY_DATE DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED     PROFILE                        INITIAL_RSRC_CONSUMER_GROUP    EXTERNAL_NAME

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

MGMT_VIEW                              53 181DA128BC4A91E7               OPEN                                                     SYSTEM                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

SYS                                     0 D3CE9AB10E42F19D               OPEN                                                     SYSTEM                         TEMP                           2007/4/17 0 DEFAULT                        SYS_GROUP                      

SYSTEM                                  5 9788807910D58ED9               OPEN                                                     SYSTEM                         TEMP                           2007/4/17 0 DEFAULT                        SYS_GROUP                      

DBSNMP                                 24 0344EFE44BA8E12C               OPEN                                                     SYSAUX                         TEMP                           2007/4/17 0 MONITORING_PROFILE             DEFAULT_CONSUMER_GROUP         

SYSMAN                                 51 BCDEB078A5E81AC2               OPEN                                                     SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

SCOTT                                  54 F894844C34402B67               EXPIRED                                      2020/9/6 11 USERS                          TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

OUTLN                                  11 4A3BA55E08595C81               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSTEM                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

MDSYS                                  46 72979A94BAD2AF80               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

ORDSYS                                 43 7EFA02EC7EA6B86F               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

EXFSYS                                 34 66F4EF5650C20355               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

DMSYS                                  35 BFBA5A553FD9E28A               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

WMSYS                                  25 7C9BA362F8314299               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

CTXSYS                                 36 71E687F036AD56E5               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

ANONYMOUS                              39 anonymous                      EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

XDB                                    38 88D8364765FCE6AF               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

ORDPLUGINS                             44 88A2B2C183431F00               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

SI_INFORMTN_SCHEMA                     45 84B8CBCA4D477FA3               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

OLAPSYS                                47 3FB8EF9DB538647C               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 SYSAUX                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

TSMSYS                                 21 3DF26A8B17D0F29F               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

BI                                     60 FA1D2B85B70213F3               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2020/9/6 11 DEFAULT                        DEFAULT_CONSUMER_GROUP         

 

USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE   EXPIRY_DATE DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED     PROFILE                        INITIAL_RSRC_CONSUMER_GROUP    EXTERNAL_NAME

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

PM                                     59 72E382A52E89575A               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2020/9/6 11 DEFAULT                        DEFAULT_CONSUMER_GROUP         

MDDATA                                 50 DF02A496267DEE66               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

IX                                     57 2BE6F80744E08FEB               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2020/9/6 11 DEFAULT                        DEFAULT_CONSUMER_GROUP         

SH                                     58 9793B3777CD3BD1A               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2020/9/6 11 DEFAULT                        DEFAULT_CONSUMER_GROUP         

DIP                                    19 CE4A36B8E06CA59C               EXPIRED & LOCKED                 2020/9/6 11             USERS                          TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

OE                                     56 9C30855E7E0CB02D               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2020/9/6 11 DEFAULT                        DEFAULT_CONSUMER_GROUP         

HR                                     55 6399F3B38EDF3288               EXPIRED & LOCKED                 2020/9/6 11 2020/9/6 11 USERS                          TEMP                           2020/9/6 11 DEFAULT                        DEFAULT_CONSUMER_GROUP         

 

27 rows selected

查询数据文件信息dba_data_files:select * from dba_data_files;

SQL> select * from dba_data_files;

 

FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS

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

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                         4 USERS                             5242880        640 AVAILABLE            4 YES            3435972198    4194302          160    5177344         632 ONLINE

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                                        3 SYSAUX                          262144000      32000 AVAILABLE            3 YES            3435972198    4194302         1280  262078464       31992 ONLINE

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                                       2 UNDOTBS1                         73400320       8960 AVAILABLE            2 YES            3435972198    4194302          640   73334784        8952 ONLINE

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                        1 SYSTEM                          503316480      61440 AVAILABLE            1 YES            3435972198    4194302         1280  503250944       61432 SYSTEM

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF                                       5 EXAMPLE                         104857600      12800 AVAILABLE            5 YES            3435972198    4194302           80  104792064       12792 ONLINE

 

select * from v$datafile;

SQL> select * from v$datafile;

 

     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME   OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             PLUGGED_IN BLOCK1_OFFSET AUX_NAME                                                                         FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME

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

         1               11 2007/4/17 03:          0          1 SYSTEM  READ WRITE             730077 2020/10/9 15:56                     0                                                      521802         521803 2020/9/6 11  503316480      61440            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                        0          8192 NONE                                                                                               0

         2           519918 2007/4/17 06:          1          2 ONLINE  READ WRITE             730077 2020/10/9 15:56                     0                                                      521802         521803 2020/9/6 11   73400320       8960            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                                       0          8192 NONE                                                                                               0

         3             5554 2007/4/17 03:          2          3 ONLINE  READ WRITE             730077 2020/10/9 15:56                     0                                                      521802         521803 2020/9/6 11  262144000      32000            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                                        0          8192 NONE                                                                                               0

         4             9202 2007/4/17 03:          4          4 ONLINE  READ WRITE             730077 2020/10/9 15:56                     0                                                      521802         521803 2020/9/6 11    5242880        640            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                         0          8192 NONE                                                                                               0

         5           546520 2020/9/6 11:0          6          5 ONLINE  READ WRITE             730077 2020/10/9 15:56                     0                                                           0              0              104857600      12800    104857600       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF                                       0          8192 NONE                                                                                               0

查询归档文件信息:select name,log_mode from v$database;

SQL> select name,log_mode from v$database;

 

NAME      LOG_MODE

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

ORCL      NOARCHIVELOG

查询控制文件信息:select * from v$controlfile;

SQL> select * from v$controlfile;

 

STATUS  NAME                                                                             IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS

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

        C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL                              NO                         16384            430

        C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL                              NO                         16384            430

        C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL                              NO                         16384            430

 

查询日志文件信息:select * from v$log;

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME

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

         1          1          8   52428800          1 NO       INACTIVE                661898 2020/9/11 0

         2          1          9   52428800          1 NO       INACTIVE                695903 2020/9/11 1

         3          1         10   52428800          1 NO       CURRENT                 730076 2020/10/9 1

 

select * from v$logfile;

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

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

         3         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO

         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO

         1 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO

按组添加日志文件LOGFILE并作出相关查询:ALTER DATABASE ADD LOGFILE GROUP 4('log1a.rdo','log2a.rdo')size 5000k;

SQL> ALTER DATABASE ADD LOGFILE GROUP 4('log1a.rdo','log2a.rdo')size 5000k;

 

Database altered

 

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

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

         3         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO

         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO

         1 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO

         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1A.RDO                                 NO

         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2A.RDO                                 NO

按成员MEMBER添加日志文件LOGFILE到组GROUP并作出相关查询:ALTER DATABASE ADD LOGFILE MEMBER 'log3a.log' TO Group 4;

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'log3a.log' TO Group 4;

 

Database altered

 

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

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

         3         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO

         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO

         1 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO

         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1A.RDO                                 NO

         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2A.RDO                                 NO

         4 INVALID ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG3A.LOG                                 NO

 

6 rows selected

按成员删除日志文件:ALTER DATABASE DROP LOGFILE MEMBER 'log3a.log';

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'log3a.log';

 

Database altered

 

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

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

         3         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO

         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO

         1 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO

         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1A.RDO                                 NO

         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2A.RDO                                 NO

按组删除日志文件:ALTER DATABASE DROP LOGFILE  Group 4;

SQL> ALTER DATABASE DROP LOGFILE  Group 4;

 

Database altered

 

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

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

         3         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO

         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO

         1 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO

 

创建表空间:

SQL> CREATE TABLESPACE ts1

  2  datafile 'c:ts1.dbf' size 5000K, 'c:ts2.dbf' size 1M;

 

Tablespace created