日常收集整理的oracle常用命令大全(收藏)_oracle_

 su - oracle 

 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup SVRMGR>quit 

 oracle>svrmgrl SVRMGR>connect internal SVRMGR>shutdown SVRMGR>quit 

 $ sqlplus /nolog SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> startup^C SQL> startup ORACLE instance started. 

 su － root 

 hareg －y oracle 

 hareg －n oracle 

 startup nomount alter database mount alter database open 

 SQL>select * from dictionary where instr(comments,'index')>0; 

 SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES'; 

 SQL>select username,default_tablespace from user_users; 

 SQL>select * from user_role_privs; 

 SQL>select * from user_sys_privs; SQL>select * from user_tab_privs; 

 SQL>select * from user_tables; 

 SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0; 

 SQL>select object_name,created from user_objects where object_name=upper('&table_name'); 

 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name'); 

 SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 

 SQL>select index_name,index_type,table_name from user_indexes order by table_name; 

 SQL>select * from user_ind_columns where index_name=upper('&index_name'); 

 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name'); 

 SQL>select * from user_sequences; 

 SQL>select view_name from user_views; 

 SQL>set view_name,text_length from user_views; SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小 SQL>select text from user_views where view_name=upper('&view_name'); 

 SQL>select * from user_synonyms; 

 SQL>select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name'); SQL>select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position; 

 SQL>select object_name,status from user_objects where object_type='FUNCTION'; SQL>select object_name,status from user_objects where object_type='PROCEDURE'; 

 SQL>select text from all_source where owner=user and name=upper('&plsql_name'); 

 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name; 

 select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 

 select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ; 

 select name from v$controlfile; 

 select member from v$logfile; 

 select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name; SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

 select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 

 Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle'; 

 Select Created, Log_Mode, Log_Mode From V$Database; 

 SQL> select username,sid,serial# from v$session; 

 SQL> alter system kill session 'sid,serial#'; 

 SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;