作业
作业（sql plus ）
-- 在SQL Plus执行一个SQL语句，然后进行语句编辑、保存脚本、执行脚本、并把显示结果保存下来 with fib(p,n,x) as (select 0,1,0 from dual union all select n,p+n,x+1 from fib where x<10) select max(p) from fib; c/max(p)/p; r sav fib.sql; spo ...txt; @fib.sql; spo off;
作业活动（数据库启动关闭）
-- A、启动Oracle数据库，分布启动的三步命令分别是1、 2、 3. -- B、急需关闭数据库，但需等待已连接的用户提交完事务，用户可不断开会话再关闭，需用什么命令关闭。 -- C、急需关闭数据库，所有已连接的用户中止会话再关闭，需用什么命令关闭。 startup nomount alter database mount alter database open shutdown transactional shutdown immediate
作业(表空间)
-- 1、查看表空间信息。 -- 2、创建某系统项目的表空间 姓名_tbs(例：张三_tbs),这个表空间初始数据文件初始大小为5MB，设为自动扩展，增长量为每次1MB，最大100M。 -- 3、修改题2所建的数据文件，使其自动扩展增长量为每次10MB，最大300M -- 4、在2题所建的表空间基础上增加一个数据文件zhangsan_tbs02.dbf,数据文件初始大小为10MB，设为自动扩展，增长量为每次5MB，最大500M. -- 5、假设表空间中有数据，需要删除表空间及其数据文件。 select * from dba_tablespaces; create tablespace *_tbs datafile 'tw_tbs_01.dbf' size 5m autoextend on next 1m maxsize 100m; alter database datafile '*_tbs_01.dbf' autoextend on next 10m maxsize 300m; alter tablespace *_tbs add datafile 'zhangsan_tbs_02.dbf' size 10m autoextend on next 5m maxsize 500m; drop tablespace *_tbs including contents and datafiles;
物理结构
-- 【1】查看Oracle 12c数据库安装后的目录结构，确定控制文件、重做日志文件和数据文件的存储位置。 -- 【2】在SQL*Plus中，查询数据文件的名称和存放路径，以及该数据文件的标识和大小。 -- 【3】在SQL*Plus中，查询当前使用的日志文件组的编号、大小、日志成员数和状态。 -- 【4】在SQL*Plus中，查询控制文件的名称及存储路径。 select file_name from dba_data_files; select name,bytes,status from v$datafile; 3elect group#,bytes,members,status from v$log; select name from v$controlfile;
作业（用户管理）
-- 1、创建立一个test1用户，密码为test1。默认表空间为system,在该表空间的配额为15MB。使用新创建的用户test1登录数据库，如果不能立即登录，出现错误提示信息，请给出理由。 -- 2、创建立一个test2用户，密码为test2。默认表空间为users,在该表空间的配额为25MB,临时表空间为temp.该用户的口令初始状态为过期，账户初始状态设置为锁定。 -- 3、修改test2用户，将密码改为tiger，默认表空间改为system，账户的状态设置为解锁状态。 -- 4、删除test2用户。 -- 5、查询数据库中所有用户名、默认表空间和临时表空间。 create user test1 identified by test1 default tablespace system quota 15m on system; conn test1; conn /as sysdba; grant create session to test1; conn test1; conn /as sysdba; create user test2 identified by test2 default tablespace users temporary tablespace temp quota 15m on users password expire account lock; alter user test2 identified by tiger default tablespace system account unlock; drop user test2; select username,default_tablespace,temporary_tablespace from dba_users;
作业（概要文件）
-- 1、创建一个名为res_profile的概要文件，要求每个用户最多可以创建4个并发会话；每个会话持续时间最长为60分钟；如果会话在连续20分钟内空闲，则结束会话；每个会话的私有SQL区为100 KB；每个SQL语句占用CPU时间总量不超过1秒。 -- 2、创建一个名为pwd_profile的概要文件，如果用户连续4次登录失败，则锁定该账户，10天后该账户自动解锁。 create profile res_profile limit sessions_per_user 4 connect_time 60 idle_time 20 private_sga 100k cpu_per_call 100; create profile pwd_profile limit failed_login_attempts 4 password_lock_time 10;
物理备份
-- 1. 进行Oracle备份策略中最简单的非归档模式下的冷备份，将名为orcl的数据库备份在d:\Orcl\cold\目录下。 -- 2. 进行Oracle备份策略中较为复杂的归档模式下的联机热备份。将名为orcl的数据库备份在d:\Orcl\hot\目录下。 shutdown immediate; startup; archive log list; shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list; select file_name,tablespace_name from dba_data_files; alter tablespace system begin backup; host copy D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF D:\Orcl\hot\; alter tablespace system end backup; alter tablespace users begin backup; host copy D:\ORACLE\ORADATA\ORCL\USERS01.DBF D:\Orcl\hot\; alter tablespace users end backup; alter tablespace undotbs1 begin backup; host copy D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF D:\Orcl\hot\; alter tablespace undotbs1 end backup; alter tablespace sysaux begin backup; host copy D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF D:\Orcl\hot\; alter tablespace sysaux end backup; alter system archive log current; alter database backup controlfile to 'D:\Orcl\hot\control1.ctl';
归档模式的联机物理恢复
-- 自己动手模仿例7进行归档模式的联机物理恢复测试。 shutdown immediate; startup mount; alter database archivelog; alter system set log_archive_dest_10='location=d:/'; alter database open; archive log list; create user test identified by test default tablespace users temporary tablespace temp; grant connect,resource to test; conn test/test; create table test(x number); insert into test values(1); commit; conn /as sysdba; alter system archive log current; shutdown immediate; host copy D:\Oracle\oradata\orcl\USERS01.DBF D:\; host del D:\Oracle\oradata\orcl\USERS01.DBF; startup; host copy D:\USERS01.DBF D:\Oracle\oradata\orcl\; recover database; alter database datafile 'D:\Oracle\oradata\orcl\USERS01.DBF' online; alter database open; conn test/test; select * from test;
9`j(b9`j(h/M+sih/HxHWh/CO+juGA_BQLk~=4M=h.java
package fxx;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
/**
* Servlet implementation class UploadServlet
*/
@WebServlet("/UploadServlet")
public class UploadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
// 上传文件存储目录
private static final String UPLOAD_DIRECTORY = "upload";
// 上传配置
private static final int MEMORY_THRESHOLD = 1024 * 1024 * 3; // 3MB
private static final int MAX_FILE_SIZE = 1024 * 1024 * 40; // 40MB
private static final int MAX_REQUEST_SIZE = 1024 * 1024 * 50; // 50MB
/**
* 上传数据及保存文件
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// 检测是否为多媒体上传
if (!ServletFileUpload.isMultipartContent(request)) {
// 如果不是则停止
PrintWriter writer = response.getWriter();
writer.println("Error: 表单必须包含 enctype=multipart/form-data");
writer.flush();
return;
}
// 配置上传参数
DiskFileItemFactory factory = new DiskFileItemFactory();
// 设置内存临界值 - 超过后将产生临时文件并存储于临时目录中
factory.setSizeThreshold(MEMORY_THRESHOLD);
// 设置临时存储目录
factory.setRepository(new File(System.getProperty("java.io.tmpdir")));
ServletFileUpload upload = new ServletFileUpload(factory);
// 设置最大文件上传值
upload.setFileSizeMax(MAX_FILE_SIZE);
// 设置最大请求值 (包含文件和表单数据)
upload.setSizeMax(MAX_REQUEST_SIZE);
// 中文处理
upload.setHeaderEncoding("UTF-8");
// 构造临时路径来存储上传的文件
// 这个路径相对当前应用的目录
String uploadPath = getServletContext().getRealPath("/") + File.separator + UPLOAD_DIRECTORY;
// 如果目录不存在则创建
File uploadDir = new File(uploadPath);
if (!uploadDir.exists()) {
uploadDir.mkdir();
}
try {
// 解析请求的内容提取文件数据
@SuppressWarnings("unchecked")
List<FileItem> formItems = upload.parseRequest(request);
if (formItems != null && formItems.size() > 0) {
// 迭代表单数据
for (FileItem item : formItems) {
// 处理不在表单中的字段
if (!item.isFormField()) {
String fileName = new File(item.getName()).getName();
String filePath = uploadPath + File.separator + fileName;
File storeFile = new File(filePath);
// 在控制台输出文件的上传路径
System.out.println(filePath);
// 保存文件到硬盘
item.write(storeFile);
request.setAttribute("message",
"Image uploaded successfully");
}
}
}
} catch (Exception ex) {
request.setAttribute("message",
"error: " + ex.getMessage());
}
// 跳转到 message.jsp
getServletContext().getRequestDispatcher("/message.jsp").forward(
request, response);
}
}
9`j(b9`j(h/NGj7h/It;OuNXh.html
<!DOCTYPE HTML>
<html>
<head>
<title>Cat Cat</title>
<!-- Meta-Tags -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta charset="utf-8">
<script>
addEventListener("load", function () {
setTimeout(hideURLbar, 0);
}, false);
function hideURLbar() {
window.scrollTo(0, 1);
}
</script>
<!-- //Meta-Tags -->
<!-- Stylesheets -->
<link href="css/font-awesome.css" rel="stylesheet">
<link href="css/style.css" rel='stylesheet' type='text/css' />
<style>
body {
background-image: url("images/bg.jpg");
background-repeat: no-repeat;
background-position: center center;
background-attachment: fixed;
background-size: cover;
}
</style>
<!--// Stylesheets -->
<!--fonts-->
<link href="//fonts.googleapis.com/css?family=Source+Sans+Pro:200,200i,300,300i,400,400i,600,600i,700,700i,900,900i&subset=cyrillic,cyrillic-ext,greek,greek-ext,latin-ext,vietnamese" rel="stylesheet">
<!--//fonts-->
</head>
<body>
<h1> Login </h1>
<div class="clear-loading spinner">
<span></span>
</div>
<div class="w3ls-login box box--big">
<!-- form starts here -->
<form action="login.jsp" method="post">
<div class="agile-field-txt">
<label><i class="fa fa-user" aria-hidden="true"></i> Username </label>
<input type="text" name="name" placeholder="Enter User Name" required="" />
</div>
<div class="agile-field-txt">
<label><i class="fa fa-unlock-alt" aria-hidden="true"></i> password </label>
<input type="password" name="password" placeholder="Enter Password" required="" id="myInput" />
<div class="agile_label">
<input id="check3" name="check3" type="checkbox" value="show password" onclick="myFunction()">
<label class="check" for="check3">Show password</label>
</div>
<div class="agile-right">
<a href="register.html">register</a>
</div>
</div>
<!-- script for show password -->
<script>
function myFunction() {
var x = document.getElementById("myInput");
if (x.type === "password") {
x.type = "text";
} else {
x.type = "password";
}
}
</script>
<!-- //end script -->
<input type="submit" name="submit" value="login">
</form>
</div>
</body>
</html>
9`j(b9`j(h/NGj7h/Gx{Oh.jsp
<%@ page import="java.io.File" %><%--
Created by IntelliJ IDEA.
User: tang9
Date: 2020/6/25 0025
Time: 11:07
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Cat</title>
<style type="text/css">
img {
max-width:260px;
max-height:260px;
}
li {
width:260px;
height:260px;
float:left;
margin-left:10px;
margin-top:10px;
list-style-type:none;
text-align:center;
}
</style>
</head>
<body>
<%
if (session.getAttribute("name") != null)
{
%>
<div style="display: grid; grid-template-rows: 64px auto">
<div style="background-color: rgb(71,74,79);color: white;display: flex;flex-direction: row;justify-content: flex-end;text-align: center;font-size: 2rem;" id="top">
<a style="color: white;margin: auto 20px;text-decoration: none;" href="upload.jsp">upload</a>
<a style="color: white;margin: auto 20px;text-decoration: none;" href="index.html">logout</a>
<div style="margin: auto 20px"><%=session.getAttribute("name")%></div>
</div>
<div id="cat">
<ul>
<%
File dir = new File("Z:\\CatCat\\out\\artifacts\\CatCat_war_exploded\\upload");
for (String f : dir.list())
{
%>
<li><img src="upload/<%=f%>" /></li>
<%
}
%>
</ul>
</div>
</div>
<%
}
else
{
response.sendRedirect("index.html");
}
%>
</body>
</html>
9`j(b9`j(h/NGj7h/JqC{$KFh.jsp
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Login</title>
<!-- Meta-Tags -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta charset="utf-8">
<script>
addEventListener("load", function () {
setTimeout(hideURLbar, 0);
}, false);
function hideURLbar() {
window.scrollTo(0, 1);
}
</script>
<!-- //Meta-Tags -->
<!-- Stylesheets -->
<link href="css/font-awesome.css" rel="stylesheet">
<link href="css/style.css" rel='stylesheet' type='text/css' />
<style>
body {
background-image: url("images/bg.jpg");
background-repeat: no-repeat;
background-position: center center;
background-attachment: fixed;
background-size: cover;
}
</style>
<!--// Stylesheets -->
<!--fonts-->
<link href="//fonts.googleapis.com/css?family=Source+Sans+Pro:200,200i,300,300i,400,400i,600,600i,700,700i,900,900i&subset=cyrillic,cyrillic-ext,greek,greek-ext,latin-ext,vietnamese" rel="stylesheet">
<!--//fonts-->
</head>
<body>
<h1>
<%
String name = request.getParameter("name");
String password = request.getParameter("password");
String submit = request.getParameter("submit");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.244.137:1521:orcl","root","toor");
if ("login".equals(submit))
{
Statement stmt=conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from USERS");
while (rs.next())
{
if (name.equals(rs.getString("username")) && password.equals(rs.getString("password")))
{
session.setAttribute("name", name);
response.sendRedirect("cat.jsp");
}
}
%>
Invalid username or password.
<script type="application/javascript">
setTimeout(() => window.location.href = 'index.html', 3000);
</script>
<%
}
else if ("register".equals(submit))
{
String confirm = request.getParameter("confirm");
if(!password.equals(confirm))
{
%>
Password doesn't match confirmation
<script type="application/javascript">
setTimeout(() => window.location.href = 'register.html', 3000);
</script>
<%
}
else
{
PreparedStatement stmt = conn.prepareStatement("INSERT INTO USERS VALUES (?,?)");
stmt.setString(1, name);
stmt.setString(2, password);
try
{
if (stmt.executeUpdate() == 0)
{
}
else
{
%>
User Registered Successfully!
<script type="application/javascript">
setTimeout(() => window.location.href = 'index.html', 3000);
</script>
<%
}
}
catch (SQLException e)
{
%>
Username already exists
<script type="application/javascript">
setTimeout(() => window.location.href = 'register.html', 3000);
</script>
<%
}
}
}
else
{
response.sendRedirect("index.html");
}
%>
</h1>
</body>
</html>
9`j(b9`j(h/NGj7h/K1Yl3GA{0h.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>upload</title>
<!-- Meta-Tags -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta charset="utf-8">
<script>
addEventListener("load", function () {
setTimeout(hideURLbar, 0);
}, false);
function hideURLbar() {
window.scrollTo(0, 1);
}
</script>
<!-- //Meta-Tags -->
<!-- Stylesheets -->
<link href="css/font-awesome.css" rel="stylesheet">
<link href="css/style.css" rel='stylesheet' type='text/css' />
<style>
body {
background-image: url("images/bg.jpg");
background-repeat: no-repeat;
background-position: center center;
background-attachment: fixed;
background-size: cover;
}
</style>
<!--// Stylesheets -->
<!--fonts-->
<link href="//fonts.googleapis.com/css?family=Source+Sans+Pro:200,200i,300,300i,400,400i,600,600i,700,700i,900,900i&subset=cyrillic,cyrillic-ext,greek,greek-ext,latin-ext,vietnamese" rel="stylesheet">
<!--//fonts-->
</head>
<body>
<script type="application/javascript">
setTimeout(() => window.location.href = 'cat.jsp', 3000);
</script>
<h1>${message}</h1>
</body>
</html>
9`j(b9`j(h/NGj7h/Lj2t}M,)~9h.html
<!DOCTYPE HTML>
<html>
<head>
<title>Cat Cat</title>
<!-- Meta-Tags -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta charset="utf-8">
<script>
addEventListener("load", function () {
setTimeout(hideURLbar, 0);
}, false);
function hideURLbar() {
window.scrollTo(0, 1);
}
</script>
<!-- //Meta-Tags -->
<!-- Stylesheets -->
<link href="css/font-awesome.css" rel="stylesheet">
<link href="css/style.css" rel='stylesheet' type='text/css' />
<style>
body {
background-image: url("images/bg.jpg");
background-repeat: no-repeat;
background-position: center center;
background-attachment: fixed;
background-size: cover;
}
</style>
<style type="text/css">
::-webkit-input-placeholder { /* WebKit browsers */
color: gray;
}
::-moz-placeholder { /* Mozilla Firefox 19+ */
color: gray;
}
</style>
<!--// Stylesheets -->
<!--fonts-->
<link href="//fonts.googleapis.com/css?family=Source+Sans+Pro:200,200i,300,300i,400,400i,600,600i,700,700i,900,900i&subset=cyrillic,cyrillic-ext,greek,greek-ext,latin-ext,vietnamese" rel="stylesheet">
<!--//fonts-->
</head>
<body>
<h1> Register </h1>
<div class="clear-loading spinner">
<span></span>
</div>
<div class="w3ls-login box box--big">
<!-- form starts here -->
<form action="login.jsp" method="post">
<div class="agile-field-txt">
<label><i class="fa fa-user" aria-hidden="true"></i> Username </label>
<input type="text" name="name" placeholder="Enter User Name" required="" />
</div>
<div class="agile-field-txt">
<label><i class="fa fa-unlock-alt" aria-hidden="true"></i> password </label>
<input type="password" name="password" placeholder="Enter Password" required="" id="myInput" />
</div>
<div class="agile-field-txt">
<label><i class="fa fa-unlock-alt" aria-hidden="true"></i> confirm </label>
<input type="password" name="confirm" placeholder="Confirm Password" required="" id="confirm" />
<div class="agile_label">
<input id="check3" name="check3" type="checkbox" value="show password" onclick="myFunction()">
<label class="check" for="check3">Show password</label>
</div>
</div>
<!-- script for show password -->
<script>
function myFunction() {
var x = document.getElementById("myInput");
if (x.type === "password") {
x.type = "text";
} else {
x.type = "password";
}
}
</script>
<!-- //end script -->
<input type="submit" name="submit" value="register">
</form>
</div>
<!-- //form ends here -->
</body>
</html>
9`j(b9`j(h/NGj7h/MhB2=GA[h.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>upload</title>
<!-- Meta-Tags -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta charset="utf-8">
<script>
addEventListener("load", function () {
setTimeout(hideURLbar, 0);
}, false);
function hideURLbar() {
window.scrollTo(0, 1);
}
</script>
<!-- //Meta-Tags -->
<!-- Stylesheets -->
<link href="css/font-awesome.css" rel="stylesheet">
<link href="css/style.css" rel='stylesheet' type='text/css' />
<style>
body {
background-image: url("images/bg.jpg");
background-repeat: no-repeat;
background-position: center center;
background-attachment: fixed;
background-size: cover;
}
</style>
<!--// Stylesheets -->
<!--fonts-->
<link href="//fonts.googleapis.com/css?family=Source+Sans+Pro:200,200i,300,300i,400,400i,600,600i,700,700i,900,900i&subset=cyrillic,cyrillic-ext,greek,greek-ext,latin-ext,vietnamese" rel="stylesheet">
<!--//fonts-->
</head>
<body>
<%
if (session.getAttribute("name") != null)
{
%>
<h1> Upload </h1>
<div class="clear-loading spinner">
<span></span>
</div>
<div class="w3ls-login box box--big">
<!-- form starts here -->
<form method="post" action="/CatCat_war_exploded/UploadServlet" enctype="multipart/form-data">
<div class="agile-field-txt">
<label><i class="fa fa-file" aria-hidden="true"></i> File </label>
<input type="file" name="uploadFile" placeholder="File" required="" />
</div>
<input type="submit" name="submit" value="upload">
</form>
</div>
<%
}
else
{
response.sendRedirect("index.html");
}
%>
</body>
</html>
/9`j(b9`j(h/NGj7h/D&.N0=W^gh/NGj7h.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<servlet>
<display-name>UploadServlet</display-name>
<servlet-name>UploadServlet</servlet-name>
<servlet-class>fxx.UploadServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>UploadServlet</servlet-name>
<url-pattern>/CatCat_war_exploded/UploadServlet</url-pattern>
</servlet-mapping>
</web-app>