--查询表空间剩余空间
select a.tablespace_name,
a.bytes / 1024 / 1024 "Sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc
SELECT SID,
decode(totalwork, 0, 0, round(100 * sofar / totalwork, 2)) "Percent",
message "Message",
start_time,
elapsed_seconds,
time_remaining
from V$Session_longops
--查看进度
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
--查看分区表的使用情况
select
owner,segment_name,t.partition_name, round(bytes/1024/1024/1024,3) seg_size
from DBA_SEGMENTS t
WHERE owner = upper('lbs')
and segment_name =upper('T_SUBSP_LOG_2011')
shutdown abort :禁止所有新用户连接，强制断开所有连接，立即关闭，数据库重启需要恢复，相当于断电。关闭速度最快。
shutdown immediate :禁止所有新用户连接，未完成的事务回滚，强行断开用户，数据库重启不需要恢复。关闭速度较快。
shutdown transactional :禁止所有新用户连接，等待所有事务完成，强行断开用户，数据库重启不需要恢复。关闭速度较慢。
shutdown normal :禁止所有新用户连接，等待所有事务完成和退出，数据库重启不需要恢复，与shutdown等效。关闭速度最慢。
--查看表空间及创建语句 --可通过PLSQL导出导成HTML
SELECT u.tablespace_name,DBMS_METADATA.GET_DDL('TABLESPACE',u.tablespace_name)
FROM user_tablespaces u;
--查看表及其创建语句 --可通过PLSQL导出导成HTML
SELECT u.table_name,DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
-- 删除重复记录
DELETE FROM emp e
WHERE e.ROWID > (SELECT MIN( x.ROWID )
FROM emp x
WHERE x.empno = e.empno);
--判断记录是否存在
Select count(*) from t1 where rownum=1;
--查询某个表在什么地方引用到了
select * from user_dependencies where referenced_name= 'T_BILL_LOCATE_HIS'
(1) 删除重复记录
DELETE FROM emp e
WHERE e.ROWID > (SELECT MIN( x.ROWID )
FROM emp x
WHERE x.empno = e.empno);
（2）判断记录是否存在
Select count(*) from t1 where rownum=1;
（3）查询某个表在什么地方引用到了
select * from user_dependencies where referenced_name= 'T_BILL_LOCATE_HIS'