1.查询数据库所有用户的状态

select username,account_status from dba_users;

2.给锁定的用户解锁

alter user 用户名 account unlock;

3.查询密码有效期

SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

4.修改密码有效期为无限期

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

5.查询密码尝试登录错误次数（超过就会被锁定）

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='FAILED_LOGIN_ATTEMPTS';

6.修改密码最大尝试次数

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 次数（如：6）;

7.修改当前会话时间格式

alter session set nls_date_format = 'yyyy-MM-dd hh24:mi:ss';

8.查询最近某个用户锁定日期

select username,lock_date  from dba_users where username like '用户名';

9. 查找用户锁

select s.username,

decode(l.type,'TM','TABLE LOCK',

'TX','ROW LOCK',

NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser

from v$session s,v$lock l,dba_objects o

where s.sid=l.sid

and o.object_id=l.id1

and s.username is not null;

(1)锁表查询的代码有以下的形式：
select count(*) from v$locked_object;
select * from v$locked_object;
(2)查看哪个表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
(3)查看是哪个session引起的
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
(4)查看是哪个sql引起的
select b.username,b.sid,b.serial#,c.* from v$locked_object a,v$session b,v$sql c where a.session_id = b.sid
and b.SQL_ID = c.sql_id and c.sql_id = ''
order by b.logon_time;
(5)杀掉对应进程
执行命令：alter system kill session'1025,41';
其中1025为sid,41为serial#.

object_name=‘表名’

然后得到sid,serial#
解决方案
1.alter system kill session ‘sid, serial#’

2.alter system disconnect session ‘sid, serial#’ immediate;

10.查询表空间大小

select tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
  from sys.dba_data_files
 order by tablespace_name;

11.修改当前表空间大小

alter database datafile '/App/oracle/app/oradata/USER/USER_SPACE.DBF' resize 32g

12.为当前表空间增加新的数据文件

 alter tablespace USER_SPACE add datafile '/App/oracle/app/oradata/USER/USER_SPACE1.DBF' size 32g;

13.闪回某一时间点某表的数据

1.查询某一时间点该表的数据
select * from 表名 as of timestamp to_timestamp('2022-03-31 12:11:11','yyyy-mm-dd hh24:mi:ss');  //后面也可以加过滤条件 WHERE XXX;
2.启用行移动功能
alter table 表 enable row movement;
3.回滚表到时间节点
flashback table 表 to timestamp to_timestamp('2022-03-31 12:11:11','yyyy-mm-dd hh24:mi:ss');