【Oracle笔记】最详细的操作命令大全（牛人高阶版）

conn / as sysdba：不需要listener进程
conn system/admin：只能连接本地数据库，不需要listener进程
conn system/admin@oracle：需要listener进程处于可用状态

sqlplus /nolog：不登录到数据库服务器
sqlplus / as sysdba：不需要listener进程（本人最常用的）

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

lsnrctl start：启动监听
lsnrctl stop：关闭监听
lsnrctl status：查看监听状态

startup：启动实例
shutdown immediate：关闭实例
select * from v$instance：查看实例状态    

select * from all_users; 

show user 

create user a identified by a;(默认建在SYSTEM表空间下)
grant connect,resource to a; 

alter user a identified by 123456;

grant select any table to a；

grant select,insert,update,delete on student.emp to a;

select * from tab;

select username,default_tablespace from user_users; 

select * from user_role_privs; 

select * from user_sys_privs; 
select * from user_tab_privs;

 select * from user_tables; 

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

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

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

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

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

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

    select sum(bytes)/(1024*1024) as "size(M)" from user_segments 
                where segment_name=upper('&index_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 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;