Oracle's Common query statements

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--tables analysis;

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 have 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 M B ", 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

--Table space */

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 ';--the size of the table;

SELECT bytes/1024/1024 from dba_segments WHERE segment_name= ' FAMILY ';--Table size

SELECT * from V$parameter where name= ' db_block_size ';--size of block

Select blocks*8/1024 from User_tables where table_name= ' FAMILY ';--Table size

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;--redo log switchover, the archive log will also switch after switching

SELECT * from V$logfile;--Redo Log

SELECT * FROM v$archive_dest;--archive log-

SELECT * from V$parameter where name= ' db_recovery_file_dest_size ';--archive log Total size-

SELECT * from V$parameter where name is 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;--View database Flashback feature No open

SELECT * FROM v$version;--database version

SELECT * from V$parameter where name is 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;--Recycle Bin

SELECT * from V$parameter where name= ' background_dump_dest ';--warning files and system trace file locations

SELECT * from V$parameter where name= ' user_dump_dest ';--User trace file location

This article is from the "Galaxy | Computer network" blog, please be sure to keep this source http://qqran.blog.51cto.com/10014850/1874587

Oracle's Common query statements