查找所有的超级用户
SELECT * FROM v$pwfile_users ;
查看临时表空间
select * from V$tempfile;
SQL> select * from v$tempfile ;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
1 947641 2015/4/24 15: 3 1 ONLINE READ WRITE 377487360 46080 20971520 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
2 6559741 2015/6/2 14:3 8 1 ONLINE READ WRITE 1085276160 132480 52428800 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAS_T_EAS0120150525_STANDARD.DBF
3 27839098 2015/11/10 10 11 1 ONLINE READ WRITE 209715200 25600 209715200 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\P2BPRODUCT_TEMP.DBF
查看表空间数据文件
SQL> select * from V$datafile ;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_TIME PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- -------------------- ------------ ------------------------ --------------------- ---------------- -------------- ------------------------ ---------------------
1 7 2010/3/30 10: 0 1 SYSTEM READ WRITE 43218582 2016/3/25 17:52 0 947454 947455 2015/4/24 1 1268776960 154880 0 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0 0 0 NO 0 0
2 2160 2010/3/30 10: 1 2 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 947454 947455 2015/4/24 1 1101004800 134400 0 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0 0 0 NO 0 0
3 944668 2010/3/30 11: 2 3 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 947454 947455 2015/4/24 1 304087040 37120 0 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0 0 0 NO 0 0
4 17981 2010/3/30 10: 4 4 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 947454 947455 2015/4/24 1 1336279040 1631200 0 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0 0 0 NO 0 0
5 1004953 2015/4/24 15: 6 5 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 0 0 3623878656 442368 268435456 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\test.DBF 0 8192 NONE 0 0 0 NO 0 0
6 6559455 2015/6/2 14:3 7 6 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 0 0 739901440 90320 20971520 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAS_D_EAS0120150525_STANDARD.ORA 0 8192 NONE 0 0 0 NO 0 0
7 6559775 2015/6/2 14:3 9 7 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 0 0 52428800 6400 52428800 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAS_D_EAS0120150525_TEMP2.DBF 0 8192 NONE 0 0 0 NO 0 0
8 10797710 2015/7/2 9:55 10 8 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 0 0 146800640 17920 10485760 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\NAMIFINANCE.DBF 0 8192 NONE 0 0 0 NO 0 0
9 27839139 2015/11/10 10 12 9 ONLINE READ WRITE 43218582 2016/3/25 17:52 0 0 0 419430400 51200 419430400 8192 D:\APP\ADMINISTRATOR\ORADATA\ORCL\PRODUCT.DBF
查询那个回滚段属于那个事务
select * from V$transaction;
查询当前回滚段的信息
select * from V$rollname;
大数据表空间
删除表空间
DROP TABLESPACE tbask INLUDEING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
查询表空间使用数据文件
SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE = 'tbask';
把表空间在线
ALTER TABLESPACE tp ONLINE;
把表空间离线
ALTER TABLESPACE tp OFFLINE;
对表空间执行介质恢复
ALTER DATABASE RECOVER TABLESPACE tp;
把表空间改成只读
ALTER TABLESPACE tp READ ONLY ;
限制用户对表空间使用
ALTER USER test QUOTA 50M ON tp ;
无限制使用表空间
ALTER USER test QUOTA UNLIMITED ON tp ;
GRANT UNLIMITED TABLESPACE TO test ;
把只读表空间改为读写
ALTER TABLESPACE tbask READ WRITE ;
命名表空间
ALTER TABLESPACE tbask RENAME TO tbask2 ;
查看数据库所有表空间
SELECT * FROM DBA_TABLESPACES ;
表空间使用情况
SELECT *
FROM DBA_FREE_SPACE a ,DBA_DATA_FILES b
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;