oracle袁,oracle常用脚本

最新推荐文章于 2021-11-01 21:53:23 发布

腻子防水涂装设备厂家

最新推荐文章于 2021-11-01 21:53:23 发布

阅读量174

点赞数

文章标签： oracle袁

1.查看用户权限

set linesize 200;

col privs_type format a10;

col username format a20;

col table_name format a35;

col column_name format a25;

col PRIVILEGE format a60;

with t1 as

(

select upper('shpj') username from dual

)

select '角色' privs_type,'NULL' username,'NULL' table_name,'NULL' column_name,wm_concat(GRANTED_ROLE) PRIVILEGE from dba_role_privs,t1 where GRANTEE=t1.username group by '角色','NULL','NULL','NULL'

union all

select '表权限',owner,TABLE_NAME,'NULL',wm_concat(PRIVILEGE) PRIVILEGE from dba_tab_privs,t1 where GRANTEE=t1.username group by '表权限',owner,TABLE_NAME,'NULL'

union all

select '列权限',owner,TABLE_NAME,column_name,wm_concat(PRIVILEGE) PRIVILEGE from dba_col_privs,t1 where GRANTEE=t1.username group by '列权限',owner,TABLE_NAME,column_name;

2.查看用户的数据占用的空间大小 & 查看表的数据大小

2.1登录用户

select sum(bytes)/1024/1024 as sizeM from user_segments;

2.2不登录用户

select owner,sum(bytes)/1024/1024 sizeM

from dba_segments

where owner in ('SYSTEM','BI_ODS')

group by owner order by 2 desc;

3.1查看表的数据大小

set linesize 120;

set pagesize 999;

col OWNER for a20;

col SEGMENT_NAME for a40;

select *

from (select owner,

segment_name,

round(sum(bytes)/1024/1024/1024,1) sizeG

from dba_segments

where segment_type <> 'INDEX' and owner='BI_ODS'

group by owner,segment_name

order by sizeG desc)

where rownum <= 20;

3.2查看某张表的数据大小

set linesize 120;

set pagesize 999;

col OWNER for a20;

col SEGMENT_NAME for a40;

select owner,

segment_name,

round(sum(bytes)/1024/1024/1024,1) sizeG

from dba_segments

where segment_type <> 'INDEX' and owner='XSMES'

group by owner,segment_name

having segment_name = 'UEX_DAQ_INFO';

4.替换临时表空间

4.1：查看旧临时表空间信息

set pagesize 999

set linesize 160

col property_name for a26

col property_value for a18

select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

set pagesize 999

set linesize 160

col username for a30

col temporary_tablespace for a18

select username, temporary_tablespace from dba_users;

4.2：创建中转的临时表空间

CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/data/orcl/temp_01.dbf' SIZE 8G AUTOEXTEND ON maxsize 30G;

4.3：切换临时表空间。

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

4.4：重新指定用户临时表空间为新建的临时表空间

select 'alter user '|| username ||' TEMPORARY TABLESPACE TEMP02 ;' from dba_users;

4.5：删除旧的临时表空间数据文件

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

5.TESTUSERB 仅能对TESTUSERA 用户下的某些表增删改查、有些表仅能对某些列update，查询TESTUSERB 用户权限，获取批量赋予语句。

select 'grant '|| PRIVILEGE ||' to TESTUSERB;' from dba_sys_privs where GRANTEE='TESTUSERB' ;

select 'grant '|| PRIVILEGE ||' on TESTUSERA.'||TABLE_NAME||' to TESTUSERB;' from dba_tab_privs where GRANTEE='TESTUSERB' and PRIVILEGE='SELECT';

select 'grant UPDATE ('||COLUMN_NAME||') ON TESTUSERA.'||TABLE_NAME||' TO TESTUSERB;;' from dba_col_privs where GRANTEE='TESTUSERB' and PRIVILEGE='UPDATE';

6.批量kill某个用户session

SELECT 'alter system kill session '''||SID || ',' || SERIAL#||''';' FROM V$SESSION where username='DOCUSER';

7.查询包含某个字段的表

select column_name,

table_name,

data_type ,

data_length,

data_precision,

data_scale

from DBA_TAB_COLUMNS

where column_name='C_KSBH';

8.Oracle12C查询自建用户(非系统自带)

select username from dba_users where INHERITED='NO';

9.监控慢sql

SELECT G.TARGET || ' ' || S.MACHINE || ' ' || ceil((G.LAST_UPDATE_TIME - G.START_TIME)*86400)

FROM V$SESSION_LONGOPS G, V$SESSION S

WHERE G.SID = S.SID

AND G.SERIAL# = S.SERIAL#

AND G.SOFAR <> G.TOTALWORK

AND s.state <> 'INACTIVE'

AND G.OPNAME NOT LIKE 'RMAN:%'

and G.TARGET <> 'TD_USERS'

and rownum <2;

10.根据SPID查找SQL语句

SELECT /*+ ORDERED */

sql_text

FROM v$sqltext a

WHERE (a.hash_value, a.address) IN (

SELECT DECODE (sql_hash_value,

prev_hash_value,

sql_hash_value),

DECODE (sql_hash_value,

prev_sql_addr,

sql_address)

FROM v$session b

WHERE b.paddr = (SELECT addr

FROM v$process c

WHERE c.spid = 23688))

ORDER BY piece ASC

11.查看表空间使用率

11.1查看表空间使用率

set linesize 160

set pagesize 999

col TABLESPACE_NAME for a18

col TBS_TOTAL_MB for 9999999

col TBS_USED_MB for 9999999

col TBS_FREE_MB for 9999999

col TBS_RATE for a11

col EXTEND_MAX_MB for 9999999999999

col EXTEND_FREE_MB for 9999999999999

col EXTEND_RATE for a11

select a.tablespace_name,

round(current_size / 1024 / 1024, 1) TBS_TOTAL_MB,

round((current_size - b.free_bytes) / 1024 / 1024, 1) TBS_USED_MB,

round(b.free_bytes / 1024 / 1024, 1) TBS_FREE_MB,

round(((current_size - b.free_bytes) / current_size) * 100, 1) || '%' TBS_RATE,

round(a.max_size / 1024 / 1024, 1) EXTEND_MAX_MB,

round((a.max_size - (current_size - b.free_bytes)) / 1024 / 1024, 1) EXTEND_FREE_MB,

round(((current_size - b.free_bytes) / a.max_size) * 100, 1) || '%' EXTEND_RATE

from (select tablespace_name,

sum(ddf.bytes) current_size,

sum(case

when ddf.autoextensible = 'YES' THEN

DDF.MAXBYTES

ELSE

DDF.BYTES

END) max_size

from dba_data_files ddf

group by tablespace_name

union

select tablespace_name,

sum(ddf.bytes) current_size,

sum(case

when ddf.autoextensible = 'YES' THEN

DDF.MAXBYTES

ELSE

DDF.BYTES

END) max_size

from dba_temp_files ddf

group by tablespace_name) a,

(select dfs.tablespace_name, sum(dfs.bytes) free_bytes

from dba_free_space dfs

group by dfs.tablespace_name

union

select tfs.tablespace_name, sum(tfs.BYTES_FREE) free_bytes

from v$TEMP_SPACE_HEADER tfs

group by tfs.tablespace_name) b

where a.tablespace_name = b.tablespace_name(+);

11.2查看表空间使用率

set lines 200

col group_number for 99

col state for a15

col name for a20

col total_gb for 999999.99

col free_gb for 999999.99

col free_percent for 99.99

col care for a5

set linesize 131

set pagesize 200

set termout off

set trimspool on

set serveroutput on

set lines 200 pages 300

col tablespace_name for a30

select u.*,

round(p.avg_use_per_day_mb, 1) avg_used_per_day_mb,

round((max_size_mb - size_used_mb) / p.avg_use_per_day_mb) tbs_exhaust_days,

case

when u.MAX_FREE_RATE

round((max_size_mb - size_used_mb) / p.avg_use_per_day_mb)

'*'

else

null

end care

from (select a.tablespace_name,

b.size_used_mb,

a.data_size_mb,

round(100 - b.size_used_mb / a.data_size_mb * 100) free_rate,

a.max_size_mb,

a.max_size_mb - b.size_used_mb free_mb,

round(100 - b.size_used_mb / a.max_size_mb * 100) max_free_rate

from (select tablespace_name,

round(sum(bytes / 1024 / 1024)) data_size_mb,

round(sum(case

when maxbytes > bytes then

maxbytes

else

bytes

end) / 1024 / 1024) max_size_mb

from dba_data_files

group by tablespace_name) a,

(select tablespace_name,

round(sum(bytes / 1024 / 1024)) size_used_mb

from dba_segments

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name

order by 6 desc, 4 desc) u,

(select name, avg(use_per_day_mb) + 0.0001 avg_use_per_day_mb

from (select x.name,

x.rdate,

(x.used_blocks - lag(x.used_blocks)

over(partition by name order by rdate)) *

y.block_size / 1024 / 1024 use_per_day_mb

from (select to_char(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'),

'yyyy-mm-dd') rdate,

name,

max(tablespace_usedsize) used_blocks

from dba_hist_tbspc_space_usage a, v$tablespace b

where a.tablespace_id = b.ts#

group by to_char(to_date(rtime,

'mm/dd/yyyy hh24:mi:ss'),

'yyyy-mm-dd'),

name

order by name) x,

dba_tablespaces y

where x.name = y.tablespace_name)

where use_per_day_mb is not null

group by name) p

where u.TABLESPACE_NAME = p.name

and u.TABLESPACE_NAME not like '%UNDO%'

and u.TABLESPACE_NAME not like '%TEMP%'

order by 4, 7;

11.3.1查询临时表空间的使用率：

select c.tablespace_name,

to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,

to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,

to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,

to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use

from (select tablespace_name,sum(bytes) bytes

from dba_temp_files

GROUP by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool

GROUP by tablespace_name) d

where c.tablespace_name = d.tablespace_name;

11.3.2查询那些用户在使用

select a.username,

a.sql_id,

a.SEGTYPE,

b.BYTES_USED/1024/1024/1024||'G',

b.BYTES_FREE/1024/1024/1024

from V$TEMPSEG_USAGE a join V$TEMP_SPACE_HEADER b on a.TABLESPACE=b.tablespace_name;

12.找出在使用临时表空间的sql

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

13.重设数据文件大小sql语句

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

column cmd format a120 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

14.创建能显示隐含参数的视图

create or replace view show_hidden_v$parameter

(INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,

ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)

select x.inst_id,

x.indx + 1,

ksppinm,

ksppity,

ksppstvl,

ksppstdvl,

ksppstdf,

decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),

decode(bitand(ksppiflg / 65536, 3),

'IMMEDIATE',

'DEFERRED',

'IMMEDIATE',

'FALSE'),

decode(bitand(ksppiflg, 4),

'FALSE',

decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),

decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),

decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),

decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),

ksppdesc,

ksppstcmnt,

ksppihash

from x$ksppi x, x$ksppcv y

where (x.indx = y.indx);

grant select on show_hidden_v$parameter to user;

15.查看锁表进程SQL语句

set pagesize 999

set line180

col ORACLE_USERNAME for a18

col OS_USER_NAME for a18

col OBJECT_NAME for a24

select sess.sid,

sess.serial#,

lo.oracle_username,

lo.os_user_name,

ao.object_name,

lo.locked_mode

from v$locked_object lo,

dba_objects ao,

v$session sess

where ao.object_id = lo.object_id and lo.session_id = sess.sid;

杀掉锁表进程：

alter system kill session '50,63547' immediate;

批量生成语句

SELECT 'alter system kill session '''||sess.sid || ',' || sess.serial#||''' immediate;'

from v$locked_object lo,

dba_objects ao,

v$session sess

where ao.object_id = lo.object_id and lo.session_id = sess.sid;

16.查看谁阻塞了谁

with vw_lock AS (SELECT * FROM v$lock)

select

a.sid,

'is blocking',

(select 'sid:'||s.sid||' object:'||do.object_name||' rowid:'||

dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

||' sql_id:'||s.sql_id

from v$session s, dba_objects do

where s.sid=b.sid

and s.ROW_WAIT_OBJ# = do.OBJECT_ID

) blockee,

b.sid,b.id1,b.id2

from vw_lock a, vw_lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

17.rman删除归档脚本

crosscheck archivelog all;

delete noprompt expired archivelog all;

delete noprompt archivelog until time 'sysdate-1';

删除log sequence为16及16之前的所有归档日志

delete archivelog until sequence 16;

删除系统时间7天以前的归档日志，不会删除闪回区有效的归档日志

delete archivelog all completed before 'sysdate-7';

删除系统时间1天以内到现在的归档日志

delete archivelog from time 'sysdate-3';

清除所有的归档日志

delete noprompt archivelog all completed before 'sysdate';

清除所有的归档日志

delete noprompt archivelog all;

18监控RMAN操作进度的脚本

REM -------------------------------

REM Script to monitor rman backup/restore operations

REM To run from sqlplus: @monitor ''

REM Example:

--SQL>spool monitor.out

--SQL>@monitor '06-aug-12 16:38:03'

REM where is the start time of your rman backup or restore job

REM Run monitor script periodically to confirm rman is progessing

REM -------------------------------

alter session set nls_date_format='dd-mon-rr hh24:mi:ss';

set lines 1500

set pages 100

col CLI_INFO format a10

col spid format a5

col ch format a20

col seconds format 999999.99

col filename format a65

col bfc format 9

col "% Complete" format 999.99

col event format a40

set numwidth 10

select sysdate from dual;

REM gv$session_longops (channel level)

prompt

prompt Channel progress - gv$session_longops:

prompt

select s.inst_id, -- 实例编号

o.sid, --session_id

CLIENT_INFO ch, --客户端信息

context, --上下文信息

sofar, --已完成工作量

totalwork, --总工作量

round(sofar/totalwork*100,2) "% Complete" --完成进度比

FROM gv$session_longops o, gv$session s

WHERE opname LIKE 'RMAN%'

AND opname NOT LIKE '%aggregate%'

AND o.sid=s.sid

AND totalwork != 0

AND sofar <> totalwork;

REM Check wait events (RMAN sessions) - this is for CURRENT waits only

REM use the following for 11G+

prompt

prompt Session progess - CURRENT wait events and time in wait so far:

prompt

select inst_id,

sid,

CLIENT_INFO ch,

seq#, --最近等待的唯一标识

event, --等待事件

state, --状态(WAITING 、WAITED UNKNOWN TIME、WAITED SHORT TIME 、WAITED KNOWN TIME )

wait_time_micro/1000000 seconds --已经等待的时间

from gv$session where program like '%rman%' and

wait_time = 0 and

not action is null;

REM use the following for 10G

--select inst_id, sid, CLIENT_INFO ch, seq#, event, state, seconds_in_wait secs

--from gv$session where program like '%rman%' and

--wait_time = 0 and

--not action is null;

REM gv$backup_async_io

prompt

prompt Disk (file and backuppiece) progress - includes tape backuppiece

prompt if backup_tape_io_slaves=TRUE:

prompt

select s.inst_id, a.sid, CLIENT_INFO Ch, a.STATUS,

open_time, --文件打开时间

round(BYTES/1024/1024,2) "SOFAR Mb" , --已完成大小

round(total_bytes/1024/1024,2) TotMb, --总大小

io_count, --文件当前发送的IO请求数量

round(BYTES/TOTAL_BYTES*100,2) "% Complete" ,

a.type, --类型 (INPUT, OUTPUT, or AGGREGATE)

filename --文件名

from gv$backup_async_io a, gv$session s

where not a.STATUS in ('UNKNOWN')

and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7;

REM gv$backup_sync_io

prompt

prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

prompt

select s.inst_id, a.sid, CLIENT_INFO Ch,

filename, --文件名

a.type, --类型

a.status, --(NOT STARTED, IN PROGRESS, or FINISHED)

buffer_size bsz, --使用的buffer大小

buffer_count bfc, --使用的buffer 数量

open_time open, --文件被打开的时间

io_count ----文件当前发送的IO请求数量

from gv$backup_sync_io a, gv$session s

where

a.sid=s.sid and

open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') ;

REM -------------------------------

18.检测数据库当前是否有备份操作在执行中

SELECT DECODE(os_backup.backup + rman_backup.backup, 0, 'FALSE', 'TRUE') backup

FROM (SELECT COUNT(*) backup FROM gv$backup WHERE status = 'ACTIVE') os_backup,

(SELECT COUNT(*) backup

FROM gv$session

WHERE status = 'ACTIVE'

AND client_info like '%rman%') rman_backup;

19.RMAN备份脚本

单机环境全备

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=hncdfpos001

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export PATH=$ORACLE_HOME/bin:$PATH

rman target / log /backup/hncdfpos001/rman_full.log append<

run

{allocate channel c1 type disk;

allocate channel c2 type disk;

backup database filesperset 4 format '/backup/hncdfpos001/full_%d_%T_%s_%p';

sql 'alter system archive log current';

backup archivelog all format '/backup/hncdfpos001/arch_%d_%T_%s_%p' delete input;

backup current controlfile format '/backup/hncdfpos001/ctl_%d_%T_%s_%p';

crosscheck backup;

crosscheck archivelog all;

delete noprompt obsolete;

delete noprompt expired backup;

delete noprompt expired archivelog all;

}

EOF

单机环境备归档

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=hncdfpos001

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export PATH=$ORACLE_HOME/bin:$PATH

rman target / log /backup/hncdfpos001/rman_arch.log append<

run

{allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter system archive log current';

backup archivelog all format '/backup/hncdfpos001/arch_%d_%T_%s_%p' delete input;

backup current controlfile format '/backup/hncdfpos001/ctl_%d_%T_%s_%p';

crosscheck backup;

crosscheck archivelog all;

delete noprompt expired backup;

delete noprompt expired archivelog all;

}

EOF

RAC环境全备

run {

allocate channel c1 device type disk connect 'sys/Oracle11@rac101';

allocate channel c2 device type disk connect 'sys/Oracle11@rac102';

backup database database format '/backup/rmanbak/db_full_%T_%d_%t_%s_%p_bak0';

sql 'alter system archive log current';

backup archivelog all format '/backup/rmanbak/arch_%T_%d_%t_%s_%p_bak0' delete input;

backup current controlfile format '/backup/rmanbak/controlfile_%T_%d_%t_%s_%p_bak0';

backup spfile format '/backup/rmanbak/spfile_%T_%d_%t_%s_%p_bak0';

crosscheck backup;

crosscheck archivelog all;

delete noprompt obsolete;

delete noprompt expired backup;

delete noprompt expired archivelog all;

release channel c1;

release channel c2;

}

20.RMAN备份脚本--DataGuard primary

单机环境全备

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=hncdfpos001

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export PATH=$ORACLE_HOME/bin:$PATH

rman target / log /backup/hncdfpos001/rman_full.log append<

run

{allocate channel c1 type disk;

allocate channel c2 type disk;

backup database filesperset 4 format '/backup/hncdfpos001/full_%d_%T_%s_%p';

sql 'alter system archive log current';

backup archivelog all format '/backup/hncdfpos001/arch_%d_%T_%s_%p' ;

backup current controlfile format '/backup/hncdfpos001/ctl_%d_%T_%s_%p';

crosscheck backup;

crosscheck archivelog all;

delete noprompt obsolete;

delete noprompt expired backup;

delete noprompt archivelog all completed before 'sysdate-3';

}

EOF

单机环境备归档

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=hncdfpos001

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export PATH=$ORACLE_HOME/bin:$PATH

rman target / log /backup/hncdfpos001/rman_arch.log append<

run

{allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter system archive log current';

backup archivelog all format '/backup/hncdfpos001/arch_%d_%T_%s_%p' ;

backup current controlfile format '/backup/hncdfpos001/ctl_%d_%T_%s_%p';

crosscheck backup;

crosscheck archivelog all;

delete noprompt expired backup;

delete noprompt archivelog all completed before 'sysdate-3';

}

EOF

RAC环境全备

run {

allocate channel c1 device type disk connect 'sys/Oracle11@rac101';

allocate channel c2 device type disk connect 'sys/Oracle11@rac102';

backup database database format '/backup/rmanbak/db_full_%T_%d_%t_%s_%p_bak0';

sql 'alter system archive log current';

backup archivelog all format '/backup/rmanbak/arch_%T_%d_%t_%s_%p_bak0' ;

backup current controlfile format '/backup/rmanbak/controlfile_%T_%d_%t_%s_%p_bak0';

backup spfile format '/backup/rmanbak/spfile_%T_%d_%t_%s_%p_bak0';

crosscheck backup;

crosscheck archivelog all;

delete noprompt obsolete;

delete noprompt expired backup;

delete noprompt archivelog all completed before 'sysdate-3';

release channel c1;

release channel c2;

}

21.Linux 系统杀Oracle 进程

杀掉进程用此方法比较好，能保证杀得干净，而不是用SQL alter system kill

kill -9 `ps -ef|grep "oracle" |grep "LOCAL=NO"|awk '{print $2}'`

22.Oracle 删除重复数据的几种方法

去重

第一种：distinct

create table tmp_t3 as select distinct * from t3;

drop table t3;

alter table tmp_t2 rename to t3;

-- 第二种，用rowid

delete from t2

where rowid <>( select min(rowid)

from t2 b

where b.c1 = t2.c1

and b.c2 = t2.c2 )

---第三种，用rowid + group by 的方法

delete from T2

where rowid not in (select min(rowid)

from t2 group by c1,c2 );

delete from t2

where not exists (select 1 from (select min(rowid) rid from t2 group by c1,c2) b where b.rid=t2.rowid)

---第四种，用分析函数

delete from t2 where rowid in

(select b.rd from

(select rowid rd,row_number() over(partition by c1,c2 order by c1) rn

from t2) b

where b.rn > 1);

22.抓出必须创建索引的列

select column_name,

num_rows,

Cardinality,

selectivity,

histogram,

num_buckets, 'Consider create index on this column' as notice

from (select a.column_name,

b.num_rows,

a.num_distinct Cardinality,

round(a.num_distinct / b.num_rows * 100, 2) selectivity,

a.histogram,

a.num_buckets

from dba_tab_col_statistics a, dba_tables b

where a.owner = b.owner

and a.table_name = b.table_name

and a.owner = 'SCOTT'

and a.table_name = 'TEST')

where selectivity >= 10

and column_name not in (select column_name

from dba_ind_columns

where table_owner = 'SCOTT'

and table_name = 'TEST')

and column_name in

(select c.name

from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r

where o.obj# = u.obj#

and c.obj# = u.obj#

and c.col# = u.intcol#

and r.name = 'SCOTT'

and o.name = 'TEST');

23.Oracle 回收站功能,彻底删除表

Oracle 10g 中出现表名：BIN$2cMp4FjwQ2Cw3Lj+BxLYTw==$0 最近发现Oracle中出现了这些奇怪的表名，

上网查找后发现是oracle10g的回收站功能，并没有彻底的删除表，而是把表放入回收站，最后就出现了这样一堆奇怪的表名。。。。

23.1清除的方法如下：

1、purge table origenal_tableName;

purge index origenal_indexName;

2、PURGE recyclebin;

23.2查询垃圾信息，可以用如下SQL语句：

set linesize 120;

set pagesize 999;

col OBJECT_NAME for a40;

col ORIGINAL_NAME for a40;

dba用户查询

SELECT t.object_name,t.type ,t.original_name FROM dba_recyclebin t;

当前用户查询

SELECT t.object_name,t.type ,t.original_name FROM user_recyclebin t;

23.3删除Table不进入Recycle的方法：

drop table tableName purge;

24.查看oracle数据库里面正在执行的sql进度

select se.sid,

opname,

trunc(sofar / totalwork * 100, 2) || '%' as pct_work,

elapsed_seconds elapsed,

round(elapsed_seconds * (totalwork - sofar) / sofar) remain_time,

sql_text

from v$session_longops sl, v$sqlarea sa, v$session se

where sl.sql_hash_value = sa.hash_value

and sl.sid = se.sid

and sofar != totalwork

order by start_time;

25.查看oracle非系统新建用户

select username

from dba_users

where username not in ('SYS',

'SYSTEM',

'OUTLN',

'FLOWS_FILES',

'MDSYS',

'ORDSYS',

'EXFSYS',

'DBSNMP',

'WMSYS',

'APPQOSSYS',

'APEX_030200',

'ORDDATA',

'CTXSYS',

'ANONYMOUS',

'XDB',

'ORDPLUGINS',

'SI_INFORMTN_SCHEMA',

'OLAPSYS',

'ORACLE_OCM',

'XS$NULL',

'MDDATA',

'DIP',

'APEX_PUBLIC_USER',

'SPATIAL_CSW_ADMIN_USR',

'SPATIAL_WFS_ADMIN_USR',

'SCOTT',

'SYSMAN',

'MGMT_VIEW');

腻子防水涂装设备厂家

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫