oracle常用命令.docx
《oracle常用命令.docx》由会员分享，可在线阅读，更多相关《oracle常用命令.docx（17页珍藏版）》请在冰豆网上搜索。
oracle常用命令
oracle常用命令
一、Oracle数据库实例、用户、目录及session会话查看：
1、ORACLESID查看设置
查看SID、用户名
$env|grepSID、select*fromv$instance、selectinstance_name,host_namefromv$instance;
查看数据库所有用户及用户状态：
SQL>selectusernames,account_statusfromdba_users;
设置SID
$exportORACLE_SID=hisvhfs
查看数据库DBID：
SQL>select*fromv$DBID
2、查询、设置Oracle数据库实例最大进程数及最大会话数
查看系统最大session：
SQL>showparametersession
查看当前连接数：
SQL>selectcount（*）fromv$bgprocess
查看系统最大进程数：
SQL>showparameterprocess
查看当前连接到数据库的用户：
SQL>selectcount（*）fromv$session
查看当前数据库建立的会话情况：
SQL>selectsid,serial#,username,program,machine,statusfromv$session;
查询应用的连接数SQL:
SQL>SELECTb.MACHINE,b.PROGRAM,COUNT（*）
FROMv$processa,v$sessionb
WHEREa.ADDR=b.PADDRANDb.USERNAMEISNOTNULL
GROUPBYb.MACHINE,b.PROGRAM
ORDERBYCOUNT（*）DESC;
查看当前数据库的并发连接数
SQL>selectcount（*）fromv$sessionwherestatus='ACTIVE';
查看当前有哪些用户正在使用数据：
selectosuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
fromv$sessiona,v$sqlareab
wherea.sql_address=b.address
orderbycpu_time/executionsdesc;
查看数据库指定用户的连接情况
SQL>selectsid,serial#fromv$sessionwhereusername='XX';XX为用户
例如：
SQL>selectsid,serial#fromv$sessionwhereusername='BSPDEV';
SIDSERIAL#
--------------------
2044609
3995841
5906041
清除用户下连接进程
SQL>altersystemkillsession'204,4609';
SQL>altersystemkillsession'399,5841';
SQL>altersystemkillsession'590,6041';
selectcount（*）fromv$process--当前的连接数
selectvaluefromv$parameterwherename='processes'--数据库允许的最大连接数
修改processes和sessions值
SQL>altersystemsetprocesses=300scope=spfile;
系统已更改。
SQL>altersystemsetsessions=335scope=spfile;
系统已更改。
重启数据库:
shutdownimmediate;
startup;
--查看当前有哪些用户正在使用数据
SELECTosuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
fromv$sessiona,v$sqlareab
wherea.sql_address=b.addressorderbycpu_time/executionsdesc;
修改processes和sessions值必须重启oracle服务器才能生效
ORACLE的连接数（sessions）与其参数文件中的进程数（process）有关，它们的关系如下：
sessions=（1.1*process+5）
3、查看数据库目录
SQL>select*fromall_directories;
4、查看数据库现有模式、是否归档
SQl>selectname,log_modefromv$database;
也可以用下面的语句
archiveloglist；（该方法需要assysdba）
查看数据库的创建日期和归档方式
SQL>SelectCreated,Log_Mode,Log_ModeFromV$Database;
5、配置用户密码过期时间
alterprofile"default"limitpassword_life_timeunlimited;配置用户密码永不过期
alterprofile"default"limitpassword_life_time100;配置用户密码100天过期
6、创建、配置新用户及查看用户属性
解锁新用户：
SQL>alteruserscottaccountunlock；
SQL>alteruserscottidentifiedbytiger;
删除oracle用户：
SQL>dropuserusernamecascade;（删除与用户相关的所有对象）
这样该用户包括该用户下的表，试图，同义词，过程，索引，及相关的一切就删除了。
创建用户并赋权限以及设置默认表空间。
以sysdba用户登陆进行以下设置：
--Createtheuser
createuserVHFSM
identifiedbyvhnj1fsm
defaulttablespaceMGRVHFSTBSDEF此处是设置默认表空间。
temporarytablespaceTEMP
profileDEFAULT
quotaunlimitedonmgrvhfstbs2010此处是设置可操作的其他表空间
quotaunlimitedonmgrvhfstbsdef;
--Grant/Revokeroleprivileges
grantconnecttoVHFSM;
grantdbatoVHFSM;
--Grant/Revokesystemprivileges
grantunlimitedtablespacetoVHFSM;
查看用户及角色权限
--1.查看所有用户：
select*fromdba_users;
select*fromall_users;
select*fromuser_users;
--2.查看用户或角色系统权限（直接赋值给用户或角色的系统权限）：
select*fromdba_sys_privs;
select*fromuser_sys_privs;
--3.查看角色（只能查看登陆用户拥有的角色）所包含的权限
sql>select*fromrole_sys_privs;
--4.查看用户对象权限：
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
--5.查看所有角色：
select*fromdba_roles;
--6.查看用户或角色所拥有的角色：
select*fromdba_role_privs;
select*fromuser_role_privs;
--7.查看哪些用户有sysdba或sysoper系统权限（查询时需要相应权限）
select*fromV$PWFILE_USERS
--注意：
--1、以下语句可以查看Oracle提供的系统权限
selectnamefromsys.system_privilege_map
--2、查看一个用户的所有系统权限（包含角色的系统权限）
SELECTprivilege
FROMdba_sys_privs
WHEREgrantee='DATAUSER'
UNION
SELECTprivilege
FROMdba_sys_privs
WHEREgranteeIN（SELECTgranted_roleFROMdba_role_privsWHEREgrantee='DATAUSER'）;
二、创建、管理Oracle表空间
1、先查询空闲空间
selecttablespace_name,file_id,block_id,bytes,blocksfromdba_free_space;
2、增加Oracle表空间
先查询数据文件名称、大小和路径的信息，语句如下：
selecttablespace_name,file_id,bytes,file_namefromdba_data_files;
3、修改文件大小语句如下
alterdatabasedatafile
'需要增加的数据文件路径，即上面查询出来的路径
'resize800M;
4、创建Oracle表空间
createtablespacetest
datafile'/home/app/oracle/oradata/oracle8i/test01.dbf'size8M
autoextendon
next5M
maxsize10M;
createtablespacesales
datafile'/home/app/oracle/oradata/oracle8i/sales01.dbf'size800M
autoextendon
next50M
maxsizeunlimited
maxsizeunlimited是大小不受限制
createtablespacesales
datafile'/home/app/oracle/oradata/oracle8i/sales01.dbf'size800M
autoextendon
next50M
maxsize1000M
extentmanagementlocaluniform;
unform表示区的大小相同，默认为1M
createtablespacesales
datafile'/home/app/oracle/oradata/oracle8i/sales01.dbf'size800M
autoextendon
next50M
maxsize1000M
extentmanagementlocaluniformsize500K;
unformsize500K表示区的大小相同，为500K
createtablespacesales
datafile'/home/app/oracle/oradata/oracle8i/sales01.dbf'size800M
autoextendon
next50M
maxsize1000M
extentmanagementlocalautoallocate;
autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区
createtablespacesales
datafile'/home/app/oracle/oradata/oracle8i/sales01.dbf'size800M
autoextendon
next50M
maxsize1000M
temporary;
temporary创建字典管理临时表空间，要创建本地管理临时表空间要加temporarytablespace关键字
createtemporarytablespacesales
tempfile'/home/app/oracle/oradata/oracle8i/sales01.dbf'size800M
autoextendon
next50M
maxsize1000M
创建本地管理临时表空间，如果是临时表空间，所有语句中的datafile都换为tempfile，所有语句中的datafile都换为tempfile8i
为表空间增加数据文件：
altertablespacesalesadd
datafile'/home/app/oracle/oradata/oracle8i/sales02.dbf'size800M
autoextendonnext50M
maxsize1000M;
5、查看表空间是否自动扩展
SQL>selectfile_id,file_name,tablespace_name,autoextensible,increment_byfromdba_data_filesorderbyfile_iddesc;
更改自动扩展属性：
alterdatabasedatafile
'/home/app/oracle/oradata/oracle8i/sales01.dbf',
'/home/app/oracle/oradata/oracle8i/sales02.dbf'
'/home/app/oracle/oradata/oracle8i/sales01.dbf
autoextendoff;
6、表空间的查看与修改
查看用户默认表空间
SQL>selectusername,default_tablespacefromdba_users;查看所有用户的默认表空间
SQL>selectusername,default_tablespacefromuser_users;查看某个用户的默认表空间，前提需要connect该用户。
SQL>selectUSERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACEfromdba_userswhereusername='hr';查看用户对应的默认表空间
修改用户默认表空间
SQL>alteruserzhanghrdefaulttablespacetest;
设置数据库的默认临时表空间:
SQL>Alterdatabasedefaulttemporarytablespacetemp_tbs_name;
查看用户和默认表空间的关系:
SQL>selectusername,default_tablespacefromdba_users;
查看临时表空间：
SQL>selectfile_name,file_id,blocks,user_blocksfromdba_temp_files；
SQL>selecttablespace_name,current_users,total_blocks,used_blocks,free_blocksfromv$sort_segment;
查看undo表空间
SQL>showparameterundo;
NAMETYPEVALUE
-----------------------------------------------------------------------------
undo_managementstringAUTO
undo_retentioninteger900
undo_tablespacestringUNDOTBS1
查看undo表空间大小
SQL>selectsum（bytes）/1024/1024"currentundosize（M）"fromdba_data_fileswheretablespace_name='UNDOTBS1';
通过增加数据文件来改变undo表空间大小
SQL>altertablespaceundotbs1
adddatafile'/oracle/oradata/orc6/undo02.dbf'size10M;
通过resize更改数据文件大小
SQL>alterdatabasedatafile'/oracle/oradata/orc6/undo02.dbf'resize100M;
查看某个表空间的数据文件
SQL>selectfile_name,tablespace_name,bytes/1024/1024"bytesMB",maxbytes/1024/1024"maxbytesMB"fromdba_data_fileswheretablespace_name='ORA1TBS';指定表空间名要大写
查看所有表空间大小
SQL>selecttablespace_name,sum（bytes）/1024/1024fromdba_data_files
groupbytablespace_name;
查看已使用的表空间大小
SQL>selecttablespace_name,sum（bytes）/1024/1024fromdba_free_space
groupbytablespace_name;
oracle查看表空间大小及使用率：
方法一：
SELECTUPPER（F.TABLESPACE_NAME）,D.TOT_GROOTTE_MB,D.TOT_GROOTTE_MB,F.TOTAL_BYTES,TO_CHAR（ROUND（（D.TOT_GROOTTE_MB-F.TOTAL_BYTES）/D.TOT_GROOTTE_MB*100,2）,'990.99'）,F.TOTAL_BYTES,F.MAX_BYTESFROM（SELECTTABLESPACE_NAME,ROUND（SUM（BYTES）/（1024*1024）,2）TOTAL_BYTES,ROUND（MAX（BYTES）/（1024*1024）,2）MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME）F,（SELECTDD.TABLESPACE_NAME,ROUND（SUM（DD.BYTES）/（1024*1024）,2）TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME）DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC;
方法二：
SELECTD.TABLESPACE_NAME,
SPACE||'M'"SUM_SPACE（M）",
BLOCKS"SUM_BLOCKS",
SPACE-NVL（FREE_SPACE,0）||'M'"USED_SPACE（M）",
ROUND（（1-NVL（FREE_SPACE,0）/SPACE）*100,2）||'%'
"USED_RATE（%）",
FREE_SPACE||'M'"FREE_SPACE（M）"
FROM（SELECTTABLESPACE_NAME,
ROUND（SUM（BYTES）/（1024*1024）,2）SPACE,
SUM（BLOCKS）BLOCKS
FROMDBA_DATA_FILES
GROUPBYTABLESPACE_NAME）D,
（SELECTTABLESPACE_NAME,
ROUND（SUM（BYTES）/（1024*1024）,2）FREE_SPACE
FROMDBA_FREE_SPACE
GROUPBYTABLESPACE_NAME）F
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME（+）
UNIONALL
SELECTD.TABLESPACE_NAME,
SPACE||'M'"SUM_SPACE（M）",
BLOCKSSUM_BLOCKS,
USED_SPACE||'M'"USED_SPACE（M）",
ROUND（NVL（USED_SPACE,0）/SPACE*100,2）||'%'"USED_RATE（%）",
NVL（FREE_SPACE,0）||'M'"FREE_SPACE（M）"
FROM（SELECTTABLESPACE_NAME,
ROUND（SUM（BYTES）/（1024*1024）,2）SPACE,
SUM（BLOCKS）BLOCKS
FROMDBA_TEMP_FILES
GROUPBYTABLESPACE_NAME）D,
（SELECTTABLESPACE_NAME,
ROUND（SUM（BYTES_USED）/（1024*1024）,2）USED_SPACE,
ROUND（SUM（BYTES_FREE）/（1024*1024）,2）FREE_SPACE
FROMV$TEMP_SPACE_HEADER
GROUPBYTABLESPACE_NAME）F
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME（+）
ORDERBY1;
方法三：
SELECT*
FROM（SELECTa.tablespace_name,
to_char（a.bytes/1024/1024,'99,999.999'）total_bytes,
to_char（b.bytes/1024/1024,'99,999.999'）free_bytes,
to_char（a.bytes/1024/1024-b.bytes/1024/1024,
'99,999.999'）use_bytes,
to_char（（1-b.bytes/a.bytes）*100,'99.99'）||'%'USE
FROM（
升级会员 