標籤：tar logging 資料表空間 count limited 失效 統計 刪除 source
---統計失效索引select sum(a.aa) from (
select count(*) aa from dba_indexes where status=‘UNUSABLE‘
union all
select count(*) aa from dba_ind_partitions where status=‘UNUSABLE‘
union all
select count(*) aa from dba_ind_subpartitions where status=‘UNUSABLE‘
)a --查失效索引，並且產生重建語句：select ‘alter index ‘||owner||‘.‘||index_name||‘ rebuild parallel 4 online;‘ from dba_indexes where status=‘UNUSABLE‘
union all
select ‘alter index ‘||index_owner||‘.‘||index_name||
‘ rebuild partition ‘||partition_name||‘ parallel 4 online;‘
from dba_ind_partitions where status=‘UNUSABLE‘
union all
select ‘alter index ‘||index_owner||‘.‘||index_name||
‘ rebuild subpartition ‘||subpartition_name||‘ parallel 4 online;‘from dba_ind_subpartitions where status=‘UNUSABLE‘; 例如：
alter index DEVELOP.IDX_RULE_REL_ID
rebuild parallel 4
online;
alter index DEVELOP.IDXULE_REL_TIME
rebuild parallel 4
online;
alter index DEVELOP.THRES_DAY
rebuild parallel 4
online;
--- 清理歸檔日誌指令碼[email protected]$more /data/del_archlog.sh#!/bin/bashcd /datasource ~/.bash_profilerman log=‘/data/del_archlog.log‘ append <<EOFconnect target /;run{DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-3‘;}EOFecho "executed rman at: `date +‘%Y-%m-%d %H:%M:%S‘`" >>del_archlog.log
oracle查看錶空間使用率（命令）：SELECT a.tablespace_name "資料表空間名",total "資料表空間大小",free "資料表空間剩餘大小",(total - free) "資料表空間使用大小",total / (1024 * 1024 * 1024) "資料表空間大小(G)",free / (1024 * 1024 * 1024) "資料表空間剩餘大小(G)",(total - free) / (1024 * 1024 * 1024) "資料表空間使用大小(G)",round((total - free) / total, 4) * 100 "使用率 %"FROM (SELECT tablespace_name, SUM(bytes) freeFROM dba_free_spaceGROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) totalFROM dba_data_filesGROUP BY tablespace_name) bWHERE a.tablespace_name = b.tablespace_name -- oracle重複資料刪除資料delete from t_site awhere a. I_SITE_ID in (select b.I_SITE_ID from t_res_site b group by b.I_SITE_ID having count(b.I_SITE_ID) > 1)and rowid not in (select min(rowid) from t_res_site b group by b.I_SITE_ID having count(b.I_SITE_ID)>1) -- 增加資料表空間大小的四種方法
Meathod1：給資料表空間增加資料檔案
ALTER TABLESPACE app_data ADD DATAFILE
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF‘ SIZE 50M;
Meathod2：新增資料檔案，並且允許資料檔案自動成長
ALTER TABLESPACE app_data ADD DATAFILE
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF‘ SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
Meathod3：允許已存在的資料檔案自動成長
ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF‘
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
Meathod4：手工改變已存在資料檔案的大小
ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF‘RESIZE 100M; --- 建立小檔案資料表空間 （小檔案SMALLFILE 單個檔案最大30G；）CREATE SMALLFILE TABLESPACE "OPENTDB" LOGGING DATAFILE ‘+DATA/XXXdb/datafile/db_data01.dbf‘ SIZE 20480M AUTOEXTEND ON NEXT 512M MAXSIZE 30480M, ‘+DATA/XXXdb/datafile/db_data02.dbf‘ SIZE 20480M AUTOEXTEND ON NEXT 512M MAXSIZE 30480M, ‘+DATA/XXXdb/datafile/db_data03.dbf‘ SIZE 20480M AUTOEXTEND ON NEXT 512M MAXSIZE 30480M, ‘+DATA/XXXdb/datafile/db_data04.dbf‘ SIZE 20480M AUTOEXTEND ON NEXT 512M MAXSIZE 30480M, ‘+DATA/XXXdb/datafile/db_data05.dbf‘ SIZE 20480M AUTOEXTEND ON NEXT 512M MAXSIZE 30480M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ----核實安裝資料庫時有沒修改預設的帳號密碼有效期間：select * from dba_profiles where profile = ‘DEFAULT‘ and resource_name = ‘PASSWORD_LIFE_TIME‘; oracle 10g的密碼沒有預設期限，但11g預設只有180天，需要手工調整：sqlplus / as sysdba--查看概要檔案的密碼有效期間設定，一般為defaultselect * from dba_profiles where profile = ‘DEFAULT‘ and resource_name = ‘PASSWORD_LIFE_TIME‘;--將概要檔案的密碼有效期間由預設的180天修改成無限制，不用重啟資料庫alter profile default limit password_life_time unlimited;--將已經提示ORA_28002警告的使用者解鎖alter user 使用者名稱 identified by 密碼 account unlock;
oracle 常用隨筆
