merge
SQL> create table xs1 as select * from xs;
Table created
SQL> truncate xs1;
truncate xs1
ORA-03290: 无效的截断命令 - 缺失 CLUSTER 或 TABLE 关键字
SQL> truncated table xs1;
truncated table xs1
ORA-00900: 无效 SQL 语句
SQL> truncate table xs1;
Table truncated
SQL> select * from xs1;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
SQL> insert into xs1 values('aa','bb','cc','dd','ee','ff','gg');
insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
ORA-01858: 在要求输入数字处找到非数字字符
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> insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd'),30,'gg');
2 ;
3 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
4 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
5
6
6 insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd')',30,'gg');
insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd')',30,'gg');
;
insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd')',30,'gg')
ORA-00917: 缺失逗号
SQL> 2 ;
2
ORA-00900: 无效 SQL 语句
SQL> 3 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
2 4 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
3 ;/
4 /
3 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
4 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
;/
ORA-00900: 无效 SQL 语句
SQL> insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd'),30,'gg');
1 row inserted
SQL> select * from xs1;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
aa bb cc dd 2000/2/7 30 gg
SQL> merge into xs a
2 using xs1 b
3 on (a.xh=b.xh)
4 when matched then
5 update
6 set a.xm=b.xm,
7 a.zym=b.zym,
8 a.xb=b.xb,
9 a.cssj=b.cssj,
10 a.zxf=b.zxf,
11 z.bz=b.bz
12 when not matched then
13 insert
14 values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
15 ;
merge into xs a
using xs1 b
on (a.xh=b.xh)
when matched then
update
set a.xm=b.xm,
a.zym=b.zym,
a.xb=b.xb,
a.cssj=b.cssj,
a.zxf=b.zxf,
z.bz=b.bz
when not matched then
insert
values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
ORA-00904: "Z"."BZ": 标识符无效
SQL>
SQL> merge into xs a
2 using xs1 b
3 on (a.xh=b.xh)
4 when matched then
5 update
6 set a.xm=b.xm,
7 a.zym=b.zym,
8 a.xb=b.xb,
9 a.cssj=b.cssj,
10 a.zxf=b.zxf,
11 a.bz=b.bz
12 when not matched then
13 insert
14 values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
15 ;
merge into xs a
using xs1 b
on (a.xh=b.xh)
when matched then
update
set a.xm=b.xm,
a.zym=b.zym,
a.xb=b.xb,
a.cssj=b.cssj,
a.zxf=b.zxf,
z.bz=b.bz
when not matched then
insert
values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
ORA-00904: "Z"."BZ": 标识符无效
SQL>
SQL>
SQL> merge into xs a
2 using xs1 b
3 on (a.xh=b.xh)
4 when matched then
5 update
6 set a.xm=b.xm,
7 a.zym=b.zym,
8 a.xb=b.xb,
9 a.cssj=b.cssj,
10 a.zxf=b.zxf,
11 a.bz=b.bz
12 when not matched then
13 insert
14 values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
15 ;
Done
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system
SQL> create table emp as select * from scott.emp;
Table created
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 1987/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 1987/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> select * from xs_kc;
select * from xs_kc
ORA-00942: 表或视图不存在
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
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
java:
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) {
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
// new oracle.jdbc.driver.OracleDriver(); 通过Class静态类中//的forName（）//方法加载数据库驱动
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL1", "system", "test");
// 建立连接，加载驱动类并在DriverManager类中注册后，即可以通过//getConnection()方法//发出请求连接，参数1表示数据库的URL地址，参数//2是用户名，参数3是密码。
stmt = conn.createStatement();
// 创建statement对象
rs = stmt.executeQuery("select * from emp");
// 执行executeQuery()方法，用于产生单个结果集。
while (rs.next()) {
System.out.print(rs.getString("empno")+" ");
System.out.print(rs.getString("ename")+" ");
System.out.print(rs.getString("deptno")+" ");
System.out.print(rs.getInt("sal")+" ");
System.out.println(rs.getInt("deptno"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package test;
import java.sql.*;
public class Test1 {
public static void main(String[] args) {
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
// new oracle.jdbc.driver.OracleDriver(); 通过Class静态类中的forName（）//方法加载数据库驱动
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL1", "system", "test");
//建立连接，加载驱动类并在DriverManager类中注册后，即可以通过getConnection()方法//发出请求连接，参数1表示数据库的URL地址，参数2是用户名，参数3是密码。
stmt = conn.createStatement();
//创建statement对象
rs = stmt.executeQuery("select * from emp");
//执行executeQuery()方法，用于产生单个结果集。
while (rs.next()) {
System.out.println(rs.getString("empno"));
System.out.println(rs.getString("ename"));//----------
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
SQL> declare
2 ex+text varchar2(30);
3 begin
4 ex+text:='hhhhhhhhhhh';
5 dbms_output.put_line(ex+text);
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
declare
ex+text varchar2(30);
begin
x+text:='hhhhhhhhhhh';
dbms_output.put_line(ex+text);
exception
when others then
dbms_output.put_line('error');
end;
ORA-06550: 第 3 行, 第 3 列:
PLS-00103: 出现符号 "+"在需要下列之一时：
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> declare
2 tests varchar2(20);
3 begin
4 test:='hhhhhhhhhhh';
5 dbms_output.put_line(tests)
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
11
12 /
declare
tests varchar2(20);
begin
test:='hhhhhhhhhhh';
dbms_output.put_line(tests)
exception
when others then
dbms_output.put_line('error');
end;
/
ORA-06550: 第 7 行, 第 3 列:
PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：
:= . ( % ;
符号 ";" 被替换为 "EXCEPTION" 后继续。
ORA-06550: 第 11 行, 第 7 列:
PLS-00103: 出现符号 "/"符号 "/" 被忽略。
SQL>
SQL> declare
2 tests varchar2(20);
3 begin
4 test:='hhhhhhhhhhh';
5 dbms_output.put_line(tests)
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
declare
tests varchar2(20);
begin
test:='hhhhhhhhhhh';
dbms_output.put_line(tests)
exception
when others then
dbms_output.put_line('error');
end;
ORA-06550: 第 7 行, 第 3 列:
PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：
:= . ( % ;
符号 ";" 被替换为 "EXCEPTION" 后继续。
SQL>
SQL>
SQL>
SQL> declare
2 tests varchar2(20);
3 begin
4 tests:='hhhhhhhhhhh';
5 dbms_output.put_line(tests)
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
declare
tests varchar2(20);
begin
tests:='hhhhhhhhhhh';
dbms_output.put_line(tests)
exception
when others then
dbms_output.put_line('error');
end;
ORA-06550: 第 7 行, 第 3 列:
PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：
:= . ( % ;
符号 ";" 被替换为 "EXCEPTION" 后继续。
SQL>
SQL>
SQL> declare
2 tests varchar2(20);
3 begin
4 tests:='hhhhhhhhhhh';
5 dbms_output.put_line(tests);
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
PL/SQL procedure successfully completed
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL> declare
2 row_id rowid;
3 info varchar2(100);
4 begin
5 update scott.dept set deptno=90 where dname="rrrr" returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
6 dbms_output.put_line('rowid:'||row_id);
7 dbms_output.put_line(info);
8 end;
9 /
declare
row_id rowid;
info varchar2(100);
begin
update scott.dept set deptno=90 where dname="rrrr" returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
dbms_output.put_line('rowid:'||row_id);
dbms_output.put_line(info);
end;
ORA-06550: 第 6 行, 第 47 列:
PL/SQL: ORA-00904: "rrrr": 标识符无效
ORA-06550: 第 6 行, 第 3 列:
PL/SQL: SQL Statement ignored
SQL>
SQL> declare
2 row_id rowid;
3 info varchar2(100);
4 begin
5 update scott.dept set deptno=90 where dname="research" returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
6 dbms_output.put_line('rowid:'||row_id);
7 dbms_output.put_line(info);
8 end;
9 /
declare
row_id rowid;
info varchar2(100);
begin
update scott.dept set deptno=90 where dname="rrrr" returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
dbms_output.put_line('rowid:'||row_id);
dbms_output.put_line(info);
end;
ORA-06550: 第 6 行, 第 47 列:
PL/SQL: ORA-00904: "rrrr": 标识符无效
ORA-06550: 第 6 行, 第 3 列:
PL/SQL: SQL Statement ignored
SQL>
SQL>
SQL> declare
2 row_id rowid;
3 info varchar2(100);
4 begin
5 update scott.dept set deptno=90 where dname="research" returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
6 dbms_output.put_line('rowid:'||row_id);
7 dbms_output.put_line(info);
8 end;
9 /
declare
row_id rowid;
info varchar2(100);
begin
update scott.dept set deptno=90 where dname="research" returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
dbms_output.put_line('rowid:'||row_id);
dbms_output.put_line(info);
end;
ORA-06550: 第 6 行, 第 47 列:
PL/SQL: ORA-00904: "research": 标识符无效
ORA-06550: 第 6 行, 第 3 列:
PL/SQL: SQL Statement ignored
SQL>
SQL> declare
2 row_id rowid;
3 info varchar2(100);
4 begin
5 update scott.dept set deptno=90 where dname='research' returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
6 dbms_output.put_line('rowid:'||row_id);
7 dbms_output.put_line(info);
8 end;
9 /
PL/SQL procedure successfully completed
SQL> declare
2 v1 constant varchar2(4):='la';
3 v2 constant varchar2(4):='ff';
4 begin
5 dbms_output.put_line(v2||''||v1);
6 end;
7 /
PL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL>
SQL> declare
2 row_id rowid;
3 info varchar2(100);
4 begin
5 update scott.dept set deptno=90 where dname='research' returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
6 dbms_output.put_line('rowid:'||row_id);
7 dbms_output.put_line(info);
8 end;
9 /
rowid:
PL/SQL procedure successfully completed
SQL>
SQL>
SQL> declare
2 number1 constant number(4):=7900;
3 name1 scott.emp.ename%type;
4 job1 scott.emp.job%type;
5 sal1 scott.emp.sal%type;
6 begin
7 select ename,job,sal into
8 name1,job1,sal1 from scott.emp where empno=number1;
9
10 dbms_output.put_line('salary::'||sal1);
11 end;
12 /
salary::950
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 v_1 number;
3 begin
4 select count(*) into v_1 from xs;
5 dbms_output.put_line(v_1);
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
4
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 emp1 scott.emp%rowtype;
3 begin
4 select * into emp1 from scott.emp where empno=7900;
5 dbms_output.put_line('dddddd'||emp1.sal);
6 end;
7 /
dddddd950
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 v_emp emp%rowtype;
3 v_ename emp.ename%type;
4 v_sal emp.sal%type;
5 begin
6 select * into v_emp from scott.emp where empno=7900;
7 dbms_output.put_line('dddddd'||v_emp.sal);
8 select ename,sal into v_ename,v_sal from emp where empno=7900;
9 dbms_output.put_line(v_ename||v_sal);
10 end;
11 /
dddddd950
JAMES950
PL/SQL procedure successfully completed
SQL>
SQL>
SQL> create table xs1 as select * from xs;
Table created
SQL> truncate xs1;
truncate xs1
ORA-03290: 无效的截断命令 - 缺失 CLUSTER 或 TABLE 关键字
SQL> truncated table xs1;
truncated table xs1
ORA-00900: 无效 SQL 语句
SQL> truncate table xs1;
Table truncated
SQL> select * from xs1;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
SQL> insert into xs1 values('aa','bb','cc','dd','ee','ff','gg');
insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
ORA-01858: 在要求输入数字处找到非数字字符
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> insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd'),30,'gg');
2 ;
3 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
4 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
5
6
6 insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd')',30,'gg');
insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd')',30,'gg');
;
insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd')',30,'gg')
ORA-00917: 缺失逗号
SQL> 2 ;
2
ORA-00900: 无效 SQL 语句
SQL> 3 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
2 4 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
3 ;/
4 /
3 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
4 insert into xs1 values('aa','bb','cc','dd','ee','ff','gg')
;/
ORA-00900: 无效 SQL 语句
SQL> insert into xs1 values('aa','bb','cc','dd',to_date('20000207','yyyymmdd'),30,'gg');
1 row inserted
SQL> select * from xs1;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
aa bb cc dd 2000/2/7 30 gg
SQL> merge into xs a
2 using xs1 b
3 on (a.xh=b.xh)
4 when matched then
5 update
6 set a.xm=b.xm,
7 a.zym=b.zym,
8 a.xb=b.xb,
9 a.cssj=b.cssj,
10 a.zxf=b.zxf,
11 z.bz=b.bz
12 when not matched then
13 insert
14 values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
15 ;
merge into xs a
using xs1 b
on (a.xh=b.xh)
when matched then
update
set a.xm=b.xm,
a.zym=b.zym,
a.xb=b.xb,
a.cssj=b.cssj,
a.zxf=b.zxf,
z.bz=b.bz
when not matched then
insert
values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
ORA-00904: "Z"."BZ": 标识符无效
SQL>
SQL> merge into xs a
2 using xs1 b
3 on (a.xh=b.xh)
4 when matched then
5 update
6 set a.xm=b.xm,
7 a.zym=b.zym,
8 a.xb=b.xb,
9 a.cssj=b.cssj,
10 a.zxf=b.zxf,
11 a.bz=b.bz
12 when not matched then
13 insert
14 values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
15 ;
merge into xs a
using xs1 b
on (a.xh=b.xh)
when matched then
update
set a.xm=b.xm,
a.zym=b.zym,
a.xb=b.xb,
a.cssj=b.cssj,
a.zxf=b.zxf,
z.bz=b.bz
when not matched then
insert
values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
ORA-00904: "Z"."BZ": 标识符无效
SQL>
SQL>
SQL> merge into xs a
2 using xs1 b
3 on (a.xh=b.xh)
4 when matched then
5 update
6 set a.xm=b.xm,
7 a.zym=b.zym,
8 a.xb=b.xb,
9 a.cssj=b.cssj,
10 a.zxf=b.zxf,
11 a.bz=b.bz
12 when not matched then
13 insert
14 values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz)
15 ;
Done
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
aa bb cc dd 2000/2/7 30 gg
SQL>
一、基础
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
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
001 001 1 11 1
001 001 1 11 1
SQL> create table class
2 ( xh varchar2(2) not null,
3 kch varchar2(6) not null,
4 cj number(2) null,
5 constraint con1 primary key (xh,kch)
6 )
7 tablespace users;
Table created
SQL> alter table class move tablespace system;
Table altered
SQL> create table course
2 (
3 cno varchar2(2) primary key,
4 cname varchar2(10),
5 cpno varchar2(10),
6 creadit smallint,
7 foreign key(cpno) references course (cno)
8 );
create table course
(
cno varchar2(2) primary key,
cname varchar2(10),
cpno varchar2(10),
creadit smallint,
foreign key(cpno) references course (cno)
)
ORA-00955: 名称已由现有对象使用
SQL> select * from course;
CNO CNAME CPNO CCREDIT
---------- ---------- ---------- ----------
1 数据库 5 4
SQL> create table xs_jsj as select * from scott.xs_kc where cj>80;
create table xs_jsj as select * from scott.xs_kc where cj>80
ORA-00942: 表或视图不存在
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
SQL> create table xs_jsj as select * from xs_kc where cj>90;
Table created
SQL> select * from xs_jsj;
XH KCH CJ XF
------ ------ --------------------------------------- ---
061107 101 98
SQL> alter table xs_jsj add(jxj number(1),
2 djsm varchar2(10) default 'reward 1000 yuan');
alter table xs_jsj add(jxj number(1),
djsm varchar2(10) default 'reward 1000 yuan')
ORA-01401: 插入的值对于列过大
SQL>
SQL> alter table xs_jsj add(jxj number(1),
2 djsm varchar2(10) default 'reward');
3
SQL> alter table xs_jsj add(jxj number(1),
2 djsm varchar2(10) default 'reward');
Table altered
SQL> select * from xs_jsj;
XH KCH CJ XF JXJ DJSM
------ ------ --------------------------------------- --- --- ----------
061107 101 98 reward
SQL> alter table xs_jsj modify (djsm default '800yuan');
Table altered
SQL> alter table xs_jsj drop column djsm;
Table altered
SQL> select * from xs_jsj;
XH KCH CJ XF JXJ
------ ------ --------------------------------------- --- ---
061107 101 98
SQL> drop table course;
Table dropped
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
SQL> select * from xscj;
select * from xscj
ORA-00942: 表或视图不存在
SQL> alter table xs_jsj add(birth date);
Table altered
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
SQL> select * from xs_jsj;
XH KCH CJ XF JXJ BIRTH
------ ------ --------------------------------------- --- --- -----------
061107 101 98
SQL> insert into xs_jsj(xh,kck,birth) values ('01010','111',to_date('1999-02-07',
2 'yyyy-mm-dd'));
insert into xs_jsj(xh,kck,birth) values ('01010','111',to_date('1999-02-07',
'yyyy-mm-dd'))
ORA-00904: "KCK": 标识符无效
SQL> insert into xs_jsj(xh,kch,birth) values ('01010','111',to_date('1999-02-07',
2 'yyyy-mm-dd'));
1 row inserted
SQL> select * from xs_jsj;
XH KCH CJ XF JXJ BIRTH
------ ------ --------------------------------------- --- --- -----------
061107 101 98
01010 111 1999/2/7
SQL> select to_date('1999-03-23','yyyy-mm-dd') from dual;
TO_DATE('1999-03-23','YYYY-MM-
------------------------------
1999/3/23
SQL> select 1+1 from dual;
1+1
----------
2
SQL> select 1*1 from dual;
1*1
----------
1
SQL> select 3452534534536464/2534543 from dual;
sele
3452534534536464/2534543
------------------------
1362192132.67893
SQL> select 456983745435768437465843245/32432 from dual;
456983745435768437465843245/32
------------------------------
1.40905200245365E22
SQL> select 'aaaaaa'||'ffffff' from dual;
'AAAAAA'||'FFFFFF'
------------------
aaaaaaffffff
SQL> select * from xs_jsj;
XH KCH CJ XF JXJ BIRTH
------ ------ --------------------------------------- --- --- -----------
061107 101 98
SQL> delete from xs_jsj where xh='23223';
0 rows deleted
SQL> update xs_jsj set xh='101010' where xh='061107';
1 row updated
SQL> create table cp1 as select * from xs_jsj;
Table created
SQL> select * from cp1;
XH KCH CJ XF JXJ BIRTH
------ ------ --------------------------------------- --- --- -----------
101010 101 98
SQL> truncate table xs_jsj;
truncate table xs_jsj
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
SQL> insert into cp1 select * from xs_kc;
insert into cp1 select * from xs_kc
二、List分区
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system
SQL> select * from xs_jsj;
XH KCH CJ XF JXJ BIRTH
------ ------ --------------------------------------- --- --- -----------
061107 101 98
SQL> delete from xs_jsj where xh='23223';
0 rows deleted
SQL> update xs_jsj set xh='101010' where xh='061107';
1 row updated
SQL> create table cp1 as select * from xs_jsj;
Table created
SQL> select * from cp1;
XH KCH CJ XF JXJ BIRTH
------ ------ --------------------------------------- --- --- -----------
101010 101 98
SQL> truncate table xs_jsj;
truncate table xs_jsj
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
SQL> insert into cp1 select * from xs_kc;
insert into cp1 select * from xs_kc
ORA-00947: 没有足够的值
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
7 partition by list(bookpress)
8 partition part1 values('清华大学出版社') tablespace system,
9 partition part2 values('人民教育出版社') tablespace users);
create table pb1(
bid number(5),
bookname varchar2(10),
bookpress varchar2(10),
booktime varchar2(10)
)
(partition by list(bookpress)
partition part1 values('清华大学出版社') tablespace system,
partition part2 values('人民教育出版社') tablespace users)
ORA-00922: 选项缺失或无效
SQL>
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
7 partition by list(bookpress)
8
8 (partition by list(bookpress)
9 partition part1 values('清华大学出版社') tablespace system,
10 partition part2 values('人民教育出版社') tablespace users)
11 ;
create table pb1(
bid number(5),
bookname varchar2(10),
bookpress varchar2(10),
booktime varchar2(10)
)
(partition by list(bookpress)
(partition part1 values('清华大学出版社') tablespace system,
partition part2 values('人民教育出版社') tablespace users)
ORA-14006: 无效的分区名
SQL>
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
7
SQL>
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
SQL> (partition by list(bookpress)
2 partition part1 values('清华大学出版社') tablespace system,
3 partition part2 values('人民教育出版社') tablespace users)
4
SQL>
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
7 partition by list(bookpress)
8
8 (partition part1 values('清华大学出版社') tablespace system,
9 partition part2 values('人民教育出版社') tablespace users)
10 ;
create table pb1(
bid number(5),
bookname varchar2(10),
bookpress varchar2(10),
booktime varchar2(10)
)
partition by list(bookpress)
(partition part1 values('清华大学出版社') tablespace system,
partition part2 values('人民教育出版社') tablespace users)
ORA-14036: 列的分区界限值过大
SQL>
SQL> create table pb1(
2 bid number(10),
3 bookname varchar2(30),
4 bookpress varchar2(30),
5 booktime varchar2(30)
6 )
7 partition by list(bookpress)
8
8 (partition part1 values('清华大学出版社') tablespace system,
9 partition part2 values('人民教育出版社') tablespace users)
10 ;
create table pb2(
bid number(10),
bookname varchar2(30),
bookpress varchar2(30),
booktime varchar2(30)
)
partition by list(bookpress)
(partition part1 values('清华大学出版社') tablespace system,
partition part2 values('人民教育出版社') tablespace users);
ORA-14036: 列的分区界限值过大
SQL>
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
7 partition by list(bookpress)
8
SQL>
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
7
7 partition by list(bookpress)
8 (partition part1 values('清华大学') tablespace system,
9
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create table pb1(
2 bid number(5),
3 bookname varchar2(10),
4 bookpress varchar2(10),
5 booktime varchar2(10)
6 )
7 partition by list(bookpress)
8 (partition part1 values('清华大学') tablespace system,
9 partition part2 values('人民教育') tablespace users);
Table created
SQL> select * from pb1;
BID BOOKNAME BOOKPRESS BOOKTIME
------ ---------- ---------- ----------
SQL>
SQL> create table pb2(
2 bid number(10),
3 bookname varchar2(30),
4 bookpress varchar2(30),
5 booktime varchar2(30)
6 )
7 partition by list(bookpress)
8 (partition part1 values('清华大学出版社') tablespace system,
9 partition part2 values('人民教育出版社') tablespace users);
Table created
SQL> insert into pb2 values(1,'oracal','清华大学出版社'to_date('20120203','yyyymmdd'));
insert into pb2 values(1,'oracal','清华大学出版社',to_date('20120203','yyyymmdd'))
ORA-00917: 缺失逗号
SQL> insert into pb2 values(1,'oracal','清华大学出版社',to_date('20120203','yyyymmdd'));
1 row inserted
SQL> select * from pb2;
BID BOOKNAME BOOKPRESS BOOKTIME
----------- ------------------------------ ------------------------------ ------------------------------
1 oracal 清华大学出版社 03-2月 -12
SQL> insert into pb2 values(1,'oracal','人民教育出版社',to_date('20120203','yyyymmdd'))
;
2 ;
1 row inserted
SQL> select * from pb2;
BID BOOKNAME BOOKPRESS BOOKTIME
----------- ------------------------------ ------------------------------ ------------------------------
1 oracal 清华大学出版社 03-2月 -12
1 oracal 人民教育出版社 03-2月 -12
SQL> update pb2 set bid='2' where BOOKPRESS='人民教育出版社';
1 row updated
SQL> select * from pb2;
BID BOOKNAME BOOKPRESS BOOKTIME
----------- ------------------------------ ------------------------------ ------------------------------
1 oracal 清华大学出版社 03-2月 -12
2 oracal 人民教育出版社 03-2月 -12
SQL> insert into pb2 values(3,'mysql','清华大学出版社' to_date('1999-04-09','yyyy-mm-dd'));
insert into pb2 values(3,'mysql','清华大学出版社' ,to_date('1999-04-09','yyyy-mm-dd'))
ORA-00917: 缺失逗号
SQL> insert into pb2 values(3,'mysql','清华大学出版社' ,to_date('1999-04-09','yyyy-mm-dd'));
1 row inserted
SQL> select * from pb2;
BID BOOKNAME BOOKPRESS BOOKTIME
----------- ------------------------------ ------------------------------ ------------------------------
1 oracal 清华大学出版社 03-2月 -12
3 mysql 清华大学出版社 09-4月 -99
2 oracal 人民教育出版社 03-2月 -12
SQL> alter table pb2 add partition part3 values(default) tablespace system;
Table altered
SQL> select * from pb2;
BID BOOKNAME BOOKPRESS BOOKTIME
----------- ------------------------------ ------------------------------ ------------------------------
1 oracal 清华大学出版社 03-2月 -12
3 mysql 清华大学出版社 09-4月 -99
2 oracal 人民教育出版社 03-2月 -12
SQL> select * from dba_tab_partitions where table_name='pb2';
TABLE_OWNER TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ ------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
SQL> select * from dba_tab_partitions where table_name='pb1';
TABLE_OWNER TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ ------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
SQL> select * from dba_tab_partitions where table_name='xs_kc';
TABLE_OWNER TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ ------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
SQL> select * from dba_part_tables where table_name='pb2';
OWNER TABLE_NAME PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT SUBPARTITIONING_KEY_COUNT STATUS DEF_TABLESPACE_NAME DEF_PCT_FREE DEF_PCT_USED DEF_INI_TRANS DEF_MAX_TRANS DEF_INITIAL_EXTENT DEF_NEXT_EXTENT DEF_MIN_EXTENTS DEF_MAX_EXTENTS DEF_PCT_INCREASE DEF_FREELISTS DEF_FREELIST_GROUPS DEF_LOGGING DEF_COMPRESSION DEF_BUFFER_POOL
------------------------------ ------------------------------ ----------------- -------------------- --------------- ---------------------- ---------------------- ------------------------- -------- ------------------------------ ------------ ------------ ------------- ------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------- ------------------- ----------- --------------- ---------------
三、Range分区
SQL> create table part_book
2 ( bid number(4),
3 bookname VARCHAR2(20),
4 bookpress VARCHAR2(30),
5 booktime date)
6 partition by range(booktime)
7 (partition part1 values less than(to_date('20100101','yyyymmdd')) tablespace system,
8 partition part2 values less than (to_date('20120101','yyyymmdd')) tablespace users,
9 partition part3 values less than (MAXVALUE) tablespace users
10 );
Table created
SQL> create table pb4
2 (
3 bid number(4),
4 bname varchar2(20),
5 bpress varchar2(20),
6 btime date)
7 partition by range (btime)
8 (partition part1 values less than (to_date('20110201','yyyymmdd')) tablespace system,
9 partition part2 values les than (to_date('20140508','yyyymmdd'))tablespace users,
10 partition part3 values less than (MAXVALUE) tablespace users
11 );
create table pb4
(
bid number(4),
bname varchar2(20),
bpress varchar2(20),
btime date)
partition by range (btime)
(partition part1 values less than (to_date('20110201','yyyymmdd')) tablespace system,
partition part2 values less than (to_date('20140508','yyyymmdd'))tablespace users,
partition part3 values less than (MAXVALUE) tablespace users
)
ORA-14007: 缺失 LESS 关键字
SQL>
SQL> create table pb4
2 (
3 bid number(4),
4 bname varchar2(20),
5 bpress varchar2(20),
6 btime date)
7 partition by range (btime)
8 (partition part1 values less than (to_date('20110201','yyyymmdd')) tablespace system,
9 partition part2 values less than (to_date('20140508','yyyymmdd'))tablespace users,
10 partition part3 values less than (MAXVALUE) tablespace users
11 )
12 ;
Table created
SQL> insert into pb4 values(1,'oracal','清华大学出版社', to_date('19990206','yyyymmdd'));
1 row inserted
SQL> select * from pb;
select * from pb
ORA-00942: 表或视图不存在
SQL> select * from pb4;
BID BNAME BPRESS BTIME
----- -------------------- -------------------- -----------
1 oracal 清华大学出版社 1999/2/6
SQL> alter table p4 split partition part3 at (to_date('20160709','yyyymmdd))
2 into (partition part3,partition part4);
3
4 alter table p4 split partition part3 at (to_date('20160709','yyyymmdd))
5 alter table p4 split partition part3 at (to_date('20160709','yyyymmdd))
6
6 alter table p4 split partition part3 at (to_date('20160709','yyyymmdd))
7 2 into (partition part3,partition part4);
SQL> alter table p4 split partition part3 at (to_date('20160709','yyyymmdd'))
SQL> into (partition part3,partition part4);
into (partition part3,partition part4)
ORA-00900: 无效 SQL 语句
SQL> alter table p4 split partition part3 at (to_date('20160709','yyyymmdd'))
2 into (partition part3,partition part4);
alter table pb4 split partition part3 at (to_date('20160709','yyyymmdd'))
into (partition part3,partition part4)
ORA-00942: 表或视图不存在
SQL>
SQL> alter table pb4 split partition part3 at (to_date('20160709','yyyymmdd'))
2 into (partition part3,partition part4);
3
SQL>
SQL> alter table pb4 split partition part3 at (to_date('20160709','yyyymmdd'))
2 into (partition part3,partition part4)
3 ;
Table altered
SQL> select * from pb4;
BID BNAME BPRESS BTIME
----- -------------------- -------------------- -----------
1 oracal 清华大学出版社 1999/2/6
SQL> begin
2 insert into pb4 values(2,'oracal++','清华大学出版社', to_date('20190206','yyyymmdd'));
3 insert into pb4 values(3,'mysql','人民教育出版社', to_date('20150206','yyyymmdd'));
4 end;
5 end;
6 end;
7 /
begin
insert into pb4 values(2,'oracal++','清华大学出版社', to_date('20190206','yyyymmdd'));
insert into pb4 values(3,'mysql','人民教育出版社', to_date('20150206','yyyymmdd'));
end;
end;
end;
ORA-06550: 第 6 行, 第 1 列:
PLS-00103: 出现符号 "END"
SQL> begin
2 insert into pb4 values(2,'oracal++','清华大学出版社', to_date('20190206','yyyymmdd'));
3 insert into pb4 values(3,'mysql','人民教育出版社', to_date('20150206','yyyymmdd'));
4 end;
5 /
PL/SQL procedure successfully completed
SQL> select * from pb4;
BID BNAME BPRESS BTIME
----- -------------------- -------------------- -----------
1 oracal 清华大学出版社 1999/2/6
3 mysql 人民教育出版社 2015/2/6
2 oracal++ 清华大学出版社 2019/2/6
SQL> alter table pb4 drop partition part4;
Table altered
SQL> begin
2 insert into pb4 values(2,'oracal++','清华大学出版社', to_date('20190206','yyyymmdd'));
3 insert into pb4 values(3,'mysql','人民教育出版社', to_date('20150206','yyyymmdd'));
4 end;
5 /
PL/SQL procedure successfully completed
SQL> select * from pb4;
BID BNAME BPRESS BTIME
----- -------------------- -------------------- -----------
1 oracal 清华大学出版社 1999/2/6
3 mysql 人民教育出版社 2015/2/6
2 oracal++ 清华大学出版社 2019/2/6
SQL> alter table pb4 drop partition part4;
Table altered
SQL>
SQL> create table MobileMessage
2 (
3 ACCT_MONTH VARCHAR2(6), -- 帐期 格式：年月 YYYYMM
4 AREA_NO VARCHAR2(10), -- 地域号码
5 DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD
6 SUBSCRBID VARCHAR2(20), -- 用户标识
7 SVCNUM VARCHAR2(30) -- 手机号码
8 )
9 partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)
10 (
11 partition p1 values less than('200705','012')
12 (
13 subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),
14 subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),
15 subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')
16 ),
17 partition p2 values less than('200709','014')
18 (
19 subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),
20 subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),
21 subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')
22 ),
23 partition p3 values less than('200801','016')
24 (
25 subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),
26 subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),
27 subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')
28 )
29 )
30 ;
Table created
SQL> begin
2
2 insert into MobileMessage values('200701','010','04','ghk001','13800000000');
3 insert into MobileMessage values('200702','015','12','myx001','13633330000');
4 insert into MobileMessage values('200703','015','24','hjd001','13300000000');
5 insert into MobileMessage values('200704','010','04','ghk001','13800000000');
6 insert into MobileMessage values('200705','010','04','ghk001','13800000000');
7 insert into MobileMessage values('200705','011','18','sxl001','13222000000');
8 insert into MobileMessage values('200706','011','21','sxl001','13222000000');
9 insert into MobileMessage values('200706','012','11','tgg001','13800044400');
10 insert into MobileMessage values('200707','010','04','ghk001','13800000000');
11 insert into MobileMessage values('200708','012','24','tgg001','13800044400');
12 insert into MobileMessage values('200709','014','29','zjj001','13100000000');
13 insert into MobileMessage values('200710','014','29','zjj001','13100000000');
14 insert into MobileMessage values('200711','014','29','zjj001','13100000000');
15 insert into MobileMessage values('200711','013','30','wgc001','13444000000');
16 insert into MobileMessage values('200712','013','30','wgc001','13444000000');
17 insert into MobileMessage values('200712','010','30','ghk001','13800000000');
18 insert into MobileMessage values('200801','015','22','myx001','13633330000');
19 end;/
20 /
begin
insert into MobileMessage values('200701','010','04','ghk001','13800000000');
insert into MobileMessage values('200702','015','12','myx001','13633330000');
insert into MobileMessage values('200703','015','24','hjd001','13300000000');
insert into MobileMessage values('200704','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','011','18','sxl001','13222000000');
insert into MobileMessage values('200706','011','21','sxl001','13222000000');
insert into MobileMessage values('200706','012','11','tgg001','13800044400');
insert into MobileMessage values('200707','010','04','ghk001','13800000000');
insert into MobileMessage values('200708','012','24','tgg001','13800044400');
insert into MobileMessage values('200709','014','29','zjj001','13100000000');
insert into MobileMessage values('200710','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','010','30','ghk001','13800000000');
insert into MobileMessage values('200801','015','22','myx001','13633330000');
end;/
ORA-06550: 第 20 行, 第 5 列:
PLS-00103: 出现符号 "/"符号 "/" 被忽略。
SQL>
SQL> begin
2 insert into MobileMessage values('200701','010','04','ghk001','13800000000');
3 insert into MobileMessage values('200702','015','12','myx001','13633330000');
4 insert into MobileMessage values('200703','015','24','hjd001','13300000000');
5 insert into MobileMessage values('200704','010','04','ghk001','13800000000');
6 insert into MobileMessage values('200705','010','04','ghk001','13800000000');
7 insert into MobileMessage values('200705','011','18','sxl001','13222000000');
8 insert into MobileMessage values('200706','011','21','sxl001','13222000000');
9 insert into MobileMessage values('200706','012','11','tgg001','13800044400');
10 insert into MobileMessage values('200707','010','04','ghk001','13800000000');
11 insert into MobileMessage values('200708','012','24','tgg001','13800044400');
12 insert into MobileMessage values('200709','014','29','zjj001','13100000000');
13 insert into MobileMessage values('200710','014','29','zjj001','13100000000');
14 insert into MobileMessage values('200711','014','29','zjj001','13100000000');
15 insert into MobileMessage values('200711','013','30','wgc001','13444000000');
16 insert into MobileMessage values('200712','013','30','wgc001','13444000000');
17 insert into MobileMessage values('200712','010','30','ghk001','13800000000');
18 insert into MobileMessage values('200801','015','22','myx001','13633330000');
19 end/
20 ;
21 /
begin
insert into MobileMessage values('200701','010','04','ghk001','13800000000');
insert into MobileMessage values('200702','015','12','myx001','13633330000');
insert into MobileMessage values('200703','015','24','hjd001','13300000000');
insert into MobileMessage values('200704','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','011','18','sxl001','13222000000');
insert into MobileMessage values('200706','011','21','sxl001','13222000000');
insert into MobileMessage values('200706','012','11','tgg001','13800044400');
insert into MobileMessage values('200707','010','04','ghk001','13800000000');
insert into MobileMessage values('200708','012','24','tgg001','13800044400');
insert into MobileMessage values('200709','014','29','zjj001','13100000000');
insert into MobileMessage values('200710','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','010','30','ghk001','13800000000');
insert into MobileMessage values('200801','015','22','myx001','13633330000');
end/
;
ORA-06550: 第 20 行, 第 4 列:
PLS-00103: 出现符号 "/"在需要下列之一时：
; <an identifier>
<a double-quoted delimited-identifier>
符号 "/" 被忽略。
SQL>
SQL> begin
2 insert into MobileMessage values('200701','010','04','ghk001','13800000000');
3 insert into MobileMessage values('200702','015','12','myx001','13633330000');
4 insert into MobileMessage values('200703','015','24','hjd001','13300000000');
5 insert into MobileMessage values('200704','010','04','ghk001','13800000000');
6 insert into MobileMessage values('200705','010','04','ghk001','13800000000');
7 insert into MobileMessage values('200705','011','18','sxl001','13222000000');
8 insert into MobileMessage values('200706','011','21','sxl001','13222000000');
9 insert into MobileMessage values('200706','012','11','tgg001','13800044400');
10 insert into MobileMessage values('200707','010','04','ghk001','13800000000');
11 insert into MobileMessage values('200708','012','24','tgg001','13800044400');
12 insert into MobileMessage values('200709','014','29','zjj001','13100000000');
13 insert into MobileMessage values('200710','014','29','zjj001','13100000000');
14 insert into MobileMessage values('200711','014','29','zjj001','13100000000');
15 insert into MobileMessage values('200711','013','30','wgc001','13444000000');
16 insert into MobileMessage values('200712','013','30','wgc001','13444000000');
17 insert into MobileMessage values('200712','010','30','ghk001','13800000000');
18 insert into MobileMessage values('200801','015','22','myx001','13633330000');
19 end
20 ;/
21 /
begin
insert into MobileMessage values('200701','010','04','ghk001','13800000000');
insert into MobileMessage values('200702','015','12','myx001','13633330000');
insert into MobileMessage values('200703','015','24','hjd001','13300000000');
insert into MobileMessage values('200704','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','011','18','sxl001','13222000000');
insert into MobileMessage values('200706','011','21','sxl001','13222000000');
insert into MobileMessage values('200706','012','11','tgg001','13800044400');
insert into MobileMessage values('200707','010','04','ghk001','13800000000');
insert into MobileMessage values('200708','012','24','tgg001','13800044400');
insert into MobileMessage values('200709','014','29','zjj001','13100000000');
insert into MobileMessage values('200710','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','010','30','ghk001','13800000000');
insert into MobileMessage values('200801','015','22','myx001','13633330000');
end
;/
ORA-06550: 第 21 行, 第 2 列:
PLS-00103: 出现符号 "/"符号 "/" 被忽略。
SQL>
SQL> begin
2 insert into MobileMessage values('200701','010','04','ghk001','13800000000');
3 insert into MobileMessage values('200702','015','12','myx001','13633330000');
4 insert into MobileMessage values('200703','015','24','hjd001','13300000000');
5 insert into MobileMessage values('200704','010','04','ghk001','13800000000');
6 insert into MobileMessage values('200705','010','04','ghk001','13800000000');
7 insert into MobileMessage values('200705','011','18','sxl001','13222000000');
8 insert into MobileMessage values('200706','011','21','sxl001','13222000000');
9 insert into MobileMessage values('200706','012','11','tgg001','13800044400');
10 insert into MobileMessage values('200707','010','04','ghk001','13800000000');
11 insert into MobileMessage values('200708','012','24','tgg001','13800044400');
12 insert into MobileMessage values('200709','014','29','zjj001','13100000000');
13 insert into MobileMessage values('200710','014','29','zjj001','13100000000');
14 insert into MobileMessage values('200711','014','29','zjj001','13100000000');
15 insert into MobileMessage values('200711','013','30','wgc001','13444000000');
16 insert into MobileMessage values('200712','013','30','wgc001','13444000000');
17 insert into MobileMessage values('200712','010','30','ghk001','13800000000');
18 insert into MobileMessage values('200801','015','22','myx001','13633330000');
19
20 end;
21 /
PL/SQL procedure successfully completed
SQL> select * from MobileMessage;
ACCT_MONTH AREA_NO DAY_ID SUBSCRBID SVCNUM
---------- ---------- ------ -------------------- ------------------------------
200701 010 04 ghk001 13800000000
200704 010 04 ghk001 13800000000
200705 010 04 ghk001 13800000000
200702 015 12 myx001 13633330000
200705 011 18 sxl001 13222000000
200703 015 24 hjd001 13300000000
200707 010 04 ghk001 13800000000
200706 012 11 tgg001 13800044400
200706 011 21 sxl001 13222000000
200708 012 24 tgg001 13800044400
200709 014 29 zjj001 13100000000
200710 014 29 zjj001 13100000000
200711 014 29 zjj001 13100000000
200711 013 30 wgc001 13444000000
200712 013 30 wgc001 13444000000
200712 010 30 ghk001 13800000000
200801 015 22 myx001 13633330000
17 rows selected
SQL>
1.表空间
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@ORCL
SQL> create tablespace ts1 datafile 'D:\t1.dbf' size 5m,'D:\t2.dbf' size 10m;
Tablespace created
SQL> show * from dba_data_files;
SQL> show * 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
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE
F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF 3 UNDOTBS1 99614720 12160 AVAILABLE 3 YES 3435972198 4194302 640 98566144 12032 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF 2 SYSAUX 692060160 84480 AVAILABLE 2 YES 3435972198 4194302 1280 691011584 84352 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM
F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF 5 EXAMPLE 104857600 12800 AVAILABLE 5 YES 3435972198 4194302 80 103809024 12672 ONLINE
D:\T1.DBF 6 TS1 5242880 640 AVAILABLE 6 NO 0 0 0 4194304 512 ONLINE
D:\T2.DBF 7 TS1 10485760 1280 AVAILABLE 7 NO 0 0 0 9437184 1152 ONLINE
7 rows selected
SQL> select * from dba_free_space;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSTEM 1 88176 131072 16 1
SYSTEM 1 89216 3145728 384 1
SYSAUX 2 53368 65536 8 2
SYSAUX 2 55432 65536 8 2
SYSAUX 2 55456 65536 8 2
SYSAUX 2 55472 65536 8 2
SYSAUX 2 55520 65536 8 2
SYSAUX 2 55552 65536 8 2
SYSAUX 2 55568 65536 8 2
SYSAUX 2 55584 65536 8 2
SYSAUX 2 55600 65536 8 2
SYSAUX 2 55616 65536 8 2
SYSAUX 2 55648 65536 8 2
SYSAUX 2 55664 65536 8 2
SYSAUX 2 55680 65536 8 2
SYSAUX 2 55696 65536 8 2
SYSAUX 2 55712 65536 8 2
SYSAUX 2 55728 65536 8 2
SYSAUX 2 55760 65536 8 2
SYSAUX 2 62048 196608 24 2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSAUX 2 62104 65536 8 2
SYSAUX 2 62168 196608 24 2
SYSAUX 2 62368 196608 24 2
SYSAUX 2 62432 262144 32 2
SYSAUX 2 62656 196608 24 2
SYSAUX 2 62704 65536 8 2
SYSAUX 2 62976 65536 8 2
SYSAUX 2 63040 65536 8 2
SYSAUX 2 63056 131072 16 2
SYSAUX 2 63088 196608 24 2
SYSAUX 2 63120 131072 16 2
SYSAUX 2 63312 196608 24 2
SYSAUX 2 63576 65536 8 2
SYSAUX 2 63600 131072 16 2
SYSAUX 2 63744 65536 8 2
SYSAUX 2 63760 262144 32 2
SYSAUX 2 63808 196608 24 2
SYSAUX 2 63848 65536 8 2
SYSAUX 2 63896 327680 40 2
SYSAUX 2 64056 196608 24 2
SYSAUX 2 64088 65536 8 2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSAUX 2 64120 65536 8 2
SYSAUX 2 64256 65536 8 2
SYSAUX 2 64272 65536 8 2
SYSAUX 2 64296 262144 32 2
SYSAUX 2 64352 327680 40 2
SYSAUX 2 64472 65536 8 2
SYSAUX 2 64488 131072 16 2
SYSAUX 2 64776 131072 16 2
SYSAUX 2 64808 65536 8 2
SYSAUX 2 64824 458752 56 2
SYSAUX 2 65504 196608 24 2
SYSAUX 2 65576 65536 8 2
SYSAUX 2 65608 196608 24 2
SYSAUX 2 65640 196608 24 2
SYSAUX 2 65920 196608 24 2
SYSAUX 2 65952 524288 64 2
SYSAUX 2 66496 131072 16 2
SYSAUX 2 66536 1507328 184 2
SYSAUX 2 66736 65536 8 2
SYSAUX 2 66752 65536 8 2
SYSAUX 2 66808 65536 8 2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSAUX 2 67072 327680 40 2
SYSAUX 2 67128 327680 40 2
SYSAUX 2 67176 327680 40 2
SYSAUX 2 67256 262144 32 2
SYSAUX 2 67304 1507328 184 2
SYSAUX 2 67520 65536 8 2
SYSAUX 2 67544 262144 32 2
SYSAUX 2 67592 327680 40 2
SYSAUX 2 67640 196608 24 2
SYSAUX 2 68856 327680 40 2
SYSAUX 2 79664 655360 80 2
SYSAUX 2 80000 36700160 4480 2
UNDOTBS1 3 184 65536 8 3
UNDOTBS1 3 280 458752 56 3
UNDOTBS1 3 344 65536 8 3
UNDOTBS1 3 360 196608 24 3
UNDOTBS1 3 768 1048576 128 3
UNDOTBS1 3 1536 2097152 256 3
UNDOTBS1 3 2432 2097152 256 3
UNDOTBS1 3 3456 71303168 8704 3
USERS 4 520 983040 120 4
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
EXAMPLE 5 480 1966080 240 5
EXAMPLE 5 1408 589824 72 5
EXAMPLE 5 10392 19726336 2408 5
TS1 6 128 4194304 512 6
TS1 7 128 9437184 1152 7
88 rows selected
SQL> select * from v$database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_TYPE CONTROLFILE_CREATED CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETLOGS VERSION_TIME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_ARCHIVE ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVELOG_COMPRESSION SWITCHOVER_STATUS DATAGUARD_BROKER GUARD_STATUS SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI FORCE_LOGGING PLATFORM_ID PLATFORM_NAME RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAILOVER_OBSERVER_PRESENT FS_FAILOVER_OBSERVER_HOST CONTROLFILE_CONVERTED PRIMARY_DB_UNIQUE_NAME SUPPLEMENTAL_LOG_DATA_PL MIN_REQUIRED_CAPTURE_CHANGE#
---------- --------- ----------- ----------------- -------------- ----------------------- -------------------- ------------ ------------------ --------------- ---------------- ------------------- --------------------- ------------------- ---------------- -------------- ------------ -------------------- -------------------- -------------------- -------------- ----------- ----------- ---------------- ------------------ ---------------------- -------------------- ---------------- ------------ ------------------------- ------------------------ ------------------------ ------------- ----------- -------------------------------------------------------------------------------- ---------------------------- ---------------------- ----------- ------------------ ------------------------ ------------------------- ------------------------------ -------------------------- ---------------------- ------------------------------ --------------------- ---------------------------- -------------------------------------------------------------------------------- --------------------- ------------------------------ ------------------------ ----------------------------
1529549295 ORCL 2019/3/1 13 947455 2019/3/1 13:02 1 2010/3/30 10:07:44 NOARCHIVELOG 1723611 1703277 CURRENT 2019/3/1 13:02:41 4681 1731766 2019/3/14 16:09: NOT ALLOWED 2019/3/1 13: READ WRITE MAXIMUM PERFORMANCE UNPROTECTED ENABLED 1529514479 1529514479 PRIMARY 947455 DISABLED NOT ALLOWED DISABLED NONE NO NO NO NO 12 Microsoft Windows x86 64-bit 2 2 1731912 NO NO NO orcl 0 DISABLED 0 NO NO
SQL> create tablespace ts2 datafile 'D:\t3.dbf' size 12m;
Tablespace created
2.数据字典
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
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE
F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF 3 UNDOTBS1 99614720 12160 AVAILABLE 3 YES 3435972198 4194302 640 98566144 12032 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF 2 SYSAUX 692060160 84480 AVAILABLE 2 YES 3435972198 4194302 1280 691011584 84352 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM
F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF 5 EXAMPLE 104857600 12800 AVAILABLE 5 YES 3435972198 4194302 80 103809024 12672 ONLINE
D:\T1.DBF 6 TS1 5242880 640 AVAILABLE 6 NO 0 0 0 4194304 512 ONLINE
D:\T2.DBF 7 TS1 10485760 1280 AVAILABLE 7 NO 0 0 0 9437184 1152 ONLINE
D:\T3.DBF 8 TS2 12582912 1536 AVAILABLE 8 NO 0 0 0 11534336 1408 ONLINE
8 rows selected
SQL> desc dba_data_files;
Name Type Nullable Default Comments
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME VARCHAR2(513) Y Name of the database data file
FILE_ID NUMBER Y ID of the database data file
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace to which the file belongs
BYTES NUMBER Y Size of the file in bytes
BLOCKS NUMBER Y Size of the file in ORACLE blocks
STATUS VARCHAR2(9) Y File status: "INVALID" or "AVAILABLE"
RELATIVE_FNO NUMBER Y Tablespace-relative file number
AUTOEXTENSIBLE VARCHAR2(3) Y Autoextensible indicator: "YES" or "NO"
MAXBYTES NUMBER Y Maximum size of the file in bytes
MAXBLOCKS NUMBER Y Maximum size of the file in ORACLE blocks
INCREMENT_BY NUMBER Y Default increment for autoextension
USER_BYTES NUMBER Y Size of the useful portion of file in bytes
USER_BLOCKS NUMBER Y Size of the useful portion of file in ORACLE blocks
ONLINE_STATUS VARCHAR2(7) Y Online status of the file
SQL> desc users;
Object users does not exist.
SQL> desc user;
Object user does not exist.
SQL> desc dba_user;
Object dba_user does not exist.
SQL> desc dba_users;
Name Type Nullable Default Comments
--------------------------- -------------- -------- ------- -------------------------------------------------------
USERNAME VARCHAR2(30) Name of the user
USER_ID NUMBER ID number of the user
PASSWORD VARCHAR2(30) Y Deprecated from 11.2 -- use AUTHENTICATION_TYPE instead
ACCOUNT_STATUS VARCHAR2(32)
LOCK_DATE DATE Y
EXPIRY_DATE DATE Y
DEFAULT_TABLESPACE VARCHAR2(30) Default tablespace for data
TEMPORARY_TABLESPACE VARCHAR2(30) Default tablespace for temporary tables
CREATED DATE User creation date
PROFILE VARCHAR2(30) User resource profile name
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) Y User's initial consumer group
EXTERNAL_NAME VARCHAR2(4000) Y User external name
PASSWORD_VERSIONS VARCHAR2(8) Y Versions of encrypted passwords
EDITIONS_ENABLED VARCHAR2(1) Y Whether editions are enabled for this user
AUTHENTICATION_TYPE VARCHAR2(8) Y
SQL> alter tablespace add logfile group 2 ('log1c.rdo','log2c.rdo')size 1m;
alter tablespace add logfile group 2 ('log1c.rdo','log2c.rdo')size 1m
ORA-02140: invalid tablespace name
SQL> alter database add logfile group 2 ('log1c.ora','log2c.ora') size 1m;
alter database add logfile group 2 ('log1c.ora','log2c.ora') size 1m
ORA-01184: logfile group 2 already exists
SQL> alter database add logfile group 10 ('log1c.ora','log2c.ora') size 1m;
alter database add logfile group 10 ('log1c.ora','log2c.ora') size 1m
ORA-01184: logfile group 10 already exists
SQL> alter database add logfile group 12 ('log1c.ora','log2c.ora') size 10m;
alter database add logfile group 12 ('log1c.ora','log2c.ora') size 1m
ORA-00336: log file size 2048 blocks is less than minimum 8192 blocks
SQL> alter database add logfile group 12 ('log1c.ora','log2c.ora') size 10m;
Database altered
SQL> alter database add logfile member 'log3c.rdo' to group 12;
Database altered
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
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE
F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF 3 UNDOTBS1 99614720 12160 AVAILABLE 3 YES 3435972198 4194302 640 98566144 12032 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF 2 SYSAUX 692060160 84480 AVAILABLE 2 YES 3435972198 4194302 1280 691011584 84352 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM
F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF 5 EXAMPLE 104857600 12800 AVAILABLE 5 YES 3435972198 4194302 80 103809024 12672 ONLINE
D:\T1.DBF 6 TS1 5242880 640 AVAILABLE 6 NO 0 0 0 4194304 512 ONLINE
D:\T2.DBF 7 TS1 10485760 1280 AVAILABLE 7 NO 0 0 0 9437184 1152 ONLINE
D:\T3.DBF 8 TS2 12582912 1536 AVAILABLE 8 NO 0 0 0 11534336 1408 ONLINE
8 rows selected
3.日志组
SQL> select group,status,member from V$LOGFILE;
select group,status,member from V$LOGFILE
ORA-00936: missing expression
SQL> select group# status,member from V$LOGFILE;
STATUS MEMBER
---------- --------------------------------------------------------------------------------
3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
10 D:\L1.LOG
10 D:\L2.LOG
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
8 rows selected
SQL> select group#,member,status from V$LOG;
select group#,member,status from V$LOG
ORA-00904: "MEMBER": invalid identifier
SQL> SELECT GROUP# ARCHIVED,STATUS FROM V$LOG;
ARCHIVED STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
10 INACTIVE
12 UNUSED
SQL> ALTER DATABASE ADD LOGFILE GROUP 13('log6c.rdo','log7c.rdo') size 500k;
ALTER DATABASE ADD LOGFILE GROUP 13('log6c.rdo','log7c.rdo') size 500k
ORA-00336: log file size 1000 blocks is less than minimum 8192 blocks
SQL> ALTER DATABASE ADD LOGFILE GROUP 13('log6c.rdo','log7c.rdo') size 5m;
Database altered
SQL> select group#,member from V$LOGFILE;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
10 D:\L1.LOG
10 D:\L2.LOG
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG6C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG7C.RDO
10 rows selected
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'LOG8.C' TO GROUP 13;
Database altered
SQL> select group#,member from V$LOGFILE;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
10 D:\L1.LOG
10 D:\L2.LOG
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG6C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG7C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG8.C
11 rows selected
SQL> ALTER DATABASE DROP LOGFILE MEMBER 'LOG8.C';
Database altered
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'LOG8C.RDO' TO GROUP 13;
Database altered
SQL> SELECT GROUP#,MEMBER FROM V$LOGFILE;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
10 D:\L1.LOG
10 D:\L2.LOG
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG6C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG7C.RDO
13 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG8C.RDO
11 rows selected
SQL> ALTER DATABASE DROP LOGFILE GROUP 13;
Database altered
SQL> SELECT GROPU#,MEMBER,STATUS FROM V$LOGFILE;
SELECT GROPU#,MEMBER,STATUS FROM V$LOGFILE
ORA-00904: "GROPU#": invalid identifier
SQL> SELECT GRUP#,MEMBER,STATUS FROM V$LOGFILE;
SELECT GRUP#,MEMBER,STATUS FROM V$LOGFILE
ORA-00904: "GRUP#": invalid identifier
SQL> SELECT GROUP#,MEMBER,STATUS FROM V$LOGFILE;
GROUP# MEMBER STATUS
---------- -------------------------------------------------------------------------------- -------
3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
10 D:\L1.LOG
10 D:\L2.LOG
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO INVALID
8 rows selected
4.存档
SQL> ARCHIVE LOG LIST;
ARCHIVE LOG LIST
ORA-00900: invalid SQL statement
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG NO
2 ONLINE F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG NO
1 ONLINE F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG NO
10 ONLINE D:\L1.LOG NO
10 ONLINE D:\L2.LOG NO
12 ONLINE F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA NO
12 ONLINE F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA NO
12 INVALID ONLINE F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO NO
8 rows selected
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 FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_TIME PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- -------------------- ------------ ------------------------ --------------------- ---------------- -------------- ------------------------ ---------------------
1 7 2010/3/30 10: 0 1 SYSTEM READ WRITE 1723611 2019/3/14 14:00 0 947454 947455 2019/3/1 13 734003200 89600 0 8192 F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0 0 0 NO 0 0
2 2160 2010/3/30 10: 1 2 ONLINE READ WRITE 1723611 2019/3/14 14:00 0 947454 947455 2019/3/1 13 692060160 84480 0 8192 F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0 0 0 NO 0 0
3 944668 2010/3/30 11: 2 3 ONLINE READ WRITE 1723611 2019/3/14 14:00 0 947454 947455 2019/3/1 13 99614720 12160 0 8192 F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0 0 0 NO 0 0
4 17981 2010/3/30 10: 4 4 ONLINE READ WRITE 1723611 2019/3/14 14:00 0 947454 947455 2019/3/1 13 5242880 640 0 8192 F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0 0 0 NO 0 0
5 976600 2019/3/1 13:0 6 5 ONLINE READ WRITE 1723611 2019/3/14 14:00 0 976600 976605 2019/3/1 13 104857600 12800 104857600 8192 F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF 0 8192 NONE 0 4043674800 948026 2010/3/30 11:10:01 NO 968202 947455 2019/3/1 13:02:42
6 1730315 2019/3/14 15: 7 6 ONLINE READ WRITE 1730320 2019/3/14 15:59 0 0 0 5242880 640 5242880 8192 D:\T1.DBF 0 8192 NONE 0 0 0 NO 0 0
7 1730319 2019/3/14 15: 7 7 ONLINE READ WRITE 1730320 2019/3/14 15:59 0 0 0 10485760 1280 10485760 8192 D:\T2.DBF 0 8192 NONE 0 0 0 NO 0 0
8 1731995 2019/3/14 16: 8 8 ONLINE READ WRITE 1731996 2019/3/14 16:13 0 0 0 12582912 1536 12582912 8192 D:\T3.DBF 0 8192 NONE 0 0 0 NO 0 0
8 rows selected
SQL> SELECT * FROM V$CONTROLFILE;
STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------------------------------------- --------------------- ---------- --------------
F:\APP\ASUS\ORADATA\ORCL\CONTROL01.CTL NO 16384 594
F:\APP\ASUS\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL NO 16384 594
SQL>
查询表空间信息
select * from v$tablespace;
TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 TS1 YES NO YES
8 TS2 YES NO YES
8 rows selected
create tablespace orac datafile 'D:\ok.dbf' size 10 m extent management local autoallocate;
Tablespace created
SQL> select * fron V$TABLESPACE;
select * fron V$TABLESPACE
ORA-00923: FROM keyword not found where expected
SQL> select * froM V$TABLESPACE;
TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 TS1 YES NO YES
8 TS2 YES NO YES
9 ORAC YES NO YES
9 rows selected
SQL> CREATE BIGFILE TABLESPACE BIGTBS DATAFILE 'D:\OK2.DBF' SIZE 20M;
Tablespace created
SQL> SHOW PARAMETER DB_LOCK_SIZE;
SQL> SHOW PARAMETER DB_BLOCK_SIZE;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> //查询所有表空间大小
SQL> SELECT TABLESPACE_NAME AS PRE,SUM(BYTES) FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
PRE SUM(BYTES)
------------------------------ ----------
UNDOTBS1 99614720
SYSAUX 692060160
TS2 12582912
USERS 5242880
SYSTEM 734003200
EXAMPLE 104857600
ORAC 10485760
BIGTBS 20971520
TS1 15728640
9 rows selected
SQL>
CREATE TABLESPACE NUMA DATAFILE 'C:\CHO1.DBF' SIZE 10000K;
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\ORCL1\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 2 UNDOTBS1 62914560 7680 AVAILABLE 2 YES 3435972198 4194302 640 62849024 7672 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\CHO1.DBF 5 NUMA 10240000 1250 AVAILABLE 5 NO 0 0 0 10158080 1240 ONLINE
SQL> alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
Database altered
已有表空间：
USERS
SYSAUX
UNDOTBS1
SYSTEM
NUMA 自己建立的表空间NUMA
ALTER DATABASE ADD LOGFILE GROUP 10('C:\L1.LOG','C:\L2.LOG') SIZE 5 M;
alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
alter database add logfile GROUP 12('C:A1.LOG','C:\A2.LOG') SIZE 10M;
alter database drop logfile group 12;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SYS
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
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\ORCL1\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 2 UNDOTBS1 62914560 7680 AVAILABLE 2 YES 3435972198 4194302 640 62849024 7672 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\CHO1.DBF 5 NUMA 10240000 1250 AVAILABLE 5 NO 0 0 0 10158080 1240 ONLINE
SQL> alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
Database altered
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
SQL> alter database add logfile GROUP 10('C:A1.LOG','C:\A2.LOG') SIZE 10M;
alter database add logfile GROUP 10('C:A1.LOG','C:\A2.LOG') SIZE 10M
ORA-01184: 日志文件组 10 已存在
SQL> alter database add logfile GROUP 12('C:A1.LOG','C:\A2.LOG') SIZE 10M;
Database altered
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL1 NOARCHIVELOG
archive log list;
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 3月 7 16:12:01 2019
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
请输入用户名: SYS
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> ARCHIVE LOG LIST
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;
NAME LOG_MODE
--------- ------------
ORCL1 NOARCHIVELOG
SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
数据库已更改。
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG
NO
2 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG
NO
10 ONLINE
C:\L1.LOG
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
NO
10 ONLINE
C:\L2.LOG
NO
10 INVALID ONLINE
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
C:\L3.LOG
NO
已选择6行。
SQL> archive log list;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
CREATE TABLESPACE NUMA DATAFILE 'C:\CHO1.DBF' SIZE 10000K;
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\ORCL1\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 2 UNDOTBS1 62914560 7680 AVAILABLE 2 YES 3435972198 4194302 640 62849024 7672 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\CHO1.DBF 5 NUMA 10240000 1250 AVAILABLE 5 NO 0 0 0 10158080 1240 ONLINE
SQL> alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
Database altered
已有表空间：
USERS
SYSAUX
UNDOTBS1
SYSTEM
NUMA 自己建立的表空间NUMA
ALTER DATABASE ADD LOGFILE GROUP 10('C:\L1.LOG','C:\L2.LOG') SIZE 5 M;
alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
alter database add logfile GROUP 12('C:A1.LOG','C:\A2.LOG') SIZE 10M;
alter database drop logfile group 12;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SYS
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
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\ORCL1\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 2 UNDOTBS1 62914560 7680 AVAILABLE 2 YES 3435972198 4194302 640 62849024 7672 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\CHO1.DBF 5 NUMA 10240000 1250 AVAILABLE 5 NO 0 0 0 10158080 1240 ONLINE
SQL> alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
Database altered
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
SQL> alter database add logfile GROUP 10('C:A1.LOG','C:\A2.LOG') SIZE 10M;
alter database add logfile GROUP 10('C:A1.LOG','C:\A2.LOG') SIZE 10M
ORA-01184: 日志文件组 10 已存在
SQL> alter database add logfile GROUP 12('C:A1.LOG','C:\A2.LOG') SIZE 10M;
Database altered
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL1 NOARCHIVELOG
archive log list;
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 3月 7 16:12:01 2019
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
请输入用户名: SYS
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> ARCHIVE LOG LIST
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;
NAME LOG_MODE
--------- ------------
ORCL1 NOARCHIVELOG
SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
数据库已更改。
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG
NO
2 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG
NO
10 ONLINE
C:\L1.LOG
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
NO
10 ONLINE
C:\L2.LOG
NO
10 INVALID ONLINE
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
C:\L3.LOG
NO
已选择6行。
SQL> archive log list;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
SQL>
CREATE TABLESPACE NUMA DATAFILE 'C:\CHO1.DBF' SIZE 10000K;
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\ORCL1\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 2 UNDOTBS1 62914560 7680 AVAILABLE 2 YES 3435972198 4194302 640 62849024 7672 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\CHO1.DBF 5 NUMA 10240000 1250 AVAILABLE 5 NO 0 0 0 10158080 1240 ONLINE
SQL> alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
Database altered
已有表空间：
USERS
SYSAUX
UNDOTBS1
SYSTEM
NUMA 自己建立的表空间NUMA
ALTER DATABASE ADD LOGFILE GROUP 10('C:\L1.LOG','C:\L2.LOG') SIZE 5 M;
alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
alter database add logfile GROUP 12('C:A1.LOG','C:\A2.LOG') SIZE 10M;
alter database drop logfile group 12;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SYS
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
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\ORCL1\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 2 UNDOTBS1 62914560 7680 AVAILABLE 2 YES 3435972198 4194302 640 62849024 7672 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\CHO1.DBF 5 NUMA 10240000 1250 AVAILABLE 5 NO 0 0 0 10158080 1240 ONLINE
SQL> alter database add logfile member 'C:\L3.LOG' TO GROUP 10;
Database altered
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
SQL> alter database add logfile GROUP 10('C:A1.LOG','C:\A2.LOG') SIZE 10M;
alter database add logfile GROUP 10('C:A1.LOG','C:\A2.LOG') SIZE 10M
ORA-01184: 日志文件组 10 已存在
SQL> alter database add logfile GROUP 12('C:A1.LOG','C:\A2.LOG') SIZE 10M;
Database altered
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
10 INVALID ONLINE C:\L3.LOG NO
6 rows selected
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL1 NOARCHIVELOG
archive log list;
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 3月 7 16:12:01 2019
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
请输入用户名: SYS
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> ARCHIVE LOG LIST
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;
NAME LOG_MODE
--------- ------------
ORCL1 NOARCHIVELOG
SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
数据库已更改。
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG
NO
2 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG
NO
10 ONLINE
C:\L1.LOG
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
NO
10 ONLINE
C:\L2.LOG
NO
10 INVALID ONLINE
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
C:\L3.LOG
NO
已选择6行。
SQL> archive log list;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
SQL>
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 3月 7 16:12:01 2019
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
请输入用户名: SYS
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> ARCHIVE LOG LIST
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;
NAME LOG_MODE
--------- ------------
ORCL1 NOARCHIVELOG
SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
数据库已更改。
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG
NO
2 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 STALE ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG
NO
10 ONLINE
C:\L1.LOG
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
NO
10 ONLINE
C:\L2.LOG
NO
10 INVALID ONLINE
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
C:\L3.LOG
NO
已选择6行。
SQL> archive log list;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 50
当前日志序列 52
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 1258291200 bytes
Fixed Size 1292156 bytes
Variable Size 318769284 bytes
Database Buffers 931135488 bytes
Redo Buffers 7094272 bytes
SQL> alter database mount
2 ;
数据库已更改。
SQL> alter database archivelog;
数据库已更改。
SQL>
一、表空间下建立数据文件
SQL> create tablespace t1
2 datafile 'c:\t01.dbf' size 5M
3 ;
Tablespace created
SQL> create tablespace t2
2 datafile 'c:\t02.dbf' size 3M;
Tablespace created
SQL>
SQL> create tablespace t3
2 datafile 'c:\t05.dbf' 'c:\t04.dbf' size 5M
3
SQL> ;
SQL>
SQL> create tablespace t3
2 datafile 'c:\t05.dbf' size 2M
3
SQL> ;
SQL> ;
SQL>
SQL> create tablespace t3
2 datafile 'c:\t05.dbf' size 2M
3 ;
Tablespace created
SQL>
SQL> create tablespace t4
2 datafile 'c:\t05.dbf' 'c:\t06.dbf' size 2M
3 ;
create tablespace t4
datafile 'c:\t05.dbf' 'c:\t06.dbf' size 2M
ORA-02180: 无效的 CREATE TABLESPACE 选项
SQL>
SQL> create tablespace t4
2 datafile 'c:\t05.dbf','c:\t06.dbf' size 2M
3
SQL>
SQL> create tablespace t4
2 datafile 'c:\t05.dbf','c:\t06.dbf' size 2M
3 ;
create tablespace t4
datafile 'c:\t05.dbf','c:\t06.dbf' size 2M
ORA-01537: 无法添加文件 'c:\t05.dbf' - 该文件已是数据库的一部分
SQL>
SQL> create tablespace t4
2 datafile 'c:\t06.dbf','c:\t07.dbf' size 2M;
create tablespace t4
datafile 'c:\t06.dbf'size 3M,'c:\t07.dbf' size 2M
ORA-01119: 创建数据库文件 'c:\t06.dbf' 时出错
ORA-17610: 文件 'c:\t06.dbf' 不存在, 大小也未指定
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
SQL>
SQL> create tablespace t4
2 datafile 'c:\t06.dbf'size 3M,'c:\t07.dbf' size 2M
3 ;
Tablespace created
二、数据字典显示和描述
SQL> SELECT*FROM DBA_DATA-FILES
2 ;
SELECT*FROM DBA_DATA-FILES
ORA-00933: SQL 命令未正确结束
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\ORCL1\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 2 UNDOTBS1 62914560 7680 AVAILABLE 2 YES 3435972198 4194302 640 62849024 7672 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\T01.DBF 5 T1 5242880 640 AVAILABLE 5 NO 0 0 0 5177344 632 ONLINE
C:\T02.DBF 6 T2 3145728 384 AVAILABLE 6 NO 0 0 0 3080192 376 ONLINE
C:\T05.DBF 7 T3 2097152 256 AVAILABLE 7 NO 0 0 0 2031616 248 ONLINE
C:\T06.DBF 8 T4 3145728 384 AVAILABLE 8 NO 0 0 0 3080192 376 ONLINE
C:\T07.DBF 9 T4 2097152 256 AVAILABLE 9 NO 0 0 0 2031616 248 ONLINE
9 rows selected
SQL>
SQL> create tablespace t4
2 datafile 'c:\t06.dbf'size 3M,'c:\t07.dbf' size 2M
3 ;
create tablespace t4
datafile 'c:\t06.dbf'size 3M,'c:\t07.dbf' size 2M
ORA-01543: 表空间 'T4' 已存在
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 3:3 0 1 SYSTEM READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 503316480 61440 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 0 8192 NONE 0
2 519918 2007/4/17 6:0 1 2 ONLINE READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 62914560 7680 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 0 8192 NONE 0
3 5554 2007/4/17 3:3 2 3 ONLINE READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 272629760 33280 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 0 8192 NONE 0
4 9202 2007/4/17 3:3 4 4 ONLINE READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 5242880 640 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF 0 8192 NONE 0
5 1577575 2018/8/12 16: 6 5 ONLINE READ WRITE 1577576 2018/8/12 16:14 0 0 0 5242880 640 5242880 8192 C:\T01.DBF 0 8192 NONE 0
6 1577656 2018/8/12 16: 7 6 ONLINE READ WRITE 1577657 2018/8/12 16:17 0 0 0 3145728 384 3145728 8192 C:\T02.DBF 0 8192 NONE 0
7 1577752 2018/8/12 16: 8 7 ONLINE READ WRITE 1577753 2018/8/12 16:20 0 0 0 2097152 256 2097152 8192 C:\T05.DBF 0 8192 NONE 0
8 1577989 2018/8/12 16: 9 8 ONLINE READ WRITE 1577993 2018/8/12 16:23 0 0 0 3145728 384 3145728 8192 C:\T06.DBF 0 8192 NONE 0
9 1577992 2018/8/12 16: 9 9 ONLINE READ WRITE 1577993 2018/8/12 16:23 0 0 0 2097152 256 2097152 8192 C:\T07.DBF 0 8192 NONE 0
9 rows selected
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 3:3 0 1 SYSTEM READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 503316480 61440 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 0 8192 NONE 0
2 519918 2007/4/17 6:0 1 2 ONLINE READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 62914560 7680 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 0 8192 NONE 0
3 5554 2007/4/17 3:3 2 3 ONLINE READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 272629760 33280 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 0 8192 NONE 0
4 9202 2007/4/17 3:3 4 4 ONLINE READ WRITE 1576471 2018/8/11 15:38 0 521802 521803 2018/8/23 9 5242880 640 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF 0 8192 NONE 0
5 1577575 2018/8/12 16: 6 5 ONLINE READ WRITE 1577576 2018/8/12 16:14 0 0 0 5242880 640 5242880 8192 C:\T01.DBF 0 8192 NONE 0
6 1577656 2018/8/12 16: 7 6 ONLINE READ WRITE 1577657 2018/8/12 16:17 0 0 0 3145728 384 3145728 8192 C:\T02.DBF 0 8192 NONE 0
7 1577752 2018/8/12 16: 8 7 ONLINE READ WRITE 1577753 2018/8/12 16:20 0 0 0 2097152 256 2097152 8192 C:\T05.DBF 0 8192 NONE 0
8 1577989 2018/8/12 16: 9 8 ONLINE READ WRITE 1577993 2018/8/12 16:23 0 0 0 3145728 384 3145728 8192 C:\T06.DBF 0 8192 NONE 0
9 1577992 2018/8/12 16: 9 9 ONLINE READ WRITE 1577993 2018/8/12 16:23 0 0 0 2097152 256 2097152 8192 C:\T07.DBF 0 8192 NONE 0
9 rows selected
SQL> desc dba_date_files;
Object dba_date_files does not exist.
SQL> desc dba_data_files;
Name Type Nullable Default Comments
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME VARCHAR2(513) Y Name of the database data file
FILE_ID NUMBER Y ID of the database data file
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace to which the file belongs
BYTES NUMBER Y Size of the file in bytes
BLOCKS NUMBER Y Size of the file in ORACLE blocks
STATUS VARCHAR2(9) Y File status: "INVALID" or "AVAILABLE"
RELATIVE_FNO NUMBER Y Tablespace-relative file number
AUTOEXTENSIBLE VARCHAR2(3) Y Autoextensible indicator: "YES" or "NO"
MAXBYTES NUMBER Y Maximum size of the file in bytes
MAXBLOCKS NUMBER Y Maximum size of the file in ORACLE blocks
INCREMENT_BY NUMBER Y Default increment for autoextension
USER_BYTES NUMBER Y Size of the useful portion of file in bytes
USER_BLOCKS NUMBER Y Size of the useful portion of file in ORACLE blocks
ONLINE_STATUS VARCHAR2(7) Y Online status of the file
SQL> desc dba_data_files;
Name Type Nullable Default Comments
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME VARCHAR2(513) Y Name of the database data file
FILE_ID NUMBER Y ID of the database data file
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace to which the file belongs
BYTES NUMBER Y Size of the file in bytes
BLOCKS NUMBER Y Size of the file in ORACLE blocks
STATUS VARCHAR2(9) Y File status: "INVALID" or "AVAILABLE"
RELATIVE_FNO NUMBER Y Tablespace-relative file number
AUTOEXTENSIBLE VARCHAR2(3) Y Autoextensible indicator: "YES" or "NO"
MAXBYTES NUMBER Y Maximum size of the file in bytes
MAXBLOCKS NUMBER Y Maximum size of the file in ORACLE blocks
INCREMENT_BY NUMBER Y Default increment for autoextension
USER_BYTES NUMBER Y Size of the useful portion of file in bytes
USER_BLOCKS NUMBER Y Size of the useful portion of file in ORACLE blocks
ONLINE_STATUS VARCHAR2(7) Y Online status of the file
SQL> dba_users;
dba_users
ORA-00900: 无效 SQL 语句
SQL> desc dba_users;
Name Type Nullable Default Comments
--------------------------- -------------- -------- ------- ---------------------------------------
USERNAME VARCHAR2(30) Name of the user
USER_ID NUMBER ID number of the user
PASSWORD VARCHAR2(30) Y Encrypted password
ACCOUNT_STATUS VARCHAR2(32)
LOCK_DATE DATE Y
EXPIRY_DATE DATE Y
DEFAULT_TABLESPACE VARCHAR2(30) Default tablespace for data
TEMPORARY_TABLESPACE VARCHAR2(30) Default tablespace for temporary tables
CREATED DATE User creation date
PROFILE VARCHAR2(30) User resource profile name
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) Y User's initial consumer group
EXTERNAL_NAME VARCHAR2(4000) Y User external name
三、日志文件添加和按组追加，注意group,member,member下不能有size
SQL> group 2('c:\L1.log','c:\L2.log') size 5M;
group 2('c:\L1.log','c:\L2.log') size 5M
ORA-00900: 无效 SQL 语句
SQL>
SQL> alter database add logfile
2 group 2('c:\L1.log','c:\L2.log') size 5M;
alter database add logfile
group 2('c:\L1.log','c:\L2.log') size 5M
ORA-01184: 日志文件组 2 已存在
SQL>
SQL> alter database add logfile
2 group 10('c:\L1.log','c:\L2.log') size 5M;
Database altered
SQL> select*from V$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
SQL> group 11 'c:\L3.log','c:\L4.log' size 3M;
group 11 'c:\L3.log','c:\L4.log' size 3M
ORA-00900: 无效 SQL 语句
SQL>
SQL> alter database add logfile
2 group 11 'c:\L3.log','c:\L4.log' size 3M;
alter database add logfile
group 11 'c:\L3.log','c:\L4.log' size 3M
ORA-00301: 添加日志文件 'c:\L3.log' 时出错 - 无法创建文件
ORA-17610: 文件 'c:\L3.log' 不存在, 大小也未指定
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
SQL>
SQL> alter database add logfile
2 group 11 ('c:\L3.log','c:\L4.log') size 3M;
alter database add logfile
group 11 ('c:\L3.log','c:\L4.log') size 3M
ORA-00336: 大小为 6144 的日志文件块数小于最小 8192 块数
SQL>
SQL> alter database add logfile
2 group 11 ('c:\L3.log','c:\L4.log') size 5M;
Database altered
SQL>
SQL> alter database add logfile memeber'c:\L5.log'to group 10;
alter database add logfile memeber'c:\L5.log'to group 10
ORA-00933: SQL 命令未正确结束
SQL> alter database add logfile member'c:\L5.log'to group 10;
Database altered
SQL> alter database add logfile member 'c:\L6.log' to group 10;
Database altered
SQL> select*from V$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
11 ONLINE C:\L3.LOG NO
11 ONLINE C:\L4.LOG NO
10 INVALID ONLINE C:\L5.LOG NO
10 INVALID ONLINE C:\L6.LOG NO
9 rows selected
SQL> alter database drop logfile member 'c:\L6.log' to group10;
alter database drop logfile member 'c:\L6.log' to group10
ORA-00933: SQL 命令未正确结束
SQL> alter database drop logfile member 'c:\L6.log' from group10;
alter database drop logfile member 'c:\L6.log' from group10
ORA-00933: SQL 命令未正确结束
SQL> alter database drop logfile member 'c:\L6.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\ORCL1\REDO03.LOG NO
2 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG NO
1 STALE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG NO
10 ONLINE C:\L1.LOG NO
10 ONLINE C:\L2.LOG NO
11 ONLINE C:\L3.LOG NO
11 ONLINE C:\L4.LOG NO
10 INVALID ONLINE C:\L5.LOG NO
8 rows selected
SQL> alter database drop group11;
alter database drop group11
ORA-01900: 需要 LOGFILE 关键字
SQL> alter database drop logfile group11;
alter database drop logfile group11
ORA-02236: 文件名无效
SQL> alter database drop logfile member 'c:\L5.log' to group 10;
alter database drop logfile member 'c:\L5.log' to group 10
ORA-00933: SQL 命令未正确结束
SQL> alter database drop logfile group 11;
Database altered
SQL> alter database add logfile member 'c:\L6.log' from group 10;
alter database add logfile member 'c:\L6.log' from group 10
ORA-00946: 缺失 TO 关键字
四、修改归档方式
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>sqlplus system/test
SQL*Plus: Release 10.2.0.3.0 - Production on 星期日 8月 12 17:09:56 2018
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> con system/test as sysdba;
SP2-0734: 未知的命令开头 "con system..." - 忽略了剩余的行。
SQL> conn syatem/test as SYSDBA;
已连接。
SQL> show user;
USER 为 "SYS"
SQL> ARCHIVE LOG LIST;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 47
当前日志序列 49
SQL> ARCHIVE LOG LIST;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 47
当前日志序列 49
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 1258291200 bytes
Fixed Size 1292156 bytes
Variable Size 318769284 bytes
Database Buffers 931135488 bytes
Redo Buffers 7094272 bytes
SQL> alter database mount;
数据库已更改。
SQL> alter database archivelog;
数据库已更改。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 47
下一个存档日志序列 49
当前日志序列 49
SQL> alter database open;
数据库已更改。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 47
下一个存档日志序列 49
当前日志序列 49
SQL>create table STU(
Sno varchar2(10),
Sname varchar2(10),
Ssex varchar2(10),
Sage varchar2(10),
Sdept varchar2(10));
insert into STU values('20171789','李勇','男','20','cs');
insert into STU values('20171788','王勇','男','21','ch');
insert into STU values('20171778','李红','女','21','ch');
create table Cou(
Cno varchar(10),
Cname varchar(10),
Cpno varchar(10),
Ccredit varchar(10));
insert into Cou values( '1','数据库','5','4');
insert into Cou( Cno,Cname,Cpno, Ccredit)
values
('3','历史','2','5');
insert into Cou( Cno,Cname,Cpno, Ccredit)
values
('4','英语','2','5');
create table Sc(
sno varchar(10),
cno varchar(10),
grade varchar(10));
insert into sc values('20171789','1','92');
insert into sc values
('20171778','3','88');
insert into sc values
('20171788','3','66');
扫一扫
1759
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
