Skip to content

Myth-coder-dot/oracle-1

BranchesTags
 
 

Repository files navigation

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命令环境下载

在Windows上登录需要ssh客户端，可以下载安装git Bash: [https://github.com/zwdcdu/oracle/raw/master/tools/gitgfb_ttrar.rar]

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;

About

oracle

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages

  • JavaScript 51.5%
  • Java 18.7%
  • CSS 13.7%
  • HTML 10.2%
  • PLSQL 5.9%