--檢視具有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 ;

Oracle學習筆記2

相關文章