常规sql的使用总结（陆续补充）

最新推荐文章于 2024-09-29 08:44:52 发布

cuanbing5840

最新推荐文章于 2024-09-29 08:44:52 发布

阅读量102

点赞数

文章标签：数据库

//创建临时表空间

create temporary tablespace mahanso_temp
tempfile 'E:\oracle\product\10.2.0\oradata\testserver\mahanso_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建数据表空间
create tablespace mahanso_data
logging
datafile 'E:\oracle\product\10.2.0\oradata\testserver\mahanso_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

--设置表空间最大大小

ALTER DATABASE DATAFILE 'E:\oracle\product\10.2.0\oradata\testserver\mahanso_data01.dbf'

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M

--创建用户
create user mahanso_new identified by mahanso_new
default tablespace mahanso_data
temporary tablespace mahanso_tmp;

--赋普通用户权限

grant

resource,connect,RECOVERY_CATALOG_OWNER,

debug any procedure, debug connect session

to mahanso_test;

--带有物化视图的用户授权：

grant

UNLIMITED TABLESPACE,CREATE ANY MATERIALIZED VIEW ,

SELECT ANY TABLE,ON COMMIT REFRESH

to mahanso_test;

--修改用户密码

alter user user01 identified by user10;

--赋值连接和开发权限
grant connect,resource to mahanso_new;

--准许使用表空间

ALTER USER QUOTA UNLIMITED ON TABLESPACENAME

--赋值debug权限

GRANT debug any procedure, debug connect session TO USERNAME

--取消用户权限

revoke dba from mahanso;

--给用户赋增删改查的权限：　

grant select/insert/update/delete any table to 用户名

--查看数据文件和临时数据文件和日志文件

SELECT NAME FROM V$DATAFILE

UNION ALL

SELECT NAME FROM V$TEMPFILE

UNION ALL

SELECT MEMBER FROM V$LOGFILE;

--角色赋权：

A>创建角色：create role role1; B>给角色赋权：grant select any talble to role1;(grant create any table,create procedure to role1;) C>把角色赋给用户b: grant role1 to b;

--批处理：

查询a的所有权限：select 'grant select on IFSAPP.'||t.object_name||' to IFSERP;' from all_all_tables t where t.owner= 'IFSAPP';, 然后把这个执行即可;

--dba所有表查询权限赋给b:谨慎使用，可能会不安全；

--删除session的链接

SELECT 'alter system kill session '''||sid||','||serial#||''';' FROM v$session WHERE username='USER';

--删除用户下所有的表
drop user mahanso_new cascade;

--查看oracle表空间是否自动扩展

select file_name,autoextensible,increment_by from dba_data_files

--删除表空间
DROP TABLESPACE mahanso_data INCLUDING CONTENTS AND DATAFILES;

--删除临时表空间
DROP TABLESPACE mahanso_tmp INCLUDING CONTENTS AND DATAFILES;

--修改表空间大小

ALTER Database datafile '/oracle/oradata/ora11/mahanso_data.dbf' Resize 4500M;

--修改临时表空间大小

ALTER Database Tempfile '/oracle/oradata/ora11/mahanso_tmp.dbf' Resize 4500M;

--修改用户临时表空间

alter user temporary tablespace new_tbs;

--增加数据文件

alter tablespace EMALL_DAT add datafile '/oracle/oradata/tianj/tianj/emall_dat_03.dbf'

size 800M autoextend

on next 50M maxsize 1000M;

--更改自动扩展属性

alter database datafile

''/oracle/oradata/ora11/mahanso_data.dbf' ,

'/oracle/oradata/ora11/mahanso_data02.dbf',

'/oracle/oradata/ora11/mahanso_data03.dbf'

autoextend off;

--修改表的表空间和对应的索引

1、先找到这哪些表的表空间不对。

select * from dba_tables where tablespace_name='TDB';

2、将表空间在 TDB 中的移到表空间 TDB2009 中

语法是： alter table table_name move tablespace tablespace_name;

alter table tdb2009.ASSOC_INFO move tablespace TDB2009;

alter table tdb2009.BGUSERPOPD move tablespace TDB2009;

select 'alter index TDB2009.'||index_name||' rebuild online nologging tablespace TDB2009;' from user_indexes

怎么在oracle中，把一个表空间的所有索引换到另一个表空间呢？

select 'alter index ' || index_name || ' move tablespace TDB2009;' from user_indexes

--创建只读用户

Create user query Identified By mahansooquery; --创建用户

GRANT SELECT ANY DICTIONARY TO query; --授予所有字典的查询权限

GRANT SELECT ANY SEQUENCE TO query; --授予所有序列的查询权限

GRANT SELECT ANY TABLE TO query; --授予所有表的查询权限

GRANT CREATE SESSION TO query; --授予创建会话

GRANT SELECT ANY VIEW TO query; --授予所有视图的查询权限

--创建访问特定表用户

create user mahanso identified by mahanso;

grant connect to mahanso;

grant select on mahanso.CYO_CRM_USER to query; --这里实现想给他权限查询的表

grant select on mahanso.CYO_CUSTOMER to query; --这里实现想给他权限查询的表

--查看闪回是否开启

select log_mode,open_mode,flashback_on from v$database;

--查看表空间使用情况

方法一、

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4 DESC;

方法二、

SELECT fs.tablespace_name "Tablespace",

(df.totalspace - fs.freespace) "Used MB",

fs.freespace "Free MB",

df.totalspace "Total MB",

ROUND (100 * (fs.freespace / df.totalspace)) "Pct. Free"

FROM (SELECT tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024)

totalspace

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024) freespace

FROM dba_free_space

GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name;

方法三、

select b.file_name FileName, b.tablespace_name "Tablespace",

round(b.bytes / 1024 / 1024 / 1024, 2) "SpaceSize(G)",

round((b.bytes - sum( nvl( a.bytes,0))) / 1024 / 1024 / 1024, 2) "Used(G)",

round(substr((b.bytes - sum( nvl( a.bytes , 0))) / ( b.bytes) * 100 , 1, 5), 2) "Used(%)"

from dba_free_space a, dba_data_files b

where a.file_id = b.file_id

group by b.tablespace_name, b.file_name, b.bytes

order by b.tablespace_name;

--查看表空间是否具有自动扩展的能力

--exp、imp方案

方法一

exp mahanso/asj file=d:/test.dmp log=d:/test.log query=\"where rownum < 5\" tables=users,result

注意：a、tables是你要导出的表名，多表用","分隔。exp只能这样指定，因为只有在表的模式下面才能按区间导出。

b、由于有字符串解析问题，容易出现报错现象(query=\"where rownum < 5\")

方法二

用parfile文件代替exp、imp参数，建立".par"文件内容如下：

file=d:/test.dmp

log=d:/test.log

query="where rownum < 4"

tables=users,result

在命令行中执行即可

exp mahanso/asj parfile=D:\new_db\111.par

使用范围：所有oracle数据库版本

--expdp和impdp方案

方法一、

导出数据库所有表的前10行：

expdp mahanso/asj dumpfile=mahanso_dir:teset32211111.dmp logfile=mahanso_dir:test2.log query=\"where rownum < 10\"

导出数据库特定表前10行：

expdp mahanso/asj dumpfile=mahanso_dir:teset32211111.dmp logfile=mahanso_dir:test2.log query=users:\"where rownum < 10\"

注意：a、也有tables参数，但是全表导出前10行不用指定tables参数

b、由于有字符串解析问题，容易出现报错现象,"\"分隔符必须使用，解析字符串

方法二

用parfile文件代替expdp、impdp参数，建立".par"文件内容如下：

directory=mahanso_dir

dumpfile=tabs_20090225.dmp

nologfile=yes

query="where rownum <=3"

在命令行中执行即可

expdp mahanso/asj parfile=D:\new_db\test.par

使用范围：10g以上数据库

具体expdp、impdp使用方法如下：

--Data pump操作准备

当使用expdp时，以下一系列操作用来确定写文件的位置：

在expdp命令行中dumpfile参数规范的一部分指定了oracle写文件的目录，但操作前要先创建目录和赋予read权限；

create directory pump_dir as 'c:\oracle\pump_dir';

grant read on directory pump_dir to XXXX；

如果不以sysdba用户登录，那么为了能从模式从不是从用户中导出数据，用户必须拥有exp_full_database权限

grant exp_full_database to XXXX;

--账号解锁

alter user user_name account unlock

--查看那密码过期限制

select * from dba_profiles s where s.profile='default' and resource_name='password_life_time';

--改为无限制

alter profile default limit password_life_time unlimited;

--批量更新表明

select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);

--检查数据库增长趋势

select to_char(creation_time, 'yyyy-mm') "Month",

sum(bytes) / 1024 / 1024 "Growth (MB)"

from sys.v_$datafile

where creation_time > SYSDATE - 365

group by to_char(creation_time, 'yyyy-mm')

order by to_char(creation_time, 'yyyy-mm');

--解锁过程

select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
--查看锁
--alter system kill session 'sid,serial#';
--把锁给KILL掉
alter system kill session '146,21177';

--修改表名

rename oldname to newname

--查看asm使用情况

select name,total_mb,free_mb, (total_mb-free_mb) used from v$asm_diskgroup;

--查看scn最大值

select scn from (select max(next_change#) scn from v$archived_log group by thread#) scn ;

--通过时间查询

select count(*) from EMALL_USERS as of timestamp to_timestamp('2011-05-17 14:17:35','yyyy-mm-dd hh24:mi:ss') where condition;

--通过SCN号来查询

select dbms_flashback.get_system_change_number from dual; --当前

select * from EMALL_USERS as of scn 145815851;

其中SCN号和时间的转换：

--scn 转换成时间

SELECT scn_to_timestamp(145815851) FROM dual;

--时间转换成scn号

SELECT timestamp_to_scn(to_date('2011-05-17 00:05:00','yyyy-mm-dd hh24:mi:ss')) FROM dual;

--列出表结构信息

select a.table_name,

c.comments,

a.column_name,

a.comments,

b.data_type,

b.data_length,

b.nullable

from dba_col_comments a, dba_tab_cols b,dba_tab_comments c

where a.owner = 'WUDADAO_TEST'

and a.table_name = b.table_name

and b.owner = 'WUDADAO_TEST'

and a.comments is not null

and c.owner = 'WUDADAO_TEST'

and a.table_name = c.table_name

and b.table_name = c.table_name

and c.comments is not null

group by a.table_name,

c.comments,

a.column_name,

a.comments,

b.data_type,

b.data_length,

b.nullable

oracle查看object对象使用空间

1、表空间大小

Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name order by Sum(bytes)/1024/1024 desc;

2、表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name order by segment_name;

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024 desc;

3、索引占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='INDEX' group by segment_name order by segment_name;

4、数据文件使用情况

select a.tablespace_name,

round(a.bytes / 1024 / 1024, 0)"总空间",

round((decode(b.bytes, null, 0, b.bytes)) / 1024 / 1024, 0)"使用空间",

round((decode(b.bytes, null, 0, b.bytes)) / a.bytes * 100, 1)"使用率",

c.file_name,

c.status

from sys.sm$ts_avail a, sys.sm$ts_free b, dba_data_files c

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name = c.tablespace_name

order by a.tablespace_name;

-----------------------------------------------------------------------

select b.file_id 文件ID,

b.tablespace_name 表空间,

b.file_name 物理文件名,

b.bytes / 1024 / 1024 大小M,

c.max_extents / 1024 / 1024 可扩展数M,

b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024 总大小M,

trunc((b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024) 　已使用M,

trunc(sum(nvl(a.bytes, 0)) / 1024 / 1024) 　剩余M,

trunc(sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2) 　剩余比

from dba_free_space a, dba_data_files b, dba_tablespaces c

where a.file_id = b.file_id

and b.tablespace_name = c.tablespace_name

group by b.tablespace_name,

b.file_name,

b.file_id,

b.bytes,

c.max_extents,

b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024

order by b.file_id;

5、表空间使用统计

select a.tablespace_name,

a.bytes / 1024 / 1024 "Sum MB",

(a.bytes - b.bytes) / 1024 / 1024 "used MB",

b.bytes / 1024 / 1024 "free MB",

round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"

from (select tablespace_name, sum(bytes) bytes

from dba_data_files

group by tablespace_name) a,

(select tablespace_name, sum(bytes) bytes, max(bytes) largest

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name

order by ((a.bytes - b.bytes) / a.bytes) desc;

删除重复列的方法

(1) DELETE FROM table_name A WHERE ROWID > (

SELECT min(rowid) FROM table_name B

WHERE A.key_values = B.key_values);

(2) create table table2 as select distinct * from table1;

drop table1;

rename table2 to table1;

(3) Delete from mytable where rowid not in(

select max(rowid) from mytable

group by column_name );

(4) delete from mytable t1

where exists (select 'x ' from my_table t2

where t2.key_value1 = t1.key_value1

and t2.key_value2 = t1.key_value2

...

and t2.rowid > t1.rowid);

1) 数据库session连接数

select count(*) from v$session;

2) 数据库的并发数

select count(*) from v$session where status='ACTIVE';

3) 是否存在死锁

set linesize 200

column oracle_username for a16

column os_user_name for a12

column object_name for a30

SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,

l.session_id,s.serial#, l.locked_mode,o.object_name

FROM v$locked_object l,dba_objects o,v$session s

where l.object_id = o.object_id and s.sid = l.session_id;

select t2.username||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_text

from v$locked_object t1,v$session t2,v$sqltext t3

where t1.session_id=t2.sid

and t2.sql_address=t3.address

order by t2.logon_time;

4) 是否有enqueue等待

select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0;

5) 是否有大量长事务

set linesize 200

column name for a16

column username for a10

select a.name,b.xacts,c.sid,c.serial#,c.username,d.sql_text

from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e

where a.usn=b.usn

and b.usn=e.XIDUSN

and c.taddr=e.addr

and c.sql_address=d.ADDRESS

and c.sql_hash_value=d.hash_value

order by a.name,c.sid,d.piece;

6)表空间使用率

set linesize 150

column file_name format a65

column tablespace_name format a20

select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,

round(f.sumbytes/1024/1024/1024,2) free_g,

round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,

round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent

from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,

(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d

where f.tablespace_name= d.tablespace_name

order by d.tablespace_name;

临时文件：

set linesize 200

column file_name format a55

column tablespace_name format a20

select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,

round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,

round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,

round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g

from dba_temp_files a,dba_free_space b

where a.file_id = b.file_id(+)

group by a.tablespace_name,a.file_name,a.bytes

order by a.tablespace_name;

select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,

round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,

round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,

round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g

from dba_temp_files a,dba_free_space b

where a.file_id = b.file_id(+)

group by a.tablespace_name,a.file_name,a.bytes

order by a.tablespace_name;

归档的生成频率:

set linesize 120

column begin_time for a26

column end_time for a26

select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,

b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,

round((b.first_time - a.first_time)*24*60,2) minutes

from v$log_history a,v$log_history b

where b.recid = a.recid+1;

sql读磁盘的频率:

select a.username,b.disk_reads,b.executions,

round((b.disk_reads/decode(b.executions,0,1,b.executions)),2) disk_read_ratio,b.sql_text

from dba_users a,v$sqlarea b

where a.user_id = b.parsing_user_id

and disk_reads > 5000;

Datafile I/O:

col tbs for a12;

col name for a46;

select c.tablespace_name tbs,b.name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt

from v$filestat a,v$datafile b,dba_data_files c

where b.file# = a.file#

and b.file# = c.file_id

order by tablespace_name,a.file#;

Disk I/O

select substr(b.name,1,13) disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,

a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds,0,1,a.phyblkrd))/100) avg_rd_time,

((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_time

from v$filestat a,v$datafile b,dba_data_files c

where b.file# = a.file#

and b.file# = c.file_id

order by disk,c.tablespace_name,a.file#;

select a.username,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_text

from dba_users a,v$sqlarea b

where a.user_id = b.parsing_user_id

and b.buffer_gets > 5000000;

col index_name for a16;

col table_name for a18;

col column_name for a18;

select index_name,table_name,column_name,column_position from user_ind_columns

where table_name = '&tbs';

大事务：

select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops

where message like '%RMAN%';

select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops

where sofar <> totalwork;

where (sofar/totalwork)*100 < 100;

索引检查：

set linesize 200;

column index_name for a15;

column index_type for a10;

column table_name for a15;

column tablespace_name for a16;

select index_name,index_type,table_name,tablespace_name from user_indexes

where table_name ='&t';

set linesize 200;

column index_name for a26;

column table_name for a26;

column column_name for a22;

column column_position for 999;

column tablespace_name for a16;

select table_name,index_name,column_name,column_position from user_ind_columns where table_name = '&tab';

select table_name,index_name,column_name,column_position from user_ind_columns where index_name = '&ind';

select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where table_name = '&tab';

select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where index_name = '&ind';

set linesize 200;

column index_name for a20;

column table_name for a20;

select index_name,index_type,table_name,partitioned from user_indexes where index_name = '&ind';

等待事件：

set linesize 200

column username for a12

column program for a30

column event for a28

column p1text for a15

column p1 for 999,999,999,999,999

select s.username,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait sw

where s.sid=sw.sid and s.status='ACTIVE'

order by sw.p1;

select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait

order by event;

where event = 'buffer busy waits';

select owner,segment_name,segment_type,file_id,block_id from dba_extents

where file_id = &P1 and &P2 between block_id and block_id + blocks -1;

column event for a35;

column p1text for a40;

select sid,event,p1,p1text from v$session_wait order by event;

查询相关SQL：

set linesize 200

set pagesize 1000

column username for a8

column program for a36

select s.sid,s.serial#,s.username,s.program,st.sql_text

from v$session s,v$sqltext st

where s.sql_hash_value=st.hash_value and s.status='ACTIVE'

order by s.sid,st.piece;

select pid,spid from v$process p,v$session s

where s.sid=&sid and p.addr = s.paddr;

select s.sid,s.serial#,s.username,s.program,st.sql_text

from v$session s,v$sqltext st,v$process ps

where s.sql_hash_value=st.hash_value

and ps.spid=&sid and s.paddr=ps.addr

order by s.sid,st.piece;

select sql_text from v$sqltext

where hash_value in (select sql_hash_value from v$session

where paddr in (select addr from v$process

where spid=&sid))

order by piece;

select sql_text from v$sqltext

where address in (select sql_address from v$session

where paddr in (select addr from v$process

where spid=&sid))

order by piece;

select sql_text from v$sqltext

where hash_value in (select sql_hash_value from v$session where sid=&sid)

order by piece;

select sql_text from v$sqltext

where address in (select sql_address from v$session where sid=&sid)

order by piece;

select ps.addr,ps.pid,ps.spid,ps.username,ps.program,s.sid,s.username,s.program

from v$process ps,v$session s

where ps.spid=&pid

and s.paddr=ps.addr;

select s.sid,s.serial#,s.username,s.program,st.sql_text

from v$session s,v$sqltext st,v$process ps

where s.sql_hash_value=st.hash_value

and ps.spid='29863' and s.paddr=ps.addr

order by s.sid,st.piece;

column username for a12

column program for a20

select s.username,s.program,s.osuser,status

from v$session s

where s.status='ACTIVE';

query undotbs used percent:

set linesize 300;

select tablespace_name,segment_name,status,count(*),round(sum(bytes)/1024/1024,2) used_M from dba_undo_extents

group by tablespace_name,segment_name,status;

set linesize 300

column username for a10;

column program for a25;

select s.username,s.program,status,p.spid,st.sql_text from v$session s,v$process p,v$sqltext st where s.status='ACTIVE' and p.addr=s.paddr and st.hash_value=s.sql_hash_value order by s.sid,st.piece;

select snap_id,dbid,instance_number,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') snap_time from stats$snapshot

order by INSTANCE_NUMBER,SNAP_ID,SNAP_TIME;

set linesize 120;

column what form. a30;

select job,log_user,what,instance from dba_jobs;

set linesize 120;

column owner for a12;

column segment_name for a24;

column segment_type for a18;

select owner,segment_name,segment_type,file_id,block_id from dba_extents

where file_id=&file and &block between block_id and block_id + blocks - 1;

select file_id,file_name from dba_data_files where file_id = &file_id;

ANALYZE TABLE ICS_ODS_CUST_ICS_CUR partition(ICS_ODS_CUST_ICS_CUR_PART_1) VALIDATE STRUCTURE CASCADE;

ANALYZE TABLE ODSDATA.&object VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;

analyze index SYS_C00311764 validate structure cascade;

column owner for a12;

column segment_name for a26;

column segment_type for a16;

column tablespace_name for a20;

column bytes for 999,999,999,999;

select owner,segment_name,segment_type,tablespace_name,bytes,blocks,buffer_pool from dba_segments

where segment_name='&seg'

order by bytes desc;

select segment_name,segment_type,tablespace_name,partition_name,bytes from user_segments

where segment_name='ODSV_REC_FILE'

and segment_name in (select distinct table_name from user_part_col_statistics where table_name='ODSV_REC_FILE')

order by bytes desc;

col object_name for a26;

select object_name,object_type,status,temporary from user_objects

where object_name = '&o';

set linesize 180

break on hash_value skip 1 dup

col child_number format 999 heading 'CHILD'

col operation format a82

col cost format 999999

col Kbytes format 999999

col object format a25

select hash_value,

child_number,

lpad(' ', 2 * depth) || operation || ' ' || options ||

decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,

object_name object,

cost,

cardinality,

round(bytes / 1024) kbytes

from v$sql_plan

where hash_value=&hash_value

/*in

(select a.sql_hash_value

from v$session a, v$session_wait b

where a.sid = b.sid and b.event = 'db file scattered read')*/

order by hash_value, child_number, id;

Top 10 by Buffer Gets:

set linesize 100

set pagesize 100

SELECT * FROM

(SELECT substr(sql_text,1,40) sql,

buffer_gets, executions, buffer_gets/executions "Gets/Exec",

hash_value,address

FROM V$SQLAREA

WHERE buffer_gets > 10000

ORDER BY buffer_gets DESC)

WHERE rownum <= 10

;