1、修改密码永不过期
select * from dba_users
select * from dba_profiles where resource_name='PASSWORD_LIFE_TIME' and profile='DEFAULT'
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
--alter user username profile DEFAULT identified by 123;
2、查询当前表被哪些表所引用
select * from user_constraints --constraint_name,r_constraint_name
select * from user_cons_columns
select a.* from user_constraints a, user_cons_columns b
where a.r_constraint_name=b.constraint_name and a.constraint_type='R' and b.table_name='CSMENU'
3、查询进程锁，清除被锁进程
select * from v$process
select * from v$lock
select * from v$session where sid in (select sid from v$lock)
alter system kill session 'sid,serial#';
4、修改索引表空间
alter index index_Name rebuild tablespace tablespace_Name;
5、查看表空间路径
select * from dba_data_files
6、修改表空间大小
ALTER DATABASE DATAFILE 'D:\TBS\xxx.DBF' RESIZE 512M;
7、 查看表空间使用率
SELECT a.tablespace_name "表空间名",total/1024/1024 "表空间大小单位M",
free/1024/1024 "表空间剩余大小单位M",(total - free)/1024/1024 "表空间使用大小单位M",
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;
8、查看jobs信息
select * from dba_jobs
select * from dba_jobs_running
9、 手动更新统计信息
当Oracle的数据表的记录发生较大变化（超过20%），统计信息没有及时更新。
而SQL优化器依赖于统计信息，可能会引起查询时部分索引不使用或者使用错误的索引。
analyze table table_name compute statistics;
10、只导出表结构，不导出数据 rows=n代表不要数据行
exp username/pwd@orcl file=f:/backup.dmp owner=user rows=n
11、闪回查询、闪回删除、闪回表
--闪回查询
select * from aa as of timestamp sysdate - 4/24/60;
--闪回删除
flashback table aa to before drop;
--闪回表
alter table aa enable row movement;
flashback table aa to timestamp sysdate - 4/24/60 disable triggers;
12、ORACLE常用系统表
select * from v$database;
select * from v$instance;
select * from v$version;
select * from user_sys_privs;
select * from user_tab_privs
select * from dba_users;
select sql_text,last_load_time from v$sql where sql_text like '%insert into%' order by last_load_time desc;
13、查询表的占用的存储空间
select UT.table_name, x.TABLE_SIZE
from user_tab_comments ut,
(select Segment_Name, Sum(bytes) / 1024 / 1024 / 1024 as "TABLE_SIZE"
From User_Extents
Group By Segment_Name) x
where ut.table_type = 'TABLE'
and ut.table_name = x.Segment_Name(+)
order by x.TABLE_SIZE desc;
14、重建表空间索引
SELECT DISTINCT 'alter INDEX ' || segment_name ||' REBUILD TABLESPACE "TBSTEST"; ' FROM dba_extents WHERE tablespace_name = 'TBSTEST' AND segment_type = 'INDEX';
15、Oracle模拟数据
select rownum as FID from dual connect by rownum<= 1000;
16、清除表数据并释放表空间
--查询表占用空间 倒序排列
select segment_name, (sum(bytes)/1024/1024) m from dba_extents where segment_type like 'TABLE%' and tablespace_name='XXXTBS' group by segment_name order by (sum(bytes)/1024/1024) desc;
--查询表空间占用空间
select (sum(bytes)/1024/1024) m from dba_extents where segment_type like 'TABLE%' and tablespace_name='XXXTBS';
--查询单表占用空间情况
select * from dba_extents where segment_type like 'TABLE%' and tablespace_name='XXXTBS' and segment_name='XXXTABLENAME';
--清除表数据，释放表占用空间
truncate table xxxtablename;
alter table xxxtablename deallocate unused keep 0;
或者
truncate table xxxtablename drop storage;注意：SQL里边的表空间、表名，dba_extents里边存储的大写。