sql statement_常用

kill a session

SELECT distinct S.SID, s.serial#, S.STATUS, s.username, s.osuser, Q.HASH_VALUE,Q.SQL_TEXT
        FROM V$SESSION S join V$SQL Q
             on S.SQL_HASH_VALUE = Q.HASH_VALUE
             AND S.SQL_ADDRESS = Q.ADDRESS
             and S.username='IPO'
             and s.osuser='ashen'
       ORDER BY S.SID

 

check the difference between differenct db:

(

select

t1.table_name

,t1.column_name

,t2.data_type

 from user_col_comments@IPO.allen t1

,user_tab_cols@IPO.allen t2

where

t1.owner=t2.owner(+)

and t1.table_name=t2.table_name(+)

and t1.owner='ipo'

 

minus

select

t1.table_name

,t1.column_name

,t2.data_type

 from user_col_comments t1

,user_tab_cols t2

where

t1.owner=t2.owner(+)

and t1.table_name=t2.table_name(+)

and t1.owner='ipo'

)

Union all

(select

t1.table_name

,t1.column_name

,t2.data_type

 from user_col_comments t1

, user_tab_cols t2

where

t1.owner=t2.owner(+)

and t1.table_name=t2.table_name(+)

and t1.owner='ipo'

minus

select

t1.table_name

,t1.column_name

,t2.data_type

 from user_col_comments@IPO.allen t1

, user_tab_cols@IPO.allen t2

where

t1.owner=t2.owner(+)

and t1.table_name=t2.table_name(+)

and t1.owner='ipo'

)

 

find index name and column name in db

 

select * from dba_ind_columns
where table_name='WCA_ITEM'
and column_name='ITEM_CLASS_ID'

 

get the entire SQL statement in the TOP SQL statement if the SQL statement is too long.

select SQL_TEXT

from v$sqltext

where hash_value=:(Hash Value)

order by piece asc;

 

find out which package one particular standalone SQL statement belongs to

select u.username,o.object_name,s.PROGRAM_LINE#

from v$sql s,user_objects o,all_users u

where s.hash_value=:(Hash Value)

and s.PROGRAM_ID=o.object_id

and s.PARSING_USER_ID=u.user_id;

export the table and column name in the db

select t1.table_name,t1.column_name,t2.table_type

from user_tab_columns t1
,user_tab_comments t2
where t2.table_type='TABLE'
and t1.table_name=t2.table_name
order by t1.TABLE_NAME

use variable in procedure

create table a(a int)

create or replace procedure insert_variable(table_name varchar2)
as
sqltxt varchar2(1000);
begin
sqltxt:='insert into '||table_name||' values(1)';
execute immediate sqltxt;
end;
 

查看版本

select * from v$version;

查看归档模式

archive log list

查看SGA信息

show sga

查看实例信息

select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,PARALLEL FROM V$INSTANCE;

 

 

创建新的临时表空间
SQL> create temporary tablespace TEMP1 TEMPFILE '/oradata/TEMP1_01.dbf' size 100M;

Tablespace created

SQL> create temporary tablespace TEMP2 TEMPFILE '/oradata/TEMP2_01.dbf' size 100M;

Tablespace created
将当前临时表空间指定为新的临时表空间
SQL> alter database default temporary tablespace TEMP1;

Database altered
删除旧的临时表空间
SQL> drop tablespace ONLYDWTEMP including contents and datafiles;

Tablespace dropped

 

select text from user_source where name ='JUSTIN_TEMP'
select namespace from v$librarycache
select substr(a.name,9,10) "Name",round(sum(b.value)/1024/1024,1)||' M' "Total UGA for all sessions"
from V$statname a,v$sesstat b
where a.statistic# = b.STATISTIC#
--and a.name='session uga memory'
group by a.name

 

 

查看一个table是否出现在一个package中。

select  * from dba_dependencies

where type='PACKAGE BODY'

and referenced_type='TABLE'

and name in ()

and referenced_name in ()

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20945761/viewspace-558021/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20945761/viewspace-558021/