Oracle基础

数据库和实例
表,视图和索引

emca -repos drop
emca -repos create
emca -config dbcontrol db

emctl status dbconsole
set ORACLE_SID=test
emctl status dbconsole

emctl start
emctl stop

---------------
.ora 参数文件
init.ora 指明了控制文件的位置.

.ctl 控制文件
多路控制, 里面包括了数据文件和日志文件的路径.

.dbf 数据文件

.log 重做日志文件.
至少两个.

归档日志.

sqlplus /nolog
conn sys/abc123 as sysdba
quit
desc v$controlfile;
desc v$datafile;
desc v$logfile;
当前用户可操作的表, 不同的用户看到的是不一样的.
desc user_tables;
set pause on
set pagesize 30
// 所有的数据库对象都在这个里面.
desc user_objects;
sqlplus sys/abc123 as sysdba

startup nomount;
alter database mount;
alter database open;
-------------------
Oracle 监听器

数据库 文件柜
表空间 抽屉
段 文件夹
区(盘区) 本子
块  纸
一个数据文件只可能属于一个表空间.
一个表空间可能有多个数据文件.
--------------
内存区域
系统全局区(SGA)
数据块缓冲区,
重做日志缓冲区
共享池:库缓冲区/sql语句, 数据字典
大型池
------------------
DDL, DML, TTL, DCL
Character:
Char
varchar2
Long
Number:
Number(p,s)
Integer
Date:
Date
TimeStamp
Raw and LogRaw:
LOB:
clob, blob
-----------------------
伪列:
rowid, rownum, currval, nextval, level
rowid ->
rownum -> 比top有更强的功能
currval,nextval主要用于序列,类似identity

create sequence userseq;
select userseq.nextval from dual;
select userseq.currval from dual;
不要对dual表进行增删改, 也不可drop, 会导致数据库起不起来.
insert into test(userid, username) values(userseq.nextval, 'abc');
drop sequence userseq;

create sequence userseq
start with 1
increment by 1
minvalue 1
maxvalue 10000
order;

alter sequence userseq increment by -11;

select rowid, rownum, userid, username from test;
select * from test where rownum<=5;
select * from test where rownum < 10 and userid not in (select userid from test where rownum<=5);
-- 不能分页
select * from (select rownum as no, userid, username from test where rownum<=10) where no >5;

select * from (select rownum as no, userid, username from test where rownum<=&end) where no >&begin

? set -- 查询
set verify off
@c:/1.txt
spool c:/2.txt;
spool off;
set echo off;
set feedback off;
select * from test where userid=&&userid;
define;
undefine userid;
define userid=5;
accept userid number format 999 prompt 'Please input userid:'
accept ename char format A10 prompt 'aa';
accept d date format'yyyy-MM-dd' prompt 'bb:';
column ename heading '编号' format A5;
column ename heading '编号' format A5 null '无';
column ename clear;
TTITLE '数据报表';
BTITLE '报表结束';
set linesize 120;

cd %ORACLE_HOME%/oc4j/j2ee/isqlplus/application-deployments/isqlplus

java -Djava.security.properties=%ORACLE_HOME%/sqlplus/admin/iplus/provider -jar %ORACLE_HOME%/oc4j/j2ee/home/jazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell 

listusers
setpasswd "iSQL*Plus DBA" admin welcome abc123
grantrole webDba "iSQL*Plus DBA" admin