oracle运维常用sql

--查看数据文件使用率
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 * from dba_data_files where tablespace_name='BAIDU_DOC';

--添加自增长
ALTER TABLESPACE LOGTBS ADD DATAFILE '/data/ora11g/oradata/oracle9i/logtbs26.DBF' SIZE 128M AUTOEXTEND ON NEXT 50M MAXSIZE 8192M;
--添加固定的文件
ALTER TABLESPACE LOGTBS ADD DATAFILE '/data/ora11g/oradata/oracle9i/logtbs27.DBF' SIZE 2048M;

--查看表空间对应的表名
select * from all_tables where tablespace_name='GAIBAN_DATA';
select OWNER,TABLE_NAME,TABLESPACE_NAME from all_tables where tablespace_name='GAIBAN_DATA';
--查看表空间对应的用户
select * from all_tables where owner='GAIBAN';

--查看建表sql
select dbms_metadata.get_ddl('TABLE','T_WIKI_CITY') from ALL_TABLES;

--查看某表空间占用较大的表
select segment_name, sum(bytes)/1024/1024 Mbytese from dba_segments where Tablespace_name='LOGTBS' group by segment_name order by MBYTESE DESC;
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;
select segment_name, sum(bytes)/1024/1024 Mbytese from dba_segments where segment_type='TABLE' group by segment_name;

--查看表使用的空间大小
select segment_name, bytes from user_segments where rownum<20;

--查看建表sql
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

--查看表中数据行数，一小时更新一次
select table_name,num_rows from dba_tables where table_name='T_LOG_TGLM_ENTRANCE_DATA';

--查看表对应的用户和表空间

select owner,table_name,tablespace_name from all_tables where table_name='t_wiki_user'