Oracle DBA Common Queries

1. Querying the system for all objects

Select owner, object_name, Object_type, created, Last_ddl_time, timestamp, status
From Dba_objects
Where Owner=upper (' Scott ')

–2. View all system tables
Select owner, TABLE_NAME, tablespace_name from Dba_tables

–3. View all users ' tables
Select owner, TABLE_NAME, tablespace_name from All_tables

–4. View the current User table
Select table_name, tablespace_name from User_tables

–5. Viewing the User table index
Select T.*,i.index_type from User_ind_columns T, user_indexes I where
T.index_name = i.index_name and t.table_name = I.table_name
and t.table_name = table to query

–6. View PRIMARY Key
Select cu.* from User_cons_columns cu, user_constraints au
where cu.constraint_name = Au.constraint_name
and Au.constraint_type = Upper (' P ') and au.table_name = table to query

–7. Viewing uniqueness constraints
Select column_name from User_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and Au.constraint_type = Upper (' u ')
and au.table_name = table to query

–8. View foreign keys
SELECT * from user_constraints c where c.constraint_type = ' r ' and c.table_name = table to query
SELECT * from User_cons_columns cl where cl.constraint_name = FOREIGN key Name
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name

–9. View column properties for a table
Select T.*,c.comments
From User_tab_columns T, user_col_comments C
where t.table_name = c.table_name and T.column_name = c.column_name and t.table_name = table to query

–10. View all table spaces
Select Tablespace_name from Dba_data_files GROUP by Tablespace_name

############################################
–1. View the maximum number of Oracle connections
Sql>show parameter Processes #最大连接数

–2. To modify the maximum number of connections
Sql>alter system set Processes=value Scope=spfile
– Restart the database
Sql>shutdown Force
Sql>start Force

–3. View current number of connections
Sql>select * from V$session where username are not null

–4. View the number of connections for different users
Sql>select Username,count (username) from V$session where username was not a null group by username #查看指定用户的连接数

–5. View the number of active connections
Sql>select Count (*) from v$session where status= ' active ' #查看并发连接数

–6. To view the number of connections for a specified program
Sql>select Count (*) from v$session where program= ' jdbc thin client ' #查看jdbc连接oracle的数目

–7. View Database Installation Instance (DBA authority)
Sql>select * from V$instance

–8. View Run Instance Name
Sql>show parameter instance_name

–9. View database name
Sql>show parameter db_name

–10. View the database domain name
Sql>show parameter Db_domain

–11. View database service Name
Sql>show parameter Service_names

–12. View the global database name
Sql>show parameter Global

–13. View Table Space usage

-- (1) select dbf.tablespace_name,         dbf.totalspace  "总量(m)" ,         dbf.totalblocks  as "总块数" ,         dfs.freespace  "剩余总量(m)" ,         dfs.freeblocks  "剩余块数" ,         (dfs.freespace / dbf.totalspace) * 100  as "空闲比例"    from ( select t.tablespace_name,                 sum (t.bytes) / 1024 / 1024 totalspace,                 sum (t.blocks) totalblocks            from dba_data_files t           group by t.tablespace_name) dbf,         ( select tt.tablespace_name,                 sum (tt.bytes) / 1024 / 1024 freespace,                 sum (tt.blocks) freeblocks            from dba_free_space tt           group by tt.tablespace_name) dfs   where trim(dbf.tablespace_name) = trim(dfs.tablespace_name) -- (2) select t. name "tablespace name" ,         free_space,         (total_space - free_space) used_space,         total_space    from ( select tablespace_name,  sum (bytes / 1024 / 1024) free_space            from sys.dba_free_space           group by tablespace_name)  free ,         ( select b. name ,  sum (bytes / 1024 / 1024) total_space            from sys.v_$datafile a, sys.v_$tablespace b           where a.ts# = b.ts#           group by b. name ) t   where free .tablespace_name = t. nameSee also: http://www.cnblogs.com/linjiqin/p/3152665.html

Oracle DBA Common Queries