Oracle学习笔记2

最新推荐文章于 2025-04-12 10:51:23 发布

ciqiezai9646

最新推荐文章于 2025-04-12 10:51:23 发布

阅读量99

点赞数

--查看具有dba权限的用户
select * from v$pwfile_users
--计算日志缓冲区的块尺寸
SELECT ROUND((A.REDOSIZE + B.REDOWAST) / C.REDOBLKS) + 16 AS REDO_BLOCK_SIZE
FROM (SELECT VALUE REDOSIZE FROM V$SYSSTAT WHERE NAME = 'redo size') A,
(SELECT VALUE REDOWAST FROM V$SYSSTAT WHERE NAME = 'redo wastage') B,
(SELECT VALUE REDOBLKS FROM V$SYSSTAT WHERE NAME = 'redo blocks written') C
--缓存命中率查询语句
select 1 - (sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
(sum(decode(name, 'consistent gets', value, 0))))) "缓冲命中率"
from v$sysstat;
--合并索引分区碎片
select 'alter index ' || a.owner || '.' || a.index_name || ' coalesce ;'
from dba_indexes a
where a.owner in ('ZLHIS', 'ZLTOOLS')
and a.index_type = 'NORMAL'
AND A.uniqueness = 'UNIQUE'
AND A.status = 'VALID'
AND A.tablespace_name IS NOT NULL;
--检查数据库的状态
SQL>select instance_name,status from v$instance ; --status=open
--监听器的启动、停止和状态
$lsnrctl start
$lsnrctl stop
$lsnrctl status
--检查表空间的使用情况
SELECT A.TABLESPACE_NAME AS "TableSpace Name",
A.TOTAL_SIZE AS "Total Size",
ROUND(B.TOTAL_FREE_SIZE, 1) AS "Total Free Size",
ROUND((A.TOTAL_SIZE - B.TOTAL_FREE_SIZE), 2) AS "Used Size",
TO_CHAR(100 * B.TOTAL_FREE_SIZE / A.TOTAL_SIZE, '99.99') || '%' AS "Percent Free"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS TOTAL_SIZE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES / 1024 / 1024) AS TOTAL_FREE_SIZE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
--另一种查询方法
SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME,
ROUND(A.BYTES / (1024 * 1024 * 1024), 2) AS "TOTAL(G)",
ROUND(B.BYTES / (1024 * 1024 * 1024), 2) AS "USED(G)",
ROUND(C.BYTES / (1024 * 1024 * 1024), 2) AS "FREE(G)",
ROUND((B.BYTES * 100) / A.BYTES, 2) AS "% USED",
ROUND((C.BYTES * 100) / A.BYTES, 2) AS "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
--计算空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称",
ROUND(D.AVAILB_BYTES, 2) AS "表空间大小(G)",
ROUND(D.MAX_BYTES, 2) AS "最终表空间大小(G)",
ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) AS "已使用空间(G)",
TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,2),'999.99') AS "使用比",
ROUND(F.USED_BYTES, 6) AS "空闲空间(G)",F.MAX_BYTES AS "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) / (1024 * 1024 * 1024),6) MAX_BYTES
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC
--检查是否有表空间碎片
--FSFI%(可用破碎空间索引)：数值最大值为100，表示完全没有破碎的空间。数值越低表明破碎的空间越严重，低于30%就需要进行重整
SELECT A.TABLESPACE_NAME AS "TableSpace Name",
SQRT(MAX(A.BLOCKS) / SUM(A.BLOCKS)) * (100 / SQRT(SQRT(COUNT(A.BLOCKS)))) AS "FSFI%(可用破碎空间索引)"
FROM DBA_FREE_SPACE A
GROUP BY A.TABLESPACE_NAME
ORDER BY 1;
--每月数据库增长报告
SELECT TO_CHAR(A.CREATION_TIME, 'RRRR Month') AS "Month",
SUM(A.BYTES) / 1024 / 1024 / 1024 AS "Growth in GB"
FROM SYS.V_$DATAFILE A
GROUP BY A.CREATION_TIME
ORDER BY TO_CHAR(A.CREATION_TIME, 'RRRR Month');
--每月表空间增长报告
SELECT A.TS# AS "TableSpace No",
B.NAME AS "TableSpace Name",
SUM(A.BYTES) / 1024 / 1024 / 1024 AS "Growth in GB"
FROM SYS.V_$DATAFILE A, SYS.V$TABLESPACE B
WHERE A.CREATION_TIME > SYSDATE - 365
AND A.TS# = B.TS#
GROUP BY A.TS#, B.NAME, TO_CHAR(A.CREATION_TIME, 'RRRR Month')
ORDER BY A.TS# ;
--表分析，并将分析的结果放入到chained_rows表中
CREATE TABLE system.chained_rows(
owner_name VARCHAR2(30),
table_name VARCHAR2(30),
cluster_name VARCHAR2(30),
partition_name VARCHAR2(30),
subpartition_name VARCHAR2(30),
head_rowid ROWID,
analyze_timestamp DATE
) ;
--以下为分析的语句
SELECT 'analyze table ' || OWNER || '.' || TABLE_NAME || ' list chained rows into system.chained_rows ;'
FROM DBA_TABLES
WHERE OWNER IN ('ZLHIS', 'ZLTOOLS');
--Oracle数据库的构成
数据文件 data file
在线重做日志文件 online redo log file
控制文件 control file
初始参数文件 initialization parameter file
密码文件 password file
归档日志文件 archive log file
警告日志文件 alert log file
跟踪文件 trace file
--新增一个日志文件
ALTER DATABASE ADD LOGFILE GROUP 4 ('C:\app\Administrator\oradata\CeShiKu\REDO4.LOG') SIZE 50m ;
--新增一个日志文件到组中（不需要指定大小，自动按照组中其他成员的大小）
alter database add logfile member 'C:\app\Administrator\oradata\CeShiKu\REDO1A.LOG' to group 1;
--删除日志文件(状态为inactive,否则需要切换日记)
alter system switch logfile ;
alter database drop logfile group 1 ;
--初始化参数分为动态参数和静态参数
--动态参数使用alter system之后不需要重启数据库即可生效；
--静态参数修改后需要重启数据才能生效；
--通过spfile创建pfile
create pfile='c:\pfile.ora' from spfile ;
--创建表空间
CREATE TABLESPACE USERSDATA
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\CESHIKU\USERS_DATA_01.DBF' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M ;
CREATE TABLESPACE USERSDATA
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\CESHIKU\USERS_DATA_01.DBF' SIZE 100M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE ;
--删除表空间
DROP TABLESPACE USERSDATA INCLUDING CONTENTS AND DATAFILES [cascade constraints] ;
--创建undo表空间
CREATE UNDO TABLESPACE UNDOTBS2
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\CESHIKU\UNDOTBS02.DBF' SIZE 700M ;
--创建临时表空间
CREATE TEMPORARY TABLESPACE TEMP02
TEMPFILE 'C:\APP\ADMINISTRATOR\ORADATA\CESHIKU\TEMP02.DBF' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M ;
--表空间离线
alter tablespace tablespaceName offline ;
--表空间在线
alter tablespace tablespaceName on ;
--表空间增加一个数据文件
alter tablespace tablespaceName
add datafile 'C:\APP\ADMINISTRATOR\ORADATA\CESHIKU\USERS_DATA_01.DBF' size 100m ;
--增加或减少表空间的大小
alter tablespace tablespaceName
datafile 'C:\APP\ADMINISTRATOR\ORADATA\CESHIKU\USERS_DATA_01.DBF' resize 200m ;
--修改数据文件的大小
alter database datafile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF' resize 1000m;
--设置undo的时间
SQL>alter system set undo_retention=1800 scope=both ;(both=spfile+memory)
--如何迁移系统数据库文件到其他地方
1.SQL>shutdown immediate ;
2.SQL>host copy c:\app\Administrator\oradata\CeShiKu\system01.dbf d:\oradata\system01.dbf;
3.SQL>startup mount ;
4.SQL>alter database rename file 'c:\app\Administrator\oradata\CeShiKu\system01.dbf' to 'd:\oradata\system01.dbf';
5.SQL>alter database open ;
--如何迁移非系统数据文件到其他地方
1.SQL>alter tablespace usertbs offline ;
2.SQL>host copy c:\app\Administrator\oradata\CeShiKu\usertbs01.dbf d:\oradata\usertbs01.dbf ;
3.SQL>alter database rename file 'c:\app\Administrator\oradata\CeShiKu\usertbs01.dbf' to 'd:\oradata\usertbs01.dbf';
4.SQL>alter tablespace usertbs online ;
--open_cursors的解释
open_cursors：控制每个session最多能够同时打开的cursor的数量，当超过这个cursor数量时，就会出现ora-01000错误；
--下面的语句用于查询该参数的设置值以及曾经达到的最大值
select max(a.value) as highest_open_cur,p.value as max_open_cur
from v$sesstat a,v$statname b,v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name = 'open_cursors'
group by p.value ;
SQL语句在Oracle中的分类：
1.简单SQL语句（single SQL）：无嵌套，整个语句只有一层select。
2.复杂SQL语句（complex SQL）：有嵌套：使用了视图，子查询，集合操作等的SQL语句。
--传统数据库的类型
1.OLTP:ONLINE TRANSACTION PROCESSING:在线事务系统
2.OLAP:ONLINE ANALYTICAL PROCESSING：在线分析系统
--block的大小是由 db_block_size 的大小决定的
show parameter db_block_size ;
--系统检查点
alter system checkpoint ;
--日记切换
alter system switch logfile ;
--刷新系统缓冲区(注意在生产环境中不要轻易使用)
alter system flush buffer_cache ;
--查看隐含参数_use_single_log_writer的值,判断是否使用多个log_writer进程
--_use_single_log_writer:false - 否 ADAPTIVE - 自适应
select a.ksppinm,b.ksppstvl,a.ksppdesc
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '%log_writer%' ;
--windows系统中启用多线程的模式
alter system set threaded_execution=true scope=spfile ;
--Oracle删除口令文件的后果
Oracle可以正常启动,但是在授权sysdba的时候会报错
SQL>grant sysdba to system ;
ORA-01994: GRANT 失败: 口令文件缺失或已禁用
--查看具有sysdba/sysoper权限的用户列表
select * from v$pwfile_users ;
--Oracle11g及以上隐藏了一个关于Oracle_Base的参数,查询语句如下:
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx
AND x.ksppinm LIKE '%oracle_base%';
--ADR(automatic diagnostic repository)自动诊断库的信息,可以通过以下语句获得:
select * from v$diag_info ;
--如何查看隐藏参数
SELECT i.ksppinm name,
i.ksppdesc description,
CV.ksppstvl VALUE,
CV.ksppstdf isdefault,
DECODE(BITAND(CV.ksppstvf, 7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismodified,
DECODE(BITAND(CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted
FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV('Instance')
AND CV.inst_id = USERENV('Instance')
AND i.indx = CV.indx
AND i.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY REPLACE(i.ksppinm, '_', '') ;
--如何清除缓冲区中的信息
alter system flush buffer_cache ;
alter system flush shared_pool ;