-
编辑 删除
vnc配置
使用Oracle用戶鍵入vncserver命令在當前目錄下進入.vnc , 修改xstartup , 去掉twm &,在最後一行添加gnome-session &kill掉當前的vnc進程 vncserver -kill :1然後再執行vncserver , 就可以了....
-
编辑 删除
RMAN建制standby
1.首先在standby機器上建制空庫,名字一樣.(該步可選,不一定要完全建制,不建制的話,後續需要手動創建目錄)2.必須確保主數據庫處於歸檔模式3.確定RMAN產生的文件放置的位置,這裡指定為/data/backup.4.在主數據庫上執行...
-
编辑 删除
檢查超過100M大小的表
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_type in ('TABLE','TABLE','INDEX PARTITION','INDEX PARTITION') and owner not in ('SYS', 'SYSTEM','...
-
编辑 删除
創建表空間
CREATE TABLESPACE BI_DATA_05 DATAFILE '/u05/oradata/imbbi/bi_data_05.dbf' SIZE 1024M AUTOEXTEND OFFLOGGINGONLINEPERMANENTEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT...
-
编辑 删除
standby數據庫相關issue
當standby數據庫recover的時候,遇到以下錯誤:ORA-00283: recovery session canceled due to errorsORA-01111: name for data file 20 is unknown - rename to correct fileORA-01110: data file 20: '/u01/product/o...
-
编辑 删除
檢查索引的使用情況
首先產生監控腳本select 'alter index '||owner||'.'||index_name||' monitoring usage;' from dba_indexes where owner in ('YOUR','PROD_DB','OWNER','LIST'); 這個是監控這四個用戶下的索引使用情...
-
编辑 删除
Oracle開機關機腳本
Linux中開啟關閉數據庫的腳本：位於/home/oracle/dba目錄下Primary database：#!/bin/sh/u01/product/oracle/bin/lsnrctl <<EOFstopexitEOFORACLE_HOME=/u01/product/oracle;export ORACLE_HOMEORACLE_SID=epodb;exp...
-
编辑 删除
檢查每個用戶下的實際佔用數據尺寸
select owner,sum(bytes)/1024/1024 from dba_extents group by owner;檢查所有狀態為OPEN的用戶下的實際佔用數據尺寸select owner,sum(bytes)/1024/1024 from dba_extents where owner in(select username from dba...
-
编辑 删除
檢查session狀況
select inst_id,sessions_current,sessions_highwater from gv$license;sessions_current表示除開Oracle本身進程之外的用戶進程select * from gv$resource_limit where resource_name = 'sessions'; ...
-
编辑 删除
Linux下自動輪轉監聽日誌
#!/bin/shlsnrctl <<EOFshowset log_status off;exitEOFFILENAME=`date +%Y%m%d%H`mv /u01/product/oracle/network/log/listener.log /u01/product/oracle/network/log/listener.log.$FILENAMElsnrctl <<EOFse...
-
编辑 删除
日常導出腳本
----in windows circumstance@echo offset year=%date:~10,4%set month=%date:~4,2%set day=%date:~7,2%set mydate=%year%%month%%day%set hhmmss=%time:~0,2%%time:~3,2%%time:~6,2%rem if "%hhmmss:~0,1%"==" " se...
-
编辑 删除
檢查包含LOB類型的表
select owner,table_name from dba_tab_columns where data_type like '%LOB%' and owner in ('EHEALTH','EHC','HHCARE','HAMSTC') order by owner
-
编辑 删除
檢查表的數據量
查詢Schema為'EHEALTH','EHC','HHCARE','HAMSTC'中包含LOB類型的表的數據量select 'select count(*) from '||owner||'.'||table_name||';'from dba_tab_columns where data_type like '%LOB%' and owner ...
-
编辑 删除
檢查所有失效的物件
select * from dba_objects where status='INVALID' and object_type <> 'SYNONYM'
-
编辑 删除
導出欄位註釋信息
SELECT 'COMMENT ON COLUMN ' || OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME &...
-
编辑 删除
檢查命中率
select a.value + b.value "logical_reads", c.value "physical_reads", round(100 * ((a.value+b.value)-c.value)/(a.value+b.value)) "BUFFER HIT RATIO"from v$sysstat a,v$sysstat b,v$sysstat cwhere a.statist...
-
编辑 删除
賦權腳本
select 'grant select on schemaname.'||table_name||' to username; ' from DBA_TABLES where wner='GWDMS'select 'create synonym tms_ap.'||table_name||' for tms.'||table_name||'; ' from DBA_tab...
-
编辑 删除
導入導出腳本
windows下，set NLS_LANG=American_america.AL32UTF8exp system/"""system@dbsec""" wner=gwdms_lh file=e:\backup\gwdms_lh.dmp log=e:\backup\gwdms_lh.txt在Linux下，NLS_LANG=American_america.AL32UTF8;export ...
-
编辑 删除
檢查Schema下的物件的數量
select OWNER, OBJECT_TYPE, count(OWNER) OBJECT_COUNTfrom SYS.DBA_OBJECTSwhere wner ='SFISM4'group by OWNER, OBJECT_TYPEorder by OBJECT_TYPE...
-
编辑 删除
Scheduler Jobs
begindbms_scheduler.create_job(job_name =>'job2',job_type =>'PLSQL_BLOCK',job_action =>'insert into xx values(3);',start_date =>SYSDATE,repeat_interval =>'FREQ=DAILY ;INTERVAL=1'); end;/...
