计算DB的总容量
select round(sum(space)) all_space_M
from (select sum(bytes) / 1024 / 1024 space
from dba_data_files
union all
select nvl(sum(bytes) / 1024 / 1024, 0) space
from dba_temp_files
union all
select sum(bytes) / 1024 / 1024 space from v$log);
获取终端的字符集设置
SQL> select userenv('language') from dual;
查看控制文件SQL> select name from v$controlfile;
查看联机日志文件
SQL> select group#,member from v$logfile;
查看数据文件SQL> col tablespace_name for a20
SQL> col file_name for a45
SQL> select tablespace_name,file_name from dba_data_files;
查看临时文件SQL> select tablespace_name,file_name from dba_temp_files;
查看当前session的process id和trace文件
SQL> select spid,tracefile from v$process where addr in
(select paddr from v$session where sid =
(select distinct sid from v$mystat));
查看数据库正在做什么?
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
SID,
MACHINE,
REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM V$SESSION SES,
V$SQLtext_with_newlines SQL
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1,sql.piece;
查询正在执行的SCHEDULER_JOBselect owner,job_name,
sid,b.SERIAL#,b.username,spid
from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process
where session_id=sid and
paddr=addr
查询正在执行的dbms_jobselect job,b.sid,b.SERIAL#,b.username,spid
from DBA_JOBS_RUNNING a ,v$session b,v$process
where a.sid=b.sid and paddr=addr
确定系统默认临时表空间
SQL> select property_name,property_value
from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
强制系统更新检查点
SQL> alter system checkpoint;
EM改为emctl unsecure dbconsole后,打开IE通过http的方式,可以正常登录了。
$ emctl unsecure dbconsole
添加redo log
alter database add logfile group 5 ‘/u01/app/oracle/oradata/orcl/redo5’ size 52M;
alter database add logfile member ‘/u01/app/oracle/oradata/orcl/redo4_3.log’ to group 4;
alter database drop logfile
alter database drop logfile member
检查归档切换的统计信息set lines 200 pages 999
col date for a10
col 00 for 999
col 01 for 999
col 02 for 999
col 03 for 999
col 04 for 999
col 05 for 999
col 06 for 999
col 07 for 999
col 08 for 999
col 09 for 999
col 10 for 999
col 11 for 999
col 12 for 999
col 13 for 999
col 14 for 999
col 15 for 999
col 16 for 999
col 17 for 999
col 18 for 999
col 19 for 999
col 20 for 999
col 21 for 999
col 22 for 999
col 23 for 999
select to_char(first_time,'yyyy-mm-dd') "date",
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23"
from v$log_history
group by to_char(first_time,'yyyy-mm-dd');
列出用户,状态,表空间和角色
select username,
ACCOUNT_STATUS,
default_tablespace,
temporary_tablespace,
granted_role
from dba_users u,dba_role_privs r
where u.username = r.grantee
order by username
列出非sys,system的DBA用户
select * from dba_role_privs
where grantee in
( select username from dba_users
where account_status='OPEN' and
username not in ('SYS','SYSTEM')) and
granted_role='DBA'
order by grantee;
查看会话正在执行的SQL和上一次执行的SQL,(v$session,v$sql,v$process)
select s.username,s.sid,s.serial#,spid,sql_text
from v$session s,v$sql q,v$process p
where s.PADDR = p.ADDR
and (s.SQL_ID=q.sql_id or s.PREV_SQL_ID=q.sql_id)
and sid=72;
select s.username,s.sid,s.serial#,spid,
DECODE(q.sql_id,s.SQL_ID, 'Curruct sql is :'|| q.sql_text,s.PREV_SQL_ID,'Last sql is :'|| q.sql_text) "SQL Text"
from v$session s,v$sql q,v$process p
where s.PADDR = p.ADDR
and (s.SQL_ID=q.sql_id or s.PREV_SQL_ID=q.sql_id)
and sid=72;
查看索引被索引的字段
select * from user_ind_columns where index_name=upper('SYS_C0011435')
检查某个表的区ID,块ID和块个数SQL> select extent_id,block_id,blocks
from dba_extents
where segment_name='EMP' order by extent_id;
查看当前的SCN
SQL> select timestamp_to_scn(sysdate) from dual;
SQL> select CURRENT_SCN from v$database;
手工删除数据库SQL> startup mount
SQL> alter system enable restricted session;
SQL> drop database;
删除表空间的同时删除数据文件SQL> drop tablespace including contents and datafiles tbs_4;
mount状态下删除一个丢失数据文件的表空间connect / as sysdba;
startup mount;
alter database datafile '丢失的DBF数据文件' offline drop;
alter database open;
drop tablespace...
sqlplus强制登陆数据库DB
sqlplus -prelim / as sysdba
sqlplus -prelim '/ as sysdba'
查看系统中硬解析,软解析的统计数据
select s.name, m.value
from v$statname s, v$mystat m
where s.statistic# = m.statistic#
and s.name like 'parse%(%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 12
parse count (hard) 5
parse count (failures) 0
parse count (describe) 0
授予普通用户访问sys schema 中的数据字典视图
grant select any dictionary to testdic;
查询非默认参数
SELECT INST_ID,
NAME,
VALUE,
DESCRIPTION,
ISDEPRECATED
FROM gv$system_parameter
WHERE ISDEFAULT = 'FALSE'
ORDER BY NAME,
INST_ID;
确定正在运行和将来运行的调度作业
SELECT owner,
job_name,
enabled,
state,
TO_CHAR(NEXT_RUN_DATE,'YYYY-MON-DD HH24:MI:SS') next_run
FROM dba_scheduler_jobs
WHERE STATE != 'DISABLED'
ORDER BY 1,2
列出所有分区表
select OWNER,
TABLE_NAME,
TABLESPACE_NAME,
logging,
partitioned,
owner sdev_link_owner,
table_name sdev_link_name,
'TABLE' sdev_link_type
from sys.dba_tables
where table_name not like 'BIN$%'
and partitioned = 'YES'
and owner not in ('SYS','SYSTEM')
order by 1, 2
oracle显示长事务,长事务会在v$session_longops表中可以查到! set linesize 200
set pagesize 5000
col transaction_duration format a45
with transaction_details as
( select inst_id
, ses_addr
, sysdate - start_date as diff
from gv$transaction
)
select s.username
, to_char(trunc(t.diff))
|| ' days, '
|| to_char(trunc(mod(t.diff * 24,24)))
|| ' hours, '
|| to_char(trunc(mod(t.diff * 24 * 60,24)))
|| ' minutes, '
|| to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
|| ' seconds' as transaction_duration
, s.program
, s.terminal
, s.status
, s.sid
, s.serial#
from gv$session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc
/
重建EMemca -config dbcontrol db -repos recreate