- 浏览: 1763016 次
- 性别:
- 来自: 成都
文章分类
- 全部博客 (520)
- Oracle (10)
- Oracle错误集 (8)
- Oracle安装升级 (15)
- Oracle日常管理 (51)
- Oracle字符集 (7)
- Oracle备份恢复 (24)
- Oracle优化 (4)
- Oracle编程 (52)
- Oracle导入导出 (19)
- Oracle体系结构 (15)
- Oracle网络 (2)
- Oracle安全 (2)
- Oracle权限 (3)
- Oracle数据字典和性能视图 (2)
- Oracle常用地址 (5)
- SQLPLUS专栏 (7)
- SqlServer (13)
- SqlServer2005编程 (27)
- SqlServer2005管理 (15)
- MySQL (20)
- Dorado应用 (1)
- C# (24)
- Arcgis Server开发 (20)
- ArcSDE技术 (19)
- UML学习 (2)
- 设计模式 (2)
- JAVA EE (4)
- JavaScript (3)
- OFBIZ (27)
- JAVA WEB开发 (22)
- Linux&Unix (34)
- SHELL编程 (14)
- C语言 (11)
- 网络协议 (14)
- FREEMARKER (2)
- GROOVY (2)
- JAVA语言 (3)
- 防火墙 (0)
- PHP (2)
- Apache (2)
- Loader Runner (1)
- Nginx (3)
- 数据库理论 (2)
- maven (1)
最新评论
-
怼怼怼怼:
oracle的timestamp类型使用 -
怼怼怼怼:
oracle的timestamp类型使用 -
怼怼怼怼:
oracle的timestamp类型使用 -
pg_guo:
感谢
oracle中查看用户权限 -
xu234234:
5、MapResourceManager控件中添加了两个服务, ...
北京ArcGis Server应用基础培训笔记1
rem 这需要 统计某个具体用户的"Table,index,column,constraits"
rem================================================================
rem 全部表-列定义 table_cols.txt
set lin 110 pages 3000
column table_name format a30
column data_type format a12
column data_default format a8
column column_name format a22
column Cid format 999
column Len format 9999
column Prec format 99
column Scale format 99
select TABLE_NAME, COLUMN_ID "Cid", COLUMN_NAME, DATA_TYPE, DATA_LENGTH "Len",
nvl(DATA_PRECISION,'-1') "Prec", nvl(DATA_SCALE,'-1') "Scale",
NULLABLE, DATA_DEFAULT
from USER_TAB_COLUMNS ;
rem ======== TAB =============
select * from tab;
spool off
spool user_indexes.txt
rem================================================================
rem 全部索引定义user_index.txt
column table_name format a22
column index_name format a28
column index_type format a7
column column_name format a18
column # format 99
column Init format 999999;
select a.table_name, t.cache "C",a.index_name,
column_position "#",column_name,
UNIQUENESS,a.INITIAL_EXTENT/1024 "Init"
from user_indexes a, user_ind_columns c,user_tables t
where c.INDEX_NAME =a.INDEX_NAME
and a.table_name= t.table_name
order by a.table_name,a.index_name,column_position;
spool off
spool user_Obj_Table_Index.txt
rem================================================================
rem 用户对象,表和索引userObj_Table_Index.txt
set lin 111 pages 333
column table_name format a24
column index_name format a32
column tablespace_name a12
column Init format 999999;
rem 由于用户要关心的是我自己的详细数据的存放位置,下面分别得出index,tables
select tablespace_name,table_name,cache,initial_extent/1024 "Init"
from user_tables order by tablespace_name,table_name;
select tablespace_name,table_name,index_name,initial_extent/1024 "Init"
from user_indexes order by tablespace_name,table_name,index_name;
spool off
spool user_constraints.txt
rem================================================================
rem 全部表-列约束_user_constraints.txt
column CONSTRAINT_NAME format a30
column TABLE_NAME format a30
column r_CONSTRAINT_NAME format a20
select CONSTRAINT_NAME,
CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, DELETE_RULE
from user_constraints
order by CONSTRAINT_TYPE,TABLE_NAME;
spool off
spool c:\user_index1rebld.sql
rem================================================================
rem 重建全部索引
rem select 'alter index '||index_name||' rebuild;' from user_indexes
rem where table_name = 'GWNEWS';
select 'alter index '||INDEX_NAME||' rebuild tablespace indexes;' from user_indexes;
spool off
rem @index1rebld.sql
spool c:\user_sources.sql
rem================================================================
rem 全部代码,主要是procedure
column name format a22 ;
column text format a77;
break on name;
select text,name from user_source;
spool off
rem =============== End of File ==================
*************查看当前用户使用的操作****************************
SELECT a.machine,a.terminal,a.osuser, a.username, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address =b.address order by b.address
***************************************************
1. 监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_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 c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率,应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9.显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
10.监控 SGA 中重做日志缓存区的命中率,应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
11.监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
12.监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13.监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
14.找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时,参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时,参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
18、找使用CPU多的用户session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
19、查看Lock
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,
'990'))) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
select l.sid,s.serial#,s.username,s.terminal,
decode(l.type,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',l.type||'System') res,
substr(t.name,1,10) tab,u.name owner,
l.id1,l.id2,
decode(l.lmode,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) lmode,
decode(l.request,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) request
from v$lock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid
and s.type != 'BACKGROUND'
and t.obj# = l.id1
and u.user# = t.owner#
--第二条语句比较有效。
20、显示表空间的使用情况:
col tsname format a16 justify c heading 'Tablespace'
col nfrags format 999,990 justify c heading 'Free|Frags'
col mxfrag format 999,999,990 justify c heading 'Largest|Frag (KB)'
col totsiz format 999,999,990 justify c heading 'Total|(KB)'
col avasiz format 999,999,990 justify c heading 'Available|(KB)'
col pctusd format 990 justify c heading 'Pct|Used'
select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from
dba_data_files total,
dba_free_space free
where
total.tablespace_name = free.tablespace_name(+)
group by
total.tablespace_name,
total.bytes;
rem================================================================
rem 全部表-列定义 table_cols.txt
set lin 110 pages 3000
column table_name format a30
column data_type format a12
column data_default format a8
column column_name format a22
column Cid format 999
column Len format 9999
column Prec format 99
column Scale format 99
select TABLE_NAME, COLUMN_ID "Cid", COLUMN_NAME, DATA_TYPE, DATA_LENGTH "Len",
nvl(DATA_PRECISION,'-1') "Prec", nvl(DATA_SCALE,'-1') "Scale",
NULLABLE, DATA_DEFAULT
from USER_TAB_COLUMNS ;
rem ======== TAB =============
select * from tab;
spool off
spool user_indexes.txt
rem================================================================
rem 全部索引定义user_index.txt
column table_name format a22
column index_name format a28
column index_type format a7
column column_name format a18
column # format 99
column Init format 999999;
select a.table_name, t.cache "C",a.index_name,
column_position "#",column_name,
UNIQUENESS,a.INITIAL_EXTENT/1024 "Init"
from user_indexes a, user_ind_columns c,user_tables t
where c.INDEX_NAME =a.INDEX_NAME
and a.table_name= t.table_name
order by a.table_name,a.index_name,column_position;
spool off
spool user_Obj_Table_Index.txt
rem================================================================
rem 用户对象,表和索引userObj_Table_Index.txt
set lin 111 pages 333
column table_name format a24
column index_name format a32
column tablespace_name a12
column Init format 999999;
rem 由于用户要关心的是我自己的详细数据的存放位置,下面分别得出index,tables
select tablespace_name,table_name,cache,initial_extent/1024 "Init"
from user_tables order by tablespace_name,table_name;
select tablespace_name,table_name,index_name,initial_extent/1024 "Init"
from user_indexes order by tablespace_name,table_name,index_name;
spool off
spool user_constraints.txt
rem================================================================
rem 全部表-列约束_user_constraints.txt
column CONSTRAINT_NAME format a30
column TABLE_NAME format a30
column r_CONSTRAINT_NAME format a20
select CONSTRAINT_NAME,
CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, DELETE_RULE
from user_constraints
order by CONSTRAINT_TYPE,TABLE_NAME;
spool off
spool c:\user_index1rebld.sql
rem================================================================
rem 重建全部索引
rem select 'alter index '||index_name||' rebuild;' from user_indexes
rem where table_name = 'GWNEWS';
select 'alter index '||INDEX_NAME||' rebuild tablespace indexes;' from user_indexes;
spool off
rem @index1rebld.sql
spool c:\user_sources.sql
rem================================================================
rem 全部代码,主要是procedure
column name format a22 ;
column text format a77;
break on name;
select text,name from user_source;
spool off
rem =============== End of File ==================
*************查看当前用户使用的操作****************************
SELECT a.machine,a.terminal,a.osuser, a.username, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address =b.address order by b.address
***************************************************
1. 监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_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 c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率,应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9.显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
10.监控 SGA 中重做日志缓存区的命中率,应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
11.监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
12.监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13.监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
14.找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时,参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时,参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
18、找使用CPU多的用户session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
19、查看Lock
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,
'990'))) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
select l.sid,s.serial#,s.username,s.terminal,
decode(l.type,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',l.type||'System') res,
substr(t.name,1,10) tab,u.name owner,
l.id1,l.id2,
decode(l.lmode,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) lmode,
decode(l.request,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) request
from v$lock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid
and s.type != 'BACKGROUND'
and t.obj# = l.id1
and u.user# = t.owner#
--第二条语句比较有效。
20、显示表空间的使用情况:
col tsname format a16 justify c heading 'Tablespace'
col nfrags format 999,990 justify c heading 'Free|Frags'
col mxfrag format 999,999,990 justify c heading 'Largest|Frag (KB)'
col totsiz format 999,999,990 justify c heading 'Total|(KB)'
col avasiz format 999,999,990 justify c heading 'Available|(KB)'
col pctusd format 990 justify c heading 'Pct|Used'
select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from
dba_data_files total,
dba_free_space free
where
total.tablespace_name = free.tablespace_name(+)
group by
total.tablespace_name,
total.bytes;
发表评论
-
Linux下开机自动启动Oracle的设置
2015-01-13 16:17 1507具体方法如下: 1. 修改oratab (root用户执 ... -
删除表空间
2014-07-14 09:19 976-删除空的表空间,但是不包含物理文件 drop table ... -
oracle删除外键约束-禁用约束-启用约束
2014-06-06 16:49 1559oracle删除外键约束-禁用约束-启用约束 执行 ... -
如何删除带有外键关系的主表,并快速重建相应的外键关系 ref_constraint
2014-06-06 16:30 11431,备份主表数据。可以用 CREATE TABLE AS S ... -
oracle中修改表字段sql语句
2014-05-15 18:09 1538oracle中修改表字段常用语句如下: 1、重命名表 AL ... -
oracle中查询所有外键引用到某张表的记录
2014-05-14 14:40 1325分为两步: 1.查询表的主键约束名 select * ... -
Oracle 创建 DBLink 的方法
2014-05-06 10:01 8941、如果需要创建全局 DBLink,则需要先确定用户有创建 ... -
CREATE TABLESPACE命令详解
2014-04-22 15:43 1455语法描述: CREATE [UNDO] TABLESPA ... -
SGA和PGA推荐配置
2014-01-23 09:07 1279查询SGA和PGA: SQL> show para ... -
oracle用户LANG和NLS_LANG环境变量设置
2014-02-14 14:14 1697export LANG=en_US.UTF-8 NLS_ ... -
几个常用的数据库操作以及视图
2014-02-19 16:48 1597一.查看表空间剩余百分比 SELECT tablespa ... -
orapwd 工具建立密码文件遵守的命名方法
2014-02-19 16:48 918orapwd 工具建立建立的密码文件一定要orapw+实例 ... -
oracle删除数据文件
2014-02-19 16:48 3688在我们详细介绍之前,我们必须说清楚一点:Oracle不提供 ... -
Oracle Undo表空间介绍
2014-02-22 08:53 1477UNDO表空间的作用和管 ... -
ALTER DATABASE 与 ALTER TABLESPACE OFFLINE的区别
2014-02-22 08:52 1043一.DataFile脱机或联机的两种方法: ① ALTE ... -
获取表空间、用户和外键的DDL语句
2013-10-24 16:22 932工作中,需要现场返回所有表空间及用户信息 get_ddl ... -
利用dbms_metadata.get_ddl查看DDL语句
2013-10-24 16:20 1254本文转载,原文地址为:http://czmmiao.itey ... -
临时表空间数据文件的迁移方法
2013-04-28 15:27 1453对于临时表空间数据文件的迁移方法如下:alter tables ... -
表空间数据文件移动或改名操作
2013-04-24 10:23 1132sqlplus /nologSQL> conn /as ... -
Duties of Oracle Database Administrators
2013-03-17 22:47 934The principal responsibility o ...
相关推荐
综上所述,"Oracle 常用脚本.zip"这个压缩包可能涵盖了Oracle数据库管理的各个方面,是数据库管理员和开发人员日常工作中不可或缺的工具集合。通过理解和应用这些脚本,我们可以更高效、更专业地管理和维护Oracle...
综上所述,Oracle DBA的常用脚本涵盖了完整性约束检查、索引检查、锁资源监控、数据库对象建立、性能监控和权限管理等多个方面。这些脚本帮助DBA更好地理解和管理数据库,确保其高效、稳定地运行。在接手新的数据库...
首先,Oracle常用脚本1.doc可能包含的是数据库的创建、初始化和管理脚本。在Oracle中,创建一个数据库需要执行一系列步骤,如定义参数文件(parameter file)、创建控制文件(control file)以及数据文件(datafile...
监控Oracle数据库的常用shell脚本是DBA日常工作中不可或缺的一部分。本文将分享8个常用的shell脚本,涵盖了数据库实例的可用性、监听器的可用性、表空间的使用情况、无效对象的检查等多个方面。 一、监控Oracle...
在Oracle数据库管理中,SQL(Structured Query Language)脚本扮演着至关重要的角色,尤其是在日常维护、性能监控和问题排查方面。以下是一些Oracle管理中常用的SQL脚本及其相关的知识点: 1. **数据查询与操作**:...
从给定的文件信息来看,主要涉及的是Oracle数据库的管理和优化相关的SQL脚本与知识点,以下将对这些知识点进行详细的解读和扩展。 ### Oracle数据库I/O统计查询 第一条SQL脚本用于统计Oracle数据库中的I/O操作,...
### ORACLE DBA常用脚本解析与应用 在Oracle数据库管理与优化中,熟练掌握一系列脚本对于提高数据库性能、解决复杂问题至关重要。以下是对给定脚本的深入解析及其实用场景说明。 #### 1. 监控系统资源与性能 - **...
### Oracle DBA 数据库日常维护手册:常用SQL脚本解析 在IT行业中,数据库的高效稳定运行对于企业的业务连续性和数据安全至关重要。Oracle作为全球领先的数据库管理系统之一,其日常维护工作是确保系统性能、安全与...
二、Oracle常用脚本 1. 表空间和数据文件管理:创建、扩展或删除表空间,调整数据文件大小的SQL脚本,如CREATE TABLESPACE、ALTER DATABASE ADD DATAFILE和DROP TABLESPACE等。 2. 用户和权限管理:创建用户、赋予...
"Oracle常用SQL脚本"集合通常包含了一系列预编写好的SQL语句或存储过程,以帮助数据库管理员和开发人员高效地完成日常任务。这些脚本可能涉及数据库性能监控、数据备份与恢复、用户权限管理、表结构操作等多个方面。...
"oracle常用脚本"这个压缩包文件包含了超过300个实用的SQL脚本,是DBA工作中的宝贵资源。这些脚本涵盖了Oracle数据库的多个方面,包括但不限于数据查询、表管理、索引操作、用户权限管理、性能监控等。下面将详细...
oracle常用维护管理工具电子书 1:Oracle数据库日常维护;2:Oracle警告日志文件监控;3Oracle数据库表空间使用情况监控;4:控制文件备份;5:Oracle定时作业;6:制作脚本
监控 Oracle 数据库的常用 Shell 脚本是 DBA 在日常工作中不可或缺的工具。这些脚本可以帮助 DBA 快速地检查 Oracle 数据库的可用性、监听器的可用性、日志文件中的错误信息、表空间的使用情况、无效的对象等。 一...
在数据库管理领域,DBA...老外提供的"DBA常用脚本"可能包含上述部分或全部内容,这些脚本可以帮助DBA高效地完成日常任务,提高工作效率,同时减少人为错误。在使用时,务必根据自己的数据库环境和需求进行适当的调整。
"Oracle管理核心脚本"集合了DBA日常工作中常用的SQL脚本,这些脚本涵盖了数据库的多个方面,如性能监控、空间管理、用户权限控制等。下面将详细介绍这些关键知识点: 1. **性能监控**: - SQL脚本可以用于监控...
ORACLE DBA 日常维护数据库使用的SQL脚本 在Oracle数据库运行期间 DBA应该对数据库的运行日志及表空间的使用 情况进行监控及早发现数据库中存在的问题
在日常的数据库管理工作中,Oracle数据库管理员(DBA)面临着各种挑战,包括系统性能监控、故障排查、数据备份与恢复等任务。为了有效地完成这些工作,掌握一定的SQL脚本是必不可少的。下面我们将详细解读《Oracle_...
以下是一些关于Oracle DBA常用脚本的知识点: 1. 完整性约束检查: - 完整性约束是数据库设计的重要部分,包括主键、外键等,确保数据的一致性和准确性。 - 外键约束对性能可能有影响,因为它们会导致行级锁定,...