oracle数据库caozw,GitHub - zwdcdu/oracle: oracle

Oracle 数据库应用

内容说明

PPT目录：各章节的PPT

script目录：各章节的SQL语句及源码

doc目录：参考文档

book.pdf： 本书的PDF文档

tools/gitgfb_ttar.rar -windows中运行git命令工具

实验服务器地址

地址：202.115.82.8

数据库：pdborcl

system密码:123，所有密码都为123

成绩网址：http://202.115.82.8:1522

git命令环境下载

SSH登录及连接Oracle测试

linux中的用户student密码是123

$ ssh student@202.115.82.8

student@202.115.82.8's password:

[student@deep02 ~]$cat readme.txt

登录linux后连接Oracle，查询表hr.employees。

[student@deep02 ~]$ sqlplus system/123@202.115.82.8/pdborcl

sqlplus 你的用户名/123@pdborcl

SQL> select * from hr.employees;

sys连接

[student@deep02 ~]$ sqlplus sys/123@202.115.82.8/orcl as sysdba

hr用户连接

[student@deep02 ~]$ sqlplus hr/123@202.115.82.8/pdborcl

工具文件下载

在Windows端运行git bash后，下载sqldeveloper和Oracle12c安装文件

scp student@202.115.82.8:~/tools/sqldeveloper.zip .

scp student@202.115.82.8:~/tools/sqldeveloper7.zip .

scp student@202.115.82.8:~/tools/linuxx64_12201_database.zip .

网址

Git命令参考

create a new repository on the command line

echo "# oracle" >> README.md

git init

git add README.md

git commit -m "first commit"

git remote add origin https://github.com/zwdcdu/oracle.git

git push -u origin master

push an existing repository from the command line

git remote add origin https://github.com/zwdcdu/oracle.git

git push -u origin master

添加student用户

useradd student -G dba

usermod -a -G oinstall student

usermod -a -G dba student

$rman target sys/123@202.115.82.8/orcl

RMAN> list backup;

RMAN>

run {

shutdown immediate;

startup mount;

backup database format='/home/oracle/rman_bak/%d_%s.bak';

alter database open;

}

run {

shutdown immediate;

startup mount;

restore database;

recover database;

alter database open resetlogs;

}

SQL> select name from v$controlfile;

/home/oracle/app/oracle/oradata/orcl/control01.ctl

/home/oracle/app/oracle/oradata/orcl/control02.ctl

SQL> select member from v$logfile;

/home/oracle/app/oracle/oradata/orcl/redo03.log

/home/oracle/app/oracle/oradata/orcl/redo02.log

/home/oracle/app/oracle/oradata/orcl/redo01.log

SQL> select file_name from v$datafile;

SQL> select file_name from dba_data_files;

/home/oracle/app/oracle/oradata/orcl/system01.dbf

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

/home/oracle/app/oracle/oradata/orcl/users01.dbf

SQL> show parameter pfile

/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/spfileorcl.orav

在客户端配置TNS

新建Windows环境变量：TNS_ADMIN=D:\sqldeveloper\network\admin

新建文件：D:\sqldeveloper\network\admin\tnsnames.ora,文件内容如下：

PDBORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 202.115.82.8)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdborcl)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 202.115.82.8)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

在sqldeveloper中查询oracle连接进程，终止部分进程

sys用户

菜单：工具->监视会话

通过UI界面菜单，或者sql语句完成相应操作

启用共享连接

sys登录

sqlplus sys/123@localhost/orcl as sysdba

ALTER SYSTEM SET dispatchers="(PROTOCOL=TCP)(dispatchers=3)"

ALTER SYSTEM SET max_dispatchers=5

ALTER SYSTEM SET shared_servers = 1

ALTER SYSTEM SET max_shared_servers=20

ALTER SYSTEM SET shared_server_sessions=200

show parameter shared_server

hr用户

共享模式登录测试：

sqlplus hr/123@localhost/pdborcl:shared

ps -ef | grep ora_d[0-9].*[_orcl$]

专用模式登录测试：

sqlplus hr/123@localhost/pdborcl

ps -ef | grep oracleorcl

查看监听状态：

lsnrctl service

lsnrctl status

收集表的统计信息

system登录到pdborcl

CREATE TABLE hr.emp_test as SELECT * FROM hr.employees;

INSERT INTO hr.emp_test SELECT * FROM hr.employees;

INSERT INTO hr.emp_test SELECT * FROM hr.employees;

INSERT INTO hr.emp_test SELECT * FROM hr.employees;

select count(*) from hr.emp_test;

--统计前

explain plan for SELECT * FROM hr.emp_test WHERE employee_id=110;

SELECT * FROM TABLE(dbms_xplan.display);

--rows = 1，这是错误的基数

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP_TEST | 1 | 69 | 3 (0)| 00:00:01 |

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

--统计后，让数据库感知表hr.emp_test记录数量的变化

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','EMP_TEST');

explain plan for SELECT * FROM hr.emp_test WHERE employee_id=110;

SELECT * FROM TABLE(dbms_xplan.display);

--rows = 4 这是正确的基数，有利于构建正确的计划

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 4 | 276 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP_TEST | 4 | 276 | 6 (0)| 00:00:01 |

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

--实验完成后：

drop table hr.emp_test;

从pdborcl创建可插接数据库

新数据库存入：/home/student/pdb/新数据库名称

$ sqlplus / as sysdba

替代命令：

$ sqlplus sys/123@202.115.82.8/orcl as sysdba

查看创建前的数据库：

show pdbs

--1.关闭pdborcl

ALTER PLUGGABLE DATABASE pdborcl CLOSE immediate;

--或者先切换到pdborcl：

alter session set container=pdborcl

shutdown immediate

--切换数据库，回到CDB

alter session set container=cdb$root

--2.只读方式打开pdborcl

ALTER PLUGGABLE DATABASE pdborcl OPEN READ ONLY;

--3.创建数据库clonedb

CREATE PLUGGABLE DATABASE clonedb FROM pdborcl file_name_convert=('/home/oracle/app/oracle/oradata/orcl/pdborcl'，'/home/student/pdb/clonedb');

--4.打开新数据库

ALTER PLUGGABLE DATABASE clonedb OPEN;

--查看新数据库

show pdbs;

--5.创建成功后，重新打开pdborcl为读写状态

ALTER PLUGGABLE DATABASE pdborcl CLOSE immediate;

ALTER PLUGGABLE DATABASE pdborcl OPEN;

--6.测试

--创建成功后,退出sys用户，以hr登录到新数据库,测试一下

$ sqlplus hr/123@202.115.82.8/clonedb

--查看数据库相关文件

$ ls /home/student/pdb/clonedb

--7.删除新数据库(可选)

--重新sys登录,删除新增的数据库

DROP PLUGGABLE DATABASE clonedb INCLUDING DATAFILES;

从pdborcl创建可插接数据库,简单流程

以yourdb为源数据库，yourdb已经打开为read only

将zhang改为自己的数据库名称

$ sqlplus sys/123@202.115.82.8/orcl as sysdba

CREATE PLUGGABLE DATABASE zhang1 FROM yourdb file_name_convert=('/home/student/pdb/yourdb'，'/home/student/pdb/ zhang1 ');

--4.打开新数据库

ALTER PLUGGABLE DATABASE zhang OPEN;

--查看新数据库

show pdbs;

--6.测试

--创建成功后,退出sys用户，以hr登录到新数据库,测试一下

$ sqlplus hr/123@202.115.82.8/ zhang

--查看数据库相关文件

$ ls /home/student/pdb/ zhang

--7 删除pdb

ALTER PLUGGABLE DATABASE zhang close;

Drop pluggable database zhang including datafiles;

表空间命令

--查看表空间大小：

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

--查看表空间已使用大小：

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

--查看表空间所在的文件地址

select * from dba_data_files

--查看表空间下的表

select table_name, tablespace_name,OWNER from dba_tables where tablespace_name = 'USERS';

--查看表的大小

select segment_name,segment_type,sum(bytes/1024/1024) from dba_segments

where segment_type='TABLE'

and segment_name = 'JOBS'

group by segment_name, segment_type;

In_Memory

--创建表sales，插入100万条记录

CREATE TABLE sales

(ID NUMBER primary key,

NAME VARCHAR2(50 BYTE) not null,

QUANTITY NUMBER(8,0),

PRICE NUMBER(8,0)

);

--插入100万行数据

declare

v1 number;

v2 number;

begin

delete from sales;

for i in 1..1000000

loop

v1:=dbms_random.value(1,90);

v2:=dbms_random.value(100,900);

insert into sales(id,name,quantity,price) values (i,'name'||i,v1,v2);

end loop;

commit;

end;

插入过程中，如果遇到超出表空间 'USERS' 的空间限额，可以执行：

alter user 你的用户名 quota unlimited on users;

--进行IN-Memory前后的查询对比

--in-memory前：

--两次执行:

set autotrace on TRACEONLY

select sum(quantity*price) total from sales;

--in-memory：

set autotrace on

alter table sales inmemory;

--in-memory后：

--两次执行:

select sum(quantity*price) total from sales;

观察consistent gets的数量，越少越快。

--查询总金额

无法启动的错误：ORA-01157: 无法标识/锁定数据文件 414 的解决办法

startup mount

alter database datafile 414 offline drop;

recover database;

alter database open;