主流数据库
1． Access
Access是由微软发布的关联式数据库管理系统，1994年推出。它结合了 Microsoft Jet Database Engine 和图形用户界面两项特点。并且具有界面友好、易学易用、开发简单、接口灵活等特点，是一个典型的新一代桌面数据库管理系统。另外，Access还是c语言的一个函数名和一种交换机的主干道模式。
2． MySQL
MySQL是一个瑞典MySQLAB公司开发的小型关系型数据库管理系统，2008年被Sun公司收购。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
3． Oracle
Oracle是世界领先的信息管理软件开发商，因其复杂的关系数据库产品而闻名。Oracle数据库产品为财富排行榜上的前1000家公司所采用，许多大型网站也选用了Oracle系统。Oracle的目标定位于高端工作站以及作为服务器的小型计算机。Oracle的关系数据库是世界第一个支持SQL语言的数据库。
4． SQL Server
SQL(Structured Query Language)，结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。SQL语句可执行各种各样的操作。绝大多数流行的关系型数据库管理系统都采用SQL语言标准。虽然很多数据库对SQL语句进行再开发和扩展，但是包括Select, Insert, Update, Delete, Create,以及Drop在内的标准的SQL命令仍可用于完成几乎所有的数据库操作。
5． DB2
DB2是IBM研制的一种关系型数据库管理系统。DB2主要应用于大型应用系统，具有较好的可伸缩性，可支持从大型机到单用户环境，应用于OS/2、Windows等平台下。它以拥有一个非常完备的查询优化器而著称。DB2具有很好的网络支持能力，每个子系统可连接十几万个分布式用户，同时激活上千个活动线程，对大型分布式应用系统尤为适用。
6． FoxPro
Visual FoxPro原名FoxBase，最初是由美国Fox Software公司于1988年推出的数据库产品，在DOS上运行，与xBase系列兼容。1992年被Microsoft收购。相继推出了FoxPro2.5、2.6和VisualFoxPro等版本。FoxPro比FoxBASE在功能和性能上又有了很大的改进，主要是引入了窗口、按钮、列表框和文本框等控件，进一步提高了系统的开发能力。
7． Informix
Informix是IBM公司出品的关系数据库管理系统（RDBMS）家族。成立于1990年，其目的是为Unix等开放操作系统提供专业的关系型数据库。InformixSE是在当时的微机Unix环境下主要的数据库产品。它也是第一个被移植到Linux上的商业数据库产品。
Oracle笔记 五、创建表、约束、视图、索引、序列、同义词、表空间
alter table userInfo add(msn varchar2(20));
1、建表
create table userInfo (
id number(6),
name varchar2(20),
sex number(1),
age number(3),
birthday date,
address varchar2(50),
email varchar2(25),
tel number(11)
);
2、创建约束
不带约束名称的：
create table userInfo (
id number(6) primary key,--主键
name varchar2(20) not null,--非空
sex number(1),
age number(3) default 18,
birthday date,
address varchar2(50),
email varchar2(25) unique,--唯一
tel number(11),
deptno number(2) references dept(deptno)—外键
);
带约束名称：
create table userInfo (
id number(6) constraint id_pk primary key,
name varchar2(20) constraint name_nn not null,
sex number(1),
age number(3) default 18,
birthday date,
address varchar2(50),
email varchar2(25) constraint email_uqe unique,
tel number(11),
deptno number(2) constraint dept_deptno_ref references dept(deptno)
);
列模式：
create table userInfo (
id number(6),
name varchar2(20),
sex number(1),
age number(3) default 18,
birthday date,
address varchar2(50),
email varchar2(25),
tel number(11),
deptno number(2),
constraint id_pk primary key (id),--也可以两个以上，联合主键
constraint dept_deptno_ref foreign key (deptno) references dept(deptno),
constraint emial_name_uqe unique (email, name)
);
Alter模式：
alter table userInfo add(msn varchar2(20));
alter table userInfo modify(msn varchar2(25));
alter table userInfo drop(msn);
alter table userInfo drop constraint id_pk;
alter table userInfo add constraint id_pk primary key (id);
3、创建视图
create table v$_dept_view
as
select deptno, dname from dept;
--重新编译视图
alter view v$_dept_view compile;
提示：视图一般是一个表或多个表的查询或子查询，这样可以减少代码量，但同时增加了对数据库视图的维护程度，如：某个表字段被删除或是修改，视图也要重新创建或修改，同时占用了数据库的一部分空间；视图就是一个虚拟的表格；
4、创建索引
普通索引：create index idx_dpt_dname on dept(dname);
联合索引：create index idx_dept_dname_deptno on dept(dname, deptno);
--唯一索引
create unique index idx_emp_ename on scott.emp(ename);
--反向键索引
create index idx_emp_rev_no on scott.emp(empno) reverse;
--位图索引
create bitmap index idx_emp_name on scott.emp(dname);
--索引组织表,一定要有主键
create table tab (
id int primary key,
name varchar2(20)
) organization index;
--索引组织表的insert效率非常低
--分区表索引
create index idx_name on table(col) local/global;
--索引分区
提示：当给表创建主键或唯一键约束时，系统也会创建一个约束给该字段；同样创建索引也会占用数据库空间；索引在访问、查询的时候效率有提高，但是在修改表的时候效率就会降低；
5、创建序列
create sequence seq;
select seq.nextval from dual;
insert into tab values(sql.nextval, ‘music’);
create sequence seqtab
start with 2 –从2开始
increment by 3—每次加3
nomaxvalue—没有最大值
minvalue 1—最小值1
nocycle—不循环
nocache;--不缓存
--修改序列 ，不能修改起始值
alter sequence seqtab
maxvalue 1000;
6、创建同义词
同义词，顾名思义就是说别名、或是另一个名字。
create synonym scott_emp for scott.emp;
create public synonym scott_dept for scott.dept;
select * from scott_emp;
select * from scott_dept;
7、创建表空间
create tablespace HooMS
datafile 'E:\HooMS.dbf'
size 5M
autoextend on next 2M maxsize 10M;
--创建用户、分配可以操作表空间
create user hoo
identified by hoo
default tablespace HooMS
temporary tablespace temp;
--创建表空间
create tablespace myMS
datafile 'c:\myMS.dbf'
size 1M
autoextend on;
--扩展表空间--修改表空间大小
alter database
datafile 'c:\myMS.dbf'
resize 2M;
--扩展表空间--添加数据文件
alter tablespace myMS
add datafile 'c:\myMS_2.dbf'
size 1M;
--设置dbf文件自动增长
alter database
datafile 'c:\myMS_2.dbf'
autoextend on next 2M maxsize 4M;
--表空间重命名
alter tablespace myMS
rename to hooMS;
--分离表空间（脱机）
alter tablespace hooMS
offline temporary;
--归档模式下脱机
alter tablespace hooMS
offline immediate;
--使表空间联机
alter tablespace hooMS online;
--删除无数据的表空间
drop tablespace hooMS;
--删除带数据的表空间
drop tablespace hooMS
including contents;
实施dba用到有水平的41条sql语句
1.检查无效的数据文件
Select * from v$data_file;
2.执行失败或中断的Jobs
select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date", to_char(this_date,'yyyy-mm-dd hh24:mi:ss')
"This Date", broken,failures, schema_user, what
from dba_jobs where broken='Y' or failures>0;
3.无效对象检查方法
Select OWNER, OBJECT_NAME, OBJECT_TYPE
from dba_objects
where status = 'INVALID'
and owner=''
ORDER BY 1,2,3;
4.表空间空间不够将导致不能扩展的Objects
Select a.tablespace_name, a.owner, decode(a.partition_name, null, a.segment_name,
a.segment_name || '.' || a.partition_name) "Segment Name", a.extents, round(next_extent/1024) next_extent_kb,
round(b.free / 1024) ts_free_kb,
round(c.morebytes / 1024 / 1024) ts_growth_mb
from dba_segments a,
(Select df.tablespace_name, nvl(max(fs.bytes), 0) free
from dba_data_files df,
dba_free_space fs
where df.file_id = fs.file_id (+)
group by df.tablespace_name) b,
(Select tablespace_name, max(maxbytes - bytes) morebytes, sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible from dba_data_files
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name and a.owner='替换为真实的owner'
and a.tablespace_name = c.tablespace_name
and ((c.autoextensible = 0) or ((c.autoextensible > 0)
and (a.next_extent > c.morebytes)))
and a.next_extent > b.free
order by 1;
5.被Disabled的约束
Select owner, table_name, constraint_name, CONSTRAINT_TYPE from dba_constraints
where status = 'DISABLED' and owner='替换为真实的owner'
ORDER BY 1,2,3;
6.没有索引的外键
SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_name FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
and acc.owner='替换为真实的owner'
AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns)
ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name;
7.被Disabled的触发器
Select owner, nvl(table_name, '') table_name, trigger_name from dba_triggers
where status = 'DISABLED' and owner='替换为真实的owner'
ORDER BY 1,2,3;
8.死锁检测
select SID,TYPE,LMODE,REQUEST,BLOCK from v$lock where type = 'TX';
9.Library Cache Reload Ratio
select SUM(RELOADS)/(SUM(PINS)+SUM(RELOADS))*100 from v$librarycache;
10.Data Dictionary Miss Ratio
Select Round((((sum(GetMisses)) / sum(Gets)) * 100),4) "DC_Miss_Ratio%" From V$rowcache;
11.数据缓冲区的命中率
select (1 - (sum(decode(name, 'physical reads', value, 0)) / (sum(decode(name, 'db block gets', value, 0)) + sum(decode(name, 'consistent gets', value, 0))))) * 100 "Hit Ratio" from v$sysstat;
12.磁盘排序
select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1, (a.value+b.value))),2) "Disk_Sort_Ratio％" from v$sysstat a, v$sysstat b where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';
13. Log Buffer latch Contention
SELECT name "Redo Name", gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,round(misses/gets*100,3)) "Miss_Ratio％", Decode(immediate_gets+immediate_misses,0,0, round( immediate_misses/(immediate_gets+immediate_misses)*100,3)) "Immediate Misses Ratio%" FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
-
含有50个以上的Extent且30%以上碎片的表空间
通过以下SQL语句查询：
查看所有表空间的碎片程度（值在30以下表示碎片很多）--
select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select
b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);
15.表空间上的I/O分布
SELECT t.name ts_name, f.name file_name, s.phyrds phy_reads, s.phyblkrd phy_blockreads, s.phywrts phy_writes, s.phyblkwrt phy_blockwrites FROM gvdatafile f, gv$filestat s WHERE t.ts# = f.ts# and f.file# = s.file# ORDER BY s.phyrds desc, s.phywrts desc;
16.数据文件上的I/O分布
Select ts.NAME "Table Space", D.NAME "File Name", FS.PHYRDS "Phys Rds", decode(fstot.sum_ph_rds, 0, 0, round(100 * FS.PHYRDS / fstot.sum_ph_rds, 2)) "% Phys Rds", FS.PHYWRTS "Phys Wrts", decode(fstot.sum_ph_wrts, 0, 0, round(100 * FS.PHYWRTS / fstot.sum_ph_wrts, 2)) "% Phys Wrts" FROM VDATAFILE d, Vfilestat) fstot WHERE D.FILE# = FS.FILE# AND D.TS# = TS.TS#;
17.已经分配超过100 Extents的Segments
通过以下SQL语句查询：
Select segment_type, owner, segment_name, extents, partition_name
from dba_segments
where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
and owner='替换为真实的owner'
and extents > 100;
18.因表空间空间不够将导致不能扩展的Objects
Select a.tablespace_name, a.owner,
decode(a.partition_name, null, a.segment_name,
a.segment_name || '.' || a.partition_name) "Segment Name", a.extents, round(next_extent/1024) next_extent_kb,
round(b.free / 1024) ts_free_kb,
round(c.morebytes / 1024 / 1024) ts_growth_mb
from dba_segments a,
(Select df.tablespace_name, nvl(max(fs.bytes), 0) free from dba_data_files df,
dba_free_space fs
where df.file_id = fs.file_id (+)
group by df.tablespace_name) b,
(Select tablespace_name, max(maxbytes - bytes) morebytes, sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible from dba_data_files
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name and a.owner='替换为真实的owner'
and a.tablespace_name = c.tablespace_name
and ((c.autoextensible = 0) or ((c.autoextensible > 0) and (a.next_extent > c.morebytes)))
and a.next_extent > b.free
order by 1;
19.查看存储过程是否失效
Select * from user_objects;
8.27.2. 优化建议
20.SQL ordered by Elapsed Time
SELECT *
FROM (SELECT parsing_user_id executions, sorts,
command_type,
disk_reads,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE rownum < 10;
21.表空间可用性检查
select tablespace_name,status from dba_tablespaces;
22.检查alert_SID.Log,并找出最近的10份trace文件
Select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid
23.undo表空间管理模式
(1) 检查方法（或操作命令）
SELECT A.NAME,'实例' || A.INST_ID || ': ' || A.DISPLAY_VALUE VALUE FROM GV$PARAMETER A WHERE A.NAME = 'undo_management'
24.undo表空间大小
(1) 检查方法（或操作命令）
SELECT 'UNDO表空间大小', TO_CHAR(WM_CONCAT(A.TABLESPACE_NAME || '表空间大小为' || SUM(BYTES) / 1024 / 1024 || 'M')) VALUE FROM DBA_TABLESPACES A, DBA_DATA_FILES B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME LIKE 'UNDO%' GROUP BY A.TABLESPACE_NAME
25.临时表空间使用情况
(1) 检查方法（或操作命令）
SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 || 'M' VALUE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME
26.数据库参数db_block_size
(1) 检查方法（或操作命令）
SELECT A.NAME,'实例' || A.INST_ID || ': ' || A.DISPLAY_VALUE VALUE FROM GV$PARAMETER A WHERE A.NAME = 'db_block_size'
27.数据库文件的表空间使用情况
SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(F) NO_MERGE(U) NO_MERGE(O) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",
ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES, 0) / 1024 / 1024,NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024) ,2) "占用量(MB)",
TO_CHAR(ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES / A.BYTES * 100, 0),NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)) ,2),'999.99')||'%' "占用率(MB)",
ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,NVL(F.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",
D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型", D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理" FROM SYS.DBA_TABLESPACES D, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES,
COUNT(FILE_ID) COUNT, CASE WHEN
SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO' ELSE '' END AS AUTOEXTENSIBLE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_UNDO_EXTENTS
WHERE STATUS IN ('UNEXPIRED', 'EXPIRED')
GROUP BY TABLESPACE_NAME) U
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+) AND NOT (D.EXTENT_MANAGEMENT = 'LOCAL' AND D.CONTENTS = 'TEMPORARY')
--AND D.TABLESPACE_NAME LIKE '%COMP%'
UNION ALL
SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(T) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",
ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
ROUND(NVL(T.BYTES, 0) / 1024 / 1024 ,2) "占用量(MB)", TO_CHAR(ROUND(NVL(T.BYTES / A.BYTES * 100, 0) ,2),'999.99')||'%' "占用率(MB)", ROUND((NVL(A.BYTES, 0) / 1024 / 1024 - NVL(T.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)", D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型", D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理" FROM SYS.DBA_TABLESPACES D,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT, CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO' ELSE '' END AS AUTOEXTENSIBLE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A, (SELECT SS.TABLESPACE_NAME, SUM((SS.USED_BLOCKS * TS.BLOCKSIZE)) BYTES
FROM GV$SORT_SEGMENT SS, SYS.TS$ TS
WHERE SS.TABLESPACE_NAME = TS.NAME
GROUP BY SS.TABLESPACE_NAME) T
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
AND D.EXTENT_MANAGEMENT = 'LOCAL'
AND D.CONTENTS = 'TEMPORARY'
--AND D.TABLESPACE_NAME LIKE :2
ORDER BY 1 Asc,6 DESC;
28.检查数据库的JOB
select * from dba_jobs
29.用户默认表空间
select username,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace from dba_users where default_tablespace in ('USERS','SYSTEM','SYSAUX');
30.检查无效的存储过程
(1) 检查方法（或操作命令）
select * from dba_objects where object_type in ('PROCEDURE','PACKAGE','PACKAGE BODY') AND STATUS<>'VALID';
31.检查无效的索引
SELECT INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, STATUS
FROM DBA_INDEXES
WHERE OWNER LIKE 'FMIS%' AND STATUS <> 'VALID';
32.检查无效的触发器
SELECT owner, trigger_name, table_name, status
FROM dba_triggers
WHERE status = 'DISABLED';
33.检查分区表
SELECT "用户","名称","类型",SUM("大小（M）") FROM (
SELECT OWNER "用户",SEGMENT_NAME "名称",SEGMENT_TYPE "类型",BYTES/1024/1024 "大小（M）" FROM DBA_SEGMENTS WHERE BYTES>=10241024200 AND SEGMENT_TYPE IN ('INDEX','TABLE')
UNION ALL
SELECT A.OWNER "用户",B.TABLE_NAME "名称",A.SEGMENT_TYPE "类型",A.BYTES/1024/1024 "大小（M）" FROM DBA_SEGMENTS A,DBA_LOBS B WHERE A.SEGMENT_NAME=B.SEGMENT_NAME AND A.BYTES>=10241024200 AND A.SEGMENT_TYPE='LOBSEGMENT'
) GROUP BY "用户","名称","类型"
ORDER BY 4 DESC
34.表空间大小使用情况
(1) 检查方法（或操作命令）
SELECT B.TABLESPACE_NAME 表空间名称, ROUND((B.BYTES/1024)/1024,2) 总空间大小, NVL2(A.BYTES,ROUND((B.BYTES-NVL(A.BYTES,0))/1024/1024,2),B.BYTES) 已使用大小MB, NVL2(A.BYTES,ROUND(NVL(A.BYTES,0)/1024/1024,2),0) 未使用大小MB, NVL2(A.BYTES,TO_CHAR(ROUND(((B.BYTES-NVL(A.BYTES,0))/B.BYTES)*100,2),'990.0'),'100')||'%' 已使用率
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)A,
(SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B
WHERE B.TABLESPACE_NAME=A.TABLESPACE_NAME(+);
35.查找时间戳不匹配的对象 若有数据返回，应立即反馈，防止出现ORA-04068
SELECT du.name duname, do.name dname, pu.name puname, po.name pname, p_timestamp, po.stime p_stime FROM sys.obj d, sys.obj du, sys.user$ pu WHERE p_obj# = po.obj#(+) AND d_obj# = do.obj# AND do.owner# = du.user# AND po.owner# = pu.user# AND do.status = 1 /dependent is valid/ AND po.status = 1 /parent is valid/ AND po.stime != p_timestamp /parent timestamp does not match/ AND do.type# not in (13, 28, 29, 30) /dependent type is not a type or java/ AND po.type# not in (13, 28, 29, 30) /parent type is not a type or java/ ORDER BY 4, 2
36.归档日志检查 检查1天内每个实例每小时产生归档，若某个时段产生归档较大，需分析出现该情况是否正常。（比如电费转换时会生成较多的redo） SELECT TO_CHAR(COMPLETION_TIME,'YYYY-MM-DD HH24') "COMPLETION TIME",THREAD#, ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024) AS "SIZE(M)" FROM V$ARCHIVED_LOG WHERE (SYSDATE-COMPLETION_TIME)<1 GROUP BY TO_CHAR(COMPLETION_TIME,'YYYY-MM-DD HH24'),THREAD# ORDER by 1 DESC,2
36.如何生成SQL报告 AWR分析 生成当天每个实例9:00-18:00的AWR报告，以每小时为快照间隔，进行分析。同时应将AWR中耗时超过1秒的SQL进行分析。每月1日AWR报告应保存1年时间。 --执行awrsqrpt.sql脚本，按要求输入begin_snap，end_snap，sql_id即可。 SQL> @?/rdbms/admin/awrsqrpt.sql 37.出现oracle进程耗费CPU资源过高情况，可参考下面命令找到相关SQL --topas出来的最耗cpu资源的pid，根据pid找到最耗资源的sql： select /*+ ordered */ sql_text from vsession b where b.paddr = (select addr from v$process c where c.spid = '&pid')) order by piece asc
38.如何使用SQL Tuning Advisor分析SQL 以下所有涉及sql_id=0r42sp7k3p54n需根据实际替换 --创建优化任务 declare l_task_id varchar2(20); begin l_task_id := dbms_sqltune.create_tuning_task(sql_id=>'0r42sp7k3p54n', scope => 'COMPREHENSIVE', time_limit => 120, task_name => 'STA_0r42sp7k3p54n' ); dbms_sqltune.execute_tuning_task('STA_0r42sp7k3p54n'); end; 39.临时表数量检查 检查临时表数量，防止因临时表异常增加导致表空间过度使用，浪费空间，同时，过多的临时表会导致数据字典膨胀，影响性能。若临时表数据过多（超过10万），需检查后台JOB是否出现问题。 SELECT COUNT() FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER LIKE 'FMIS%' AND (SUBSTR(OBJECT_NAME,1,4)='TEMP' OR SUBSTR(OBJECT_NAME,1,3)='TMP' OR (SUBSTR(OBJECT_NAME,1,5)='BBHBG' AND LENGTH(OBJECT_NAME)>5) OR (SUBSTR(OBJECT_NAME,1,10)='ZWCX_PZTMP' AND LENGTH(OBJECT_NAME)>10) OR (SUBSTR(OBJECT_NAME,1,7)='PZCX_20' AND LENGTH(OBJECT_NAME)>7) OR (SUBSTR(OBJECT_NAME,1,9)='LJZTABLE_' AND LENGTH(OBJECT_NAME)>9)) 40.Jobs运行检查 检查JOB运行状况，避免出现运行失败情况导致汇总或临时表删除等出现问题。 select * From dba_jobs where broken='Y' or failures>0 order by job; 统计HZ_JOB表中各类别汇总任务数据，若剩余汇总任务过多，需分析汇总是否出现问题。 select job_lb, count() from hz_job group by rollup(job_lb); 41.Alert日志检查 每天对alert日志进行检查，及时排除日志中的报错等异常问题。 Alert.log的位置：ORACLE_BASE/admin/<db_name>/bdump/alert_ORACLE_SID.log
视图、索引、存储过程 、触发器、游标及事务详解
1.视图不占物理存储空间，它只是一种逻辑对象。可将其看成一个"虚表"
视图是一个由select 语句指定，用以检索数据库表中某些行或列数据的语句存储定义
注：创建视图语句中，不能包括order by、compute或者compute by 子句，也不能出现into关键字
2.创建水平视图
视图的常见用法是限制用户只能够存取表中的某些数据行，用这种方法产生的视图称为水平视图，即表中行的子集
create view student_view1
as
select * from student
where (class_id='0903')
3.创建投影视图
如果限制用户只能存取表中的部分列的数据，那么，使用这种方法创建的视图就称为投影视图，即表中列的子集
create view student_view2
as
select student_id as '学号' ,student_name as '姓名',sex as '性别'
from student
where sex=1
with check option /*强制视图上执行的所有修改语句必须符合由select 语句设置的准则*/
4.创建联合视图
用户可以生成从多个表中提取数据的联合视图，把查询结果表示为一个单独的"可见表"
5.索引是数据库的对象之一，索引是为了加速对表中数据行的检索而创建的一种分散的一种存储结构。
索引是针对一个表而建立的，它是由数据页面以外的索引页面组成的
6.索引的分类
聚簇索引
数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列值的组合排列记录
create unique clustered index book_id_index--惟一性聚簇索引
on book(book_id asc)
with
fillfactor=50 /*填充因子50%*/
非聚簇索引
create nonclustered index student_course_index
on student_course(student_id asc,course_id asc)
with
fillfactor=50
7.存储过程
存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集。它与特定的数据库相关联，存储在SQL Server 服务器上
create proc spAddStudents
@name nvarchar(50)=null
as
begin transaction --事务
insert into [StudentInfo].[dbo].[Students]
(Name)
values
(@name)
if @@ERROR<>0
begin
rollback tran
return
end
commit transaction--提交事务
创建一个实现加法计算并将运算结果作为输出参数的存储过程
create proc spAdd
@value1 int,
@value2 int,
@result int output
as
select @result=@value1+@value2
go
执行spAdd存储过程
declare @value1 int
declare @value2 int
declare @result int
set @value1=1
set @value2=1
exec spAdd @value1,@value2,@result output
print convert(char(5),@value1) +'+'+convert(char(5),@value2) +'='+ convert(char(5),@result)
8.触发器
触发器是一种实施复杂数据完整性的特殊存储过程，在对表或视图执行update、insert或delete语句时自动触发执行，以防止对数据进行不正确、未授权或不一致的参数
/*创建update触发器*/create trigger [dbo].[TaocanType_update] on [dbo].[Table_TaocanType]for update
asupdate [dbo].[Table_ChoseTaocanType] set Taocan=inserted.Taocan
from [dbo].[Table_ChoseTaocanType] ,inserted where [dbo].[Table_ChoseTaocanType].TaocanId=inserted.TaocanId
触发器能够维持两个表间的参照完整性，就像外键一样。外键执行这个任务的效率更高，因为它们在数据改变之前被测试，而不像触发器在数据改变后才触发
9.游标
游标是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力
(1)创建游标
(2)打开游标
(3)读取数据
(4)数据操作
(5)关闭和释放游标
declare @taocan nvarchar(50),@youhui nvarchar(50)
declare taocan_cursor scroll cursor --声明游标
for
select Taocan,youhui from [189Shop].[dbo].[Table_TaocanType]
for read only
open taocan_cursor ---打开游标
fetch from taocan_cursor into @taocan,@youhui --从游标中提取行
while @@FETCH_STATUS=0--表示成功完成FETCH 语句
begin
print '套餐：'+@taocan+',优惠：'+@youhui
fetch from taocan_cursor into @taocan,@youhui
end
close taocan_cursor --关闭游标
deallocate taocan_cursor --释放游标
10.事务
所谓事务，是指一个操作序列，这些操作序列要么都被执行，要么都不被执行，它是一个不可分割的工作单元
JAVA中十六种主流数据库的JDBC连接字符串
[JAVA中十六种主流数据库的JDBC连接字符串]
1. Microsoft SQLServer**(http: // www.microsoft.com)
(SqlServer 2000)
Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" ); cn = DriverManager.getConnection( "jdbc:microsoft:sqlserver://localhost:1433;databaseName=pubs", sUsr, sPwd ); (SqlServer 2005)
Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" ); cn = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;databaseName=pubs", sUsr, sPwd );
2. Oracle**(http: // www.oracle.com/ip/deploy/d… )classes12.zip Class.forName( "oracle.jdbc.driver.OracleDriver" ); cn = DriverManager.getConnection( "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );
3. MySQL**(http: // *www.mysql.com*)mm.mysql-2.0.2-bin.jar
Class.forName( "org.gjt.mm.mysql.Driver" ); cn = DriverManager.getConnection( "jdbc:mysql://MyDbComputerNameOrIP:3306/myDatabaseName", sUsr, sPwd );
4. PostgreSQL**(http: // *www.de.postgresql.org*)pgjdbc2.jar
Class.forName( "org.postgresql.Driver" ); cn = DriverManager.getConnection( "jdbc:postgresql://MyDbComputerNameOrIP/myDatabaseName", sUsr, sPwd );
5. Sybase**(http: // jtds.sourceforge.net)jconn2.jar Class.forName( "com.sybase.jdbc2.jdbc.SybDriver" ); cn = DriverManager.getConnection( "jdbc:sybase:Tds:MyDbComputerNameOrIP:2638", sUsr, sPwd ); //(Default-Username/Password: "dba"/"sql")
6. Microsoft SQLServer**(http: // jtds.sourceforge.net) Class.forName( "net.sourceforge.jtds.jdbc.Driver" ); cn = DriverManager.getConnection( "jdbc:jtds:sqlserver://MyDbComputerNameOrIP:1433/master", sUsr, sPwd );
7. ODBC** Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection cn = DriverManager.getConnection( "jdbc:odbc:" + sDsn, sUsr, sPwd );
8.DB2(新添加)
Class.forName("com.ibm.db2.jdbc.net.DB2Driver"); String url="jdbc:db2://192.9.200.108:6789/SAMPLE" cn = DriverManager.getConnection( url, sUsr, sPwd );
9.Microsoft SQL Server series (6.5, 7.x and 2000) and Sybase 10** JDBC Name: jTDS URL: jtds.sourceforge.net/ Version: 0.5.1 Download URL: sourceforge.net/project/sho… 语法: Class.forName("net.sourceforge.jtds.jdbc.Driver "); Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://host:port/database","user","password"); or Connection con = DriverManager.getConnection("jdbc:jtds:sybase://host:port/database","user","password");
10.Postgresql JDBC Name: PostgreSQL JDBC URL: jdbc.postgresql.org/ Version: 7.3.3 build 110 Download URL: jdbc.postgresql.org/download.ht… 语法: Class.forName("org.postgresql.Driver"); Connection con=DriverManager.getConnection("jdbc:postgresql://host:port/database","user","password");
11.IBM AS400主机在用的JDBC
语法 有装V4R4以上版本的Client Access Express 可以在C:\Program Files\IBM\Client Access\jt400\lib 找到 driver 档案 jt400.zip，并更改扩展名成为 jt400.jar 语法: java.sql.DriverManager.registerDriver (new com.ibm.as400.access.AS400JDBCDriver ()); Class.forName("com.ibm.as400.access.AS400JDBCConnection"); con = DriverManager.getConnection("jdbc:as400://IP","user","password");
12.informix
Class.forName("com.informix.jdbc.IfxDriver").newInstance();
String url = "jdbc:informix-sqli://123.45.67.89:1533/testDB:INFORMIXSERVER=myserver; user=testuser;password=testpassword";
Lib：jdbcdrv.zip
Class.forName( "com.sybase.jdbc.SybDriver" ) url="jdbc:sybase:Tds:127.0.0.1:2638/asademo"; SybConnection connection= (SybConnection)DriverManager.getConnection(url,"dba","sql");
13.SAP DB** Class.forName ("com.sap.dbtech.jdbc.DriverSapDB"); java.sql.Connection connection = java.sql.DriverManager.getConnection ( "jdbc:sapdb://" + host + "/" + database_name,user_name, password) 14.InterBase String url = "jdbc:interbase://localhost/e:/testbed/database/employee.gdb"; Class.forName("interbase.interclient.Driver"); //Driver d = new interbase.interclient.Driver (); /* this will also work if you do not want the line above */ Connection conn = DriverManager.getConnection( url, "sysdba", "masterkey" );
15.HSqlDB** url: hsqldb.sourceforge.net/ driver: org.hsqldb.jdbcDriver 连接方式有4种，分别为： con-str（内存）: jdbc:hsqldb. con-str（本地）: jdbc:hsqldb:/path/to/the/db/dir con-str（http）: jdbc:hsqldb:http://dbsrv con-str（hsql）: jdbc:hsqldb:hsql://dbsrv
16.Accrss#Aceess
jdbc.driver=sun.jdbc.odbc.JdbcOdbcDriver #jdbc.url=jdbc:odbc:anydata jdbc.url=jdbc: odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=E:\workspace\anyData003.mdb jdbc.username= jdbc.password= | |
oracle 创建表空间,表,以及用户
oracle 创建表空间,表,以及用户
oracle要创建表要首先创建表空间，当然默认是有表空间的。而mysql创建表时，会自动创建表空间，myisam会自动建三个文件.MYD,.MYI,.frm。innodb呢，如果没有配置独立表空间的话，默认表空间ibdata1，oracle用户权限根着表空间走的，而mysql不是，mysql服务器端安装好后，会自动带上一个mysql数据库，用户的权限是记录在mysql这个数据库的表中。在这方面二种数据库设计的理念不同。
1,创建临时表空间
- SQL> create temporary tablespace test_temp
- 2 tempfile '/opt/ora10/oradata/test/test_tmp.dbf'
- 3 size 32m
- 4 autoextend on
- 5 next 32m maxsize 148m
- 6 extent management local;
- Tablespace created.
2,创建表空间
- SQL> create tablespace test_data
- 2 logging
- 3 datafile '/opt/ora10/oradata/test/test_data.dbf'
- 4 size 32m
- 5 autoextend on
- 6 next 32m maxsize 148m
- 7 extent management local;
- Tablespace created.
3,创建表用户，并表空间分配给用户
- SQL> create user tank identified by tank
- 2 default tablespace test_data
- 3 temporary tablespace test_temp;
- User created.
4,查看创建的表空间
- SQL> select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
- 2 from dba_tablespaces t, dba_data_files d where
- 3 t.tablespace_name = d.tablespace_name group by t.tablespace_name;
- TABLESPACE_NAME TS_SIZE
- ------------------------------ ----------
- SYSAUX 240
- UNDOTBS1 25
- TEST_DATA 32
- USERS 5
- SYSTEM 480
5,查看临时表空间
- SQL> select sum(bytes)/1024/1024 "temp size(M)" from dba_temp_files where tablespace_name='TEST_TMP';
- temp size(M)
- ------------
6,将表空间的部分操作权限分配给用户
- SQL> grant connect,resource,imp_full_database,exp_full_database, dba to user;
- Grant succeeded.
7,resource这是一个权限组，有这个组就不要一个一个加了。
- SQL> select GRANTEE,PRIVILEGE from DBA_SYS_PRIVS where GRANTEE ='RESOURCE';
- GRANTEE PRIVILEGE
- ------------------------------ ----------------------------------------
- RESOURCE CREATE TRIGGER
- RESOURCE CREATE SEQUENCE
- RESOURCE CREATE TYPE
- RESOURCE CREATE PROCEDURE
- RESOURCE CREATE CLUSTER
- RESOURCE CREATE OPERATOR
- RESOURCE CREATE INDEXTYPE
- RESOURCE CREATE TABLE
- 8 rows selected.
以下操作我都是能以tank这个用户进行操作的
9,创建表
- create table test(
- id number(10) not null primary key,
- name varchar(2) null ,
- city number(1) null
- );
10,创建临时表
- CREATE GLOBAL TEMPORARY TABLE test1 (
- id number(10) not null primary key,
- name varchar(2) null ,
- city number(1) null
- );
11,查看表
- SQL> select distinct table_name from user_tables;
- TABLE_NAME
- ------------------------------
- TEST
- TEST1
- //或者
- SQL> select table_name from dba_tables where owner='TANK';
- TABLE_NAME
- ------------------------------
- TEST
- TEST1
