Oracle语句

--如何修改Oracle默认用户密码有效期时间
--1、查看用户的proifle是哪个，一般是default：

SELECT username,PROFILE FROM dba_users;

--2、查看指定概要文件（如default）的密码有效期设置：

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

--3、将密码有效期由默认的180天修改成“无限制”：

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--修改之后不需要重启动数据库，会立即生效。

E35D7SMfM/p6OekUIqZvIJibIOBv9whYhv3lpQiwmYOUBdJ32dUk/Q==

--如何修改oracle数据库可以导出空表

alter system set deferred_segment_creation=false; 

--给用户表解锁
alter user xuchang_pick identified by xuchang_pick account unlock;

create user fuxin_yxy identified by fuxin_yxy;
grant dba ,connect ,resource to fuxin_yxy;
alter system set deferred_segment_creation=false; 

create user sryc_pick identified by sryc_pick;
grant dba,connect ,resource to sryc_pick;
alter system set deferred_segment_creation=false; 
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;

--查看被锁的表 
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;

--查看那个用户那个进程照成死锁
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;

--查看连接的进程 
SELECT sid, serial#, username, osuser FROM v$session;

--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。

--杀掉进程 sid,serial#
alter system kill session'210,11562';