ORACLE 常用命令

 

select * from all_users;                         ##查看所有用户
select name from v$database;                ##查看当前数据库
database test;                                     ##进入test数据库
select * from v$instance;                     ##查看所有的数据库实例
shutdown immediate                             ##关闭数据库
alter user sys identified by new_password;  ##更改用户密码
select username,password from dba_users;    ##查看当实例中的用户和密码
show parameter control_files;               ##查看控制文件;
select member from v$logfile;               ##查看日志文件
show parameter ;                            ## 查看数据库参数
select * from user_role_privs;              ##查看当前用户的角色
select username,default_tablespace from user_users;     ##查看当前用户的缺省表空间
alter user system identified by [password]              ##修改用户的密码
ALTER USER "SCOTT" ACCOUNT UNLOCK                       ##解锁SCOTT用户
show parameter processes;                               ##查看最大会话数

 

ALTER USER "swvip"  DEFAULT TABLESPACE "system"

 
修改用户swvip的默认表空间为system

 

select index_name,table_owner from dba_indexes where table_name like 'ACCT_HOST_ROUTE_BAK' ;

 
查表ACCT_HOST_ROUTE_BAK的索引表

 

查看当前库的所有数据表：

SQL> select TABLE_NAME from all_tables;
select * from all_tables;
SQL> select table_name from all_tables where table_name like ‘u’;
TABLE_NAME———————————————default_auditing_options

 

 

查看表结构：desc all_tables;

 

 

 

创建用户并赋予权限
###----------------------------创建用户并赋予权限------------------------------------####-

create user itmd
  identified by "itmd"
  default tablespace ITMD
  quota unlimited on MAS_DATA0   //在此空间无限制
  temporary tablespace TEMP;

 
  
  给用户赋予权限
  grant connect to itmd;
grant resource,create session to itmd;  开发角色
grant create procedure to dbuser； ＃这些权限足够用于开发及生产环境

 

grant connect,resource,imp_full_database,exp_full_database,debug connect session,debug any procedure to XXX; 

 正常开发所用的所有权限。

 

 

给用户授权
grant dba to omc;--授予DBA权限

revoke dba from userid; 收回DBA权限
grant unlimited tablespace to lxg;--授予不限制的表空间
grant select any table to lxg;--授予查询任何表
grant select any dictionary to lxg;--授予 查询 任何字典

 

 

删除用户
drop user SDTESTDB cascade;

查看用户和默认表空间的关系。  
  select   username,default_tablespace   from   dba_users;
查看用户使用的表空间
select DISTINCT owner ,tablespace_name from dba_extents where owner like ''jirarm'';

 

 

 

建表空间
###---------------------------------建表空间------------------------------------####-
================建立表空间============================

 

CREATE TABLESPACE "ITMD"
    LOGGING
    DATAFILE '/oracle10g/data/itmd_data01.dbf' SIZE 1024M
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
=====================================================
=================建立临时表空间============================

 

CREATE
    TEMPORARY TABLESPACE "TEMP01" TEMPFILE '/app/oracle/oradata/
    sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM
    SIZE 1M
=====================================================

创建完后把刚才创建的表空间，设置成系统的默认表空间

alter database default temporary tablespace temp02

 

create tablespace OMC datafile '/oradata/omc.bdf ' size 20480m autoextend on ;      ###autoextend on 自动扩展

 

###------------------------------------------------------------------------------------####-

 

DROP TABLESPACE SDCRM INCLUDING CONTENTS AND DATAFILES;
删除表空间UMPP

 

查看表空间
###----------------------------查看表空间大小------------------------------------####-
SELECT D.TABLESPACE_NAME "Name",
TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024)) /(A.BYTES / 1024 / 1024))*100,'99,990.9')  "used(%)",
TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9') "Free (M)"
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;
###--------------------------------------------------------------------------------------####-

 

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ;

 

===============================================

 

查看某个用户的数据量
select sum(bytes)/1024/1024  from dba_segments where owner='DOCMANAGER';

 

==================================================

 

查看表空间物理文件的名称及大小;
 
###--------------------表空间物理文件的名称及大小------------------------####-
  select tablespace_name, file_id, file_name,
    round(bytes/(1024*1024),0) total_space
    from dba_data_files
    order by tablespace_name;
###------------------------------------------------------------------------------------####- 

 

查表空间下的表的数量；

 

###------------------------------------------------------------------------------------####- 

 

select count(*) from dba_tables  where tablespace_name ='BNMS_DATA01';
   
###------------------------------------------------------------------------------------####- 

 

查看数据文件放置的路径
###------------------------------------------------------------------------------------####-  

 

SQL> col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
###------------------------------------------------------------------------------------####-  

 

    查看数据库库对象

 

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

 

用系统管理员，查看当前数据库有几个用户连接：

 

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

 

select sid,serial#,username,program,machine,status from v$session;

select count(*) from v$session;

 

Alter system kill session 'SID,SERIAL#'
用这个命令把连接的用户给杀死。

 

扩表空间
###------------------------------------------------------------------------------------####-
alter tablespace ITMD add datafile '/oracle10g/data/itmd_data03.dbf' SIZE 2048m;
给表G000增加一个7500m的逻辑卷'/dev/vgbilling/rg000_lv03'
###------------------------------------------------------------------------------------####-

 

检查被长时间锁的对象
###------------------------------------------------------------------------------------####-

 

SQL>select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id;
###------------------------------------------------------------------------------------####-

 

  select username,default_tablespace from dba_users where username ='PRMUSER';
  查看用户TEST1的默认表空间

 

 

/*加入一个日志组*/
alter database add logfile group 3 ('/$ORACLE_HOME/oracle/ora_log_file6.rdo' size 10M);

/*加入日志组的一个成员*/
alter database add logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3;

 

/*删除日志组:当前日志组不能删；活动的日志组不能删；非归档的日志组不能删*/
alter database drop logfile group 3;

/*删除日志组中的某个成员，但每个组的最后一个成员不能被删除*/
alter databse drop logfile member '$ORACLE_HOME/oracle/ora_log_file6.rdo';

/*清除在线日志*/
alter database clear logfile '$ORACLE_HOME/oracle/ora_log_file6.rdo';

alter database clear logfile group 3;

 

/*清除非归档日志*/
alter database clear unarchived logfile group 3;

 

/*数据库归档模式到非归档模式的互换,要启动到mount状态下才能改变;startup mount;然后再打开数据库.*/
alter database noarchivelog/archivelog;

achive log start;---启动自动归档

alter system archive all;－－手工归档所有日志文件

select * from v$archived_log;

show parameter log_archive;

 

/*回滚段的统计信息*/
select end_time,begin_time,undoblks from v$undostat;

/*undo表空间的大小计算公式:  UndoSpace=[UR * (UPS * DBS)] + (DBS * 24)
                            UR :UNDO_RETENTION 保留的时间(秒)
                            UPS :每秒的回滚数据块
                            DBS:系统EXTENT和FILE SIZE(也就是db_block_size)*/

select * from dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction;

show parameter transactions;

show parameter rollback;