oracle数据库实验报告一

电脑上安装的oracle版本为12c企业版和第三方工具PL/SQL Develop，12c似乎没有支持scott用户（但是安装目录下面确实存在scott.sql，有些疑惑），执行conn scott/tiger验证失败，经验证并非是加锁原因（alert user scott account unlock;），而是不存在该用户，管理员查询scott.emp，也查询不到表，执行@H:Oracleoracleproduct12.2.0dbhome_1 dbmsadminscott.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

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:Oracleoracleproduct12.2.0dbhome_1 dbmsadminscott.sql下的sql语句放到SQLDevelop下执行一遍，成功获取数据：

实验阶段（SQL题目练习）：

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

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

分析：上述早于即小于（时间）

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

这里以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

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

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

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

列出薪金高于在部门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

-----

SQL> SELECT 1+1 FROM DUAL;

1+1

----------

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

----------

SQL> SELECT &a1+1 FROM DUAL;

2+1

----------

SQL> SELECT &&a1+1 FROM DUAL;

5+1

----------

SQL> SELECT &a1+1 FROM DUAL;

5+1

----------

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

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 /

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

连接到:

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

连接到: 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

. . 正在导入表 "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:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE

C:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF 3 SYSAUX 262144000 32000 AVAILABLE 3 YES 3435972198 4194302 1280 262078464 31992 ONLINE

C:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF 2 UNDOTBS1 73400320 8960 AVAILABLE 2 YES 3435972198 4194302 640 73334784 8952 ONLINE

C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM

C:ORACLEPRODUCT10.2.0ORADATAORCLEXAMPLE01.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:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.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:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.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:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.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:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.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:ORACLEPRODUCT10.2.0ORADATAORCLEXAMPLE01.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:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL01.CTL NO 16384 430

C:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL02.CTL NO 16384 430

C:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL03.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:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG NO

2 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG NO

1 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.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:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG NO

2 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG NO

1 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG NO

4 ONLINE C:ORACLEPRODUCT10.2.0DB_1DATABASELOG1A.RDO NO

4 ONLINE C:ORACLEPRODUCT10.2.0DB_1DATABASELOG2A.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:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG NO

2 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG NO

1 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG NO

4 ONLINE C:ORACLEPRODUCT10.2.0DB_1DATABASELOG1A.RDO NO

4 ONLINE C:ORACLEPRODUCT10.2.0DB_1DATABASELOG2A.RDO NO

4 INVALID ONLINE C:ORACLEPRODUCT10.2.0DB_1DATABASELOG3A.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:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG NO

2 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG NO

1 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG NO

4 ONLINE C:ORACLEPRODUCT10.2.0DB_1DATABASELOG1A.RDO NO

4 ONLINE C:ORACLEPRODUCT10.2.0DB_1DATABASELOG2A.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:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG NO

2 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG NO

1 STALE ONLINE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG NO

创建表空间：

SQL> CREATE TABLESPACE ts1

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

Tablespace created

作者：study-hard-forever

出处：https://www.cnblogs.com/study-hard-forever/

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文链接，否则保留追究法律责任的权利。