oracle 查询视图变更,ORACLE 常用视图查询

--数据库用户名和密码过时

--1.查看用户的profile设置：一般用户的profile设置都为DEFAULT

--2.查看系统profiles中PASSWORD_LIFE_TIME设置

--3.修改DBA_PROFILES中PASSWORD_LIFE_TIM的设置，改为ULIMITED;修改后设置立即生效，不需要重启数据库，此时密码永远不会过期

--4.已经被报告了密码快要过期的账户必须再改一次密码(需要DBA权限)

SELECT username, profile FROM dba_users;

SELECT *

FROM dba_profiles s

WHERE s.profile = 'DEFAULT'

AND resource_name = 'PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

SQLplus / as sysdba;

SQL > alter user system identified by oracle;

select distinct t.SID from v$mystat t;

select * from v$session t;

select * from v$lock;

select b.OSUSER,b.MACHINE,b.SID from v$session b where b.SID = (select a.SID from v$lock a where a.REQUEST = 6);

--查看某张表的大小

SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments WHERE segment_name=upper('THISRISKCONTROLPARAM');

select * from user_segments t order by t.BYTES desc;

--替换字符串

--translate对单个字符; replace对整个字符串

select trim(translate('中国123abc45612、7)81','1234567890mm','XYZ ')) from dual;

select regexp_replace('中国123abc456、7)8','[^0-9]') from dual;

select replace('中国123abc45612、7)81','123','XYZ ') from dual;

Alter Sequence SeqTest2010_S Increment By 1007;

Alter Sequence SeqTest2010_S Increment By 1;

Select SeqTest2010_S.NextVal From Dual;

--查询语句

--1、查看所有表空间的大小

select tablespace_name, sum(bytes) / 1024 / 1024

from dba_data_files

group by tablespace_name;

--2、查看已经使用的表空间的大小

select tablespace_name, sum(bytes)/1024/1024  from dba_free_space group by tablespace_name;

--3、查看可以使用表空间的大小

select a.tablespace_name, total, free, total - free used

from (select tablespace_name, sum(bytes) / 1024 / 1024 total

from dba_data_files 　　group by tablespace_name) a,

(select tablespace_name, sum(bytes) / 1024 / 1024 free

from dba_free_space 　　group by tablespace_name) b

where a.tablespace_name = b.tablespace_name;

select sid,

v$session.username,--用户名,

last_call_et, --持续时间,

status, --状态,

LOCKWAIT, --等待锁,

machine, --用户电脑名,

logon_time, --开始登入时间,

sql_text

from v$session, v$process, v$sqlarea

where paddr = addr

and sql_hash_value = hash_value

and status = 'ACTIVE'

and v$session.username is not null

order by last_call_et desc;

select b.MACHINE, b.PROGRAM, count(*)

from v$process a, v$session b

where a.ADDR = b.PADDR

and b.USERNAME is not null

group by b.MACHINE, b.PROGRAM

order by count(*) desc;

select t.EXECUTIONS,t.SQL_TEXT,t.ELAPSED_TIME,t.LAST_ACTIVE_TIME from v$sql t where t.SQL_TEXT like '%update TAssetDay t%';

select value from v$parameter where name='optimizer_mode';

SQL >show parameters optimizer_mode;

alter system set OPTIMIZER_MODE=RULE scope=both;

本文转自：http://blog.sina.com.cn/s/blog_6bdd0e1d01013ls6.html