澳门金沙vip一些实用的DBA语句(持续学习,持续更新)

一发赶时间,越拖延事,测量试验遇到oracle密码过期。

--查询LOB的大小和所在表空间

SELECT A.TABLE_NAME,
       A.COLUMN_NAME,
       B.SEGMENT_NAME,
       B.SEGMENT_TYPE,
       B.TABLESPACE_NAME,
       round((B.BYTES / 1024 / 1024 / 1024),2)
  FROM USER_LOBS A, USER_SEGMENTS B
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
ORDER BY B.BYTES DESC;

--查询查询一个表空间上所有表的大小
select us.segment_name, us.segment_type,us.tablespace_name,
  us.tablespace_name,
  round((us.BYTES/1024/1024/1024),2)
  from user_segments us
 where us.tablespace_name = '';

--查询特定一些表的大小,可以更换查询关键字
select segment_name,
       alt.OWNER
       tablespace_name,
       segment_type,
       round((BYTES /1024/1024/1024), 2) as "大小(GB)"
  from user_segments, all_tables alt
 where segment_type = 'TABLE'
   and segment_name like '%%'
   and alt.TABLE_NAME = segment_name
   order by tablespace_name, BYTES desc;

--查询当前表空间的使用情况
SELECT a.tablespace_name "表空间名",
       total/1024/1024 表空间大小,
       trunc((free/1024/1024),2) 表空间剩余大小,
       trunc(((total - free)/1024/1024),2) 表空间使用大小,
       ROUND((total - free) / total, 4) * 100 "使用率%"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM DBA_DATA_FILES
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name;

/*查询锁表情况*/
select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid

不多说,解决:设置成永但是期。

小哥询问,PL*SQL顾客登入后弹出警告:咋整?

  

1.以sys客商登入处理数据库;查询语句如下,未格式化查询语句。。

ORA-28002:the password will expire within 7 days密码在7天内将到期

/*1 统计数据表的num_row
  2 统计索引列的distinct_keys
  3 计算distinct_keys/num_rows的值,
  该值越接近1,则列的选择度越高,
  索引的效率就越高*/

analyze table schema.tablename compute statistics for all indexes for all columns; --收集最新的信息

select ut.num_rows, ui.distinct_keys, round((ui.distinct_keys/ut.num_rows), 2)
 from user_indexes ui,
 user_tables ut
 where ui.table_name = upper('tablename')
 and ui.index_name = upper('indexname')
 and ut.table_name = ui.table_name;

/*比较笨的办法,纯手工统计,计算*/
select count(*) from schema.tablename; --统计出了表的总行数
select distinct(ind_row) from schema.tablename; --统计索引所在列的distinct值
--之后就是纯手工计算了

/*查询等待事件*/
SELECT event,
       sum(decode(wait_time, 0, 1, 0)) "当前等待",
       sum(decode(wait_time, 0, 0, 1)) "当前未在等待",
       count(*) "Total"
  FROM v$session_wait
 GROUP BY event
 ORDER BY count(*) desc;

select a.EVENT, count(*)
  from v$session_wait a
 group by a.EVENT, a.WAIT_CLASS#
 order by count(*) desc;

--查询占用UNDO的用户相关情况
SELECT S.USERNAME, U.NAME, S.SID, S.SERIAL#
FROM V$TRANSACTION T, V$ROLLSTAT R, V$ROLLNAME U, V$SESSION S
WHERE S.TADDR = T.ADDR
AND T.XIDUSN = R.USN
AND R.USN = U.USN
ORDER BY S.USERNAME;
select * from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_LIFE_TIME'; 

 do you wish to change the password for hr@192.168.54.20/jx
now?你想要校正的密码

 

2.改换保质期,提交。

风流倜傥看正是不懂Hungary语,咱也不懂,没事翻译:
好,哥yes吧,然后输入两次新的密码;难题甘休

  –锁定、解锁账户语句

alter profile default limit password_life_time unlimited; commit; 

yes后,弹出对话框:

   alter user username account lock;

3.切换客户。不会再报密码过期难点。

new password  新的密码

   alter user username account unlock;

注:改正客户密码;alter user XXX identified by MMMM; –修正密码

verification       验证

   –改进顾客密码,借使高出ORA-28001错误也可那般消除

解锁顾客;alter user XXX identified by MMMM unlock; –解锁客商

 

   alter user username identified by password;

如上就是本文的全部内容,希望对大家的读书抱有利于,也可望大家多多点拨脚本之家。

***主题素材带来的沉凝,密码7天内过期,为何过期,曾几何时过期?

   –手工业扩展学一年级个数据文件

Oracle使用概要文件管理客商:

   alter database datafile ‘/data/data_file1.dbf’ resize 10G;

–首先供给有DBA权限的客户查询:   –查询你所登录的先入手

 

SQL> select username,profile from dba_users where username=’HR’;