Oracle常用的查询语句
SELECT * from user_views where view_name='v$session'; SELECT * FROM ALL_USERS where username like 'S%'; select * from v$database; select username,profile from dba_users;; select * from dba_profiles where profile='DEFAULT'; SELECT * from v$archive_dest; select * from v$kccle; select * from v$logfile; select * from v$archive_dest; select * from v$archive_dest_status; select * from dba_tables T where owner='SYSTEM' AND TABLE_NAME LIKE 'FAM%'; analyze table family compute statistics for table--表分析; select * from user_tables where table_name='FAMILY'; select * from v$parameter where name='db_block_size'; select segment_name,bytes from user_segments; select count(*) from all_tables; select count(*) from dba_tables; select * from dba_tables where tablespace_name='SYSTEM' and table_name='FAMILY'; select segment_name,count(*),round(sum(bytes/1024/1024),9) MB from user_segments group by segment_name order by MB desc; select * from REPCAT$_DDL; select * from user_segments wheRE segment_name='REPCAT$_DDL'; select count(*) from user_tables; select * from DBA_tables WHERE TABLE_NAME='SYSTEM'; SELECT SUM(BYTES/1024/1024) FROM DBA_EXTENTS WHERE SEGMENT_NAME='FAMILY'; SELECT SUM(BYTES/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM'; SELECT SUM(BYTES/1024/1024) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='SYSTEM'; SELECT SEGMENT_NAME,sum(bytes/1024/1024) from user_segments group by segment_name having segment_name='FAMILY'; SELECT SUM(BYTES/1024/1024) FROM DBA_EXTENTS WHERE SEGMENT_NAME='FAMILY'; SELECT SUM(BYTES/1024/1024) FROM USER_SEGMENTS WHERE SEGMENT_NAME='FAMILY'; SELECT 1-104.5625/810 FROM DUAL; /*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 SUM(BYTES/1024/1024) from SYS.DBA_FREE_SPACE t WHERE TABLESPACE_NAME='SYSTEM'; SELECT TABLESPACE_NAME,SUM(BYTES/1024/1024) MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME; SELECT SUM(BYTES/1024/1024) MB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM'; select * from SYS.DBA_FREE_SPACE t; SELECT * FROM DBA_EXTENTS WHERE SEGMENT_NAME='FAMILY'; SELECT SUM(BYTES/1024/1024) MB FROM DBA_EXTENTS WHERE SEGMENT_NAME='FAMILY' ;--表的大小; SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='FAMILY';--表的大小 select * from v$parameter where name='db_block_size';--块的大小 select blocks*8/1024 from user_tables where table_name='FAMILY';--表的大小 select sql_text,first_load_time from v$sql order by first_load_time desc; commit; select * from family; delete from family where name='zXq'; alter system switch logfile;--重做日志切换,切换后归档日志也会切换 select * from v$logfile; --重做日志 select * from v$archive_dest;--归档日志- select * from v$parameter where name='db_recovery_file_dest_size'; --归档日志总大小- select * from v$parameter where name like '%retention_target'; select * from v$parameter where name='db_recovery_file_dest'; select * from v$flashback_database_log; select flashback_on from v$database;--查看数据库闪回功能有没有打开 select * from v$version;--数据库版本 select * from v$parameter where name like '%retention_target'; select value/1024/1024/1024 AS "LOG/GB" from v$parameter where name='db_recovery_file_dest_size'--3.76171875; select * from v$parameter where name='db_recovery_file_dest'; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;; select * from user_recyclebin;--回收站 select * from v$parameter where name='background_dump_dest';--警告文件和系统跟踪文件位置 select * from v$parameter where name='user_dump_dest';--用户跟踪文件位置 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |