Oracle 练习用（1）

desc v$logfile;
select * from v$logfile;

select * from v$controlfile;

select * from v$datafile;


select * from all_tables where OWNER='ADMIN';

-- Find large process
SELECT
L.sid ssid,
substr(OPNAME,1,15) opname,
target,
trunc((sofar/totalwork)*100) pct,
to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9999.0')
Rate,
elapsed_seconds/60 es,
time_remaining/60 tr,
PROGRAM,
MACHINE
FROM v$session_longops L,v$session S
WHERE time_remaining > 0 AND L.SID=S.SID
ORDER BY start_time;

select * from user_views;

select * from user_indexes;
select * from user_ind_columns ;

select * from MBR_SECEDE_MST;

--rename the system auto-index name
alter index SYS_C005162 rename to USER_INFO_PK;

--1 shared pool
select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache; --share sql lib cache, If < 90% then must make large the shareed pool

select (sum(gets -getmisses - usage -fixED)) / sum(gets) "Row Cache" from v$rowcache; -- If < 90% then ...

--2 data cache
select name, value from v$sysstat where name in('db block gets','consistent gets','physical reads'); --If (1 - db block gets/(consistent gets + physical reads))<90% then ...

--3 log cache
select name, value from v$sysstat where name in('redo entries','redo log space requests'); --If  requests/entries is not be closed to 0 then ...