1、 select count(*) from v$session where status=ACTIVE;查看当前有哪些用户正在使用数据:select osuser,a.username,cpu_time/executions/1000000|s,sql_fulltext,machinefrom v$session a,v$sqlarea bwhere a.sql_address = b.addressorder by cpu_time/executions desc;查看数据库指定用户的连接情况 select sid,serial# from v$session where username=
2、XX XX为用户例如:BSPDEVSID SERIAL#- -204 4609399 5841590 6041清除用户下连接进程 alter system kill session 204,4609399,5841590,6041修改processes和sessions值SQL alter system set processes=300 scope=spfile;系统已更改。 alter system set sessions=335 scope=spfile;修改processes和sessions值必须重启oracle服务器才能生效ORACLE的连接数(sessions)与其参数文件中的
3、进程数(process)有关,它们的关系如下:sessions=(1.1*process+5)1.3 查看数据库目录 select * from all_directories;1.4 查看数据库现有模式、是否归档SQlselect name,log_mode from v$database;也可以用下面的语句archive log list;(该方法需要as sysdba)查看数据库的创建日期和归档方式 Select Created, Log_Mode, Log_Mode From V$Database; 1.5 配置用户密码过期时间alter profile default limit p
4、assword_life_time unlimited; 配置用户密码永不过期 limit password_life_time 100; 配置用户密码100天过期1.6 创建、配置新用户及查看用户属性解锁新用户: alter user scott account unlock; alter user scott identified by tiger;删除oracle用户:drop user username cascade; (删除与用户相关的所有对象)这样该用户包括该用户下的表,试图,同义词,过程,索引,及相关的一切就删除了。创建用户并赋权限以及设置默认表空间。以sysdba用户登陆进行
5、以下设置:- Create the user create user VHFSMidentified by vhnj1fsmdefault tablespace MGRVHFSTBSDEF 此处是设置默认表空间。temporary tablespace TEMPprofile DEFAULTquota unlimited on mgrvhfstbs2010 此处是设置可操作的其他表空间quota unlimited on mgrvhfstbsdef;- Grant/Revoke role privileges grant connect to VHFSM;grant dba to VHFSM;
6、- Grant/Revoke system privileges grant unlimited tablespace to VHFSM;查看用户及角色权限-1.查看所有用户:select * from dba_users;select * from all_users;select * from user_users;-2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):select * from dba_sys_privs;select * from user_sys_privs;-3.查看角色(只能查看登陆用户拥有的角色)所包含的权限sqlselect * from role_
7、sys_privs;-4.查看用户对象权限:select * from dba_tab_privs;select * from all_tab_privs;select * from user_tab_privs;-5.查看所有角色:select * from dba_roles;-6.查看用户或角色所拥有的角色:select * from dba_role_privs;select * from user_role_privs;-7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)select * from V$PWFILE_USERS-注意:-1、以下语句可以查看O
8、racle提供的系统权限select name from sys.system_privilege_map-2、查看一个用户的所有系统权限(包含角色的系统权限)SELECT privilegeFROM dba_sys_privsWHERE grantee = DATAUSERUNIONWHERE grantee IN (SELECT granted_role FROM dba_role_privs WHERE grantee = );2 创建、管理Oracle表空间1、先查询空闲空间select tablespace_name,file_id,block_id,bytes,blocks fro
9、m dba_free_space;2、增加Oracle表空间先查询数据文件名称、大小和路径的信息,语句如下:select tablespace_name,file_id,bytes,file_name from dba_data_files;3、修改文件大小语句如下alter database datafile 需要增加的数据文件路径,即上面查询出来的路径 resize 800M;4、创建Oracle表空间create tablespace test datafile /home/app/oracle/oradata/oracle8i/test01.dbf size 8M autoextend
10、 on next 5M maxsize 10M;create tablespace sales /home/app/oracle/oradata/oracle8i/sales01.dbf size 800M next 50M maxsize unlimited maxsize unlimited 是大小不受限制 maxsize 1000M extent management local uniform;unform表示区的大小相同,默认为1M extent management local uniform size 500K;unform size 500K表示区的大小相同,为500K ext
11、ent management local autoallocate;autoallocate表示区的大小由随表的大小自动动态改变,大表使用大区小表使用小区 temporary;temporary创建字典管理临时表空间 ,要创建本地管理临时表空间要加temporary tablespace关键字 create temporary tablespace sales tempfile 创建本地管理临时表空间,如果是临时表空间,所有语句中的datafile都换为tempfile ,所有语句中的datafile都换为tempfile8i为表空间增加数据文件:alter tablespace sales
12、add /home/app/oracle/oradata/oracle8i/sales02.dbfautoextend on next 50M maxsize 1000M;5、查看表空间是否自动扩展 select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;更改自动扩展属性:, /home/app/oracle/oradata/oracle8i/sales01.dbf autoextend off;6、表空间的查看与修改查看用户默认
13、表空间 select username,default_tablespace from dba_users;查看所有用户的默认表空间 select username,default_tablespace from user_users;查看某个用户的默认表空间,前提需要connect该用户。select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username = hr 查看用户对应的默认表空间修改用户默认表空间 alter user zhanghr default tablespace tes
14、t;设置数据库的默认临时表空间:Alter database default temporary tablespace temp_tbs_name;查看用户和默认表空间的关系:select username,default_tablespace from dba_users;查看临时表空间: select file_name,file_id,blocks,user_blocks from dba_temp_files; select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_se
15、gment;查看undo表空间 show parameter undo;NAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1查看undo表空间大小 select sum(bytes)/1024/1024 current undo size(M) from dba_data_files where tablespace_name=UNDOTBS1通过增加数据文件来改变undo表空间大小 alter tablespace undotbs1add
16、 datafile /oracle/oradata/orc6/undo02.dbf size 10M;通过resize更改数据文件大小 alter database datafile resize 100M;查看某个表空间的数据文件 select file_name,tablespace_name,bytes/1024/1024 bytes MB,maxbytes/1024/1024 maxbytes MBORA1TBS指定表空间名要大写查看所有表空间大小 select tablespace_name,sum(bytes)/1024/1024 from dba_data_filesgroup
17、by tablespace_name;查看已使用的表空间大小 select tablespace_name,sum(bytes)/1024/1024 from dba_free_spaceoracle查看表空间大小及使用率:方法一:SELECT UPPER(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.M
18、AX_BYTES FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP
19、 BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;方法二:SELECT D.TABLESPACE_NAME, SPACE | M SUM_SPACE(M)BLOCKS SUM_BLOCKSSPACE - NVL (FREE_SPACE, 0) | USED_SPACE(M)ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) | %USED_RATE(%)FREE_SPACE | FREE_SPACE(M)FROM ( SE
20、LECT 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_
21、NAME(+) UNION ALL BLOCKS SUM_BLOCKS, USED_SPACE | ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) | NVL (FREE_SPACE, 0) | FROM DBA_TEMP_FILES ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER ORDER BY 1;方法三:SELECT *FR
22、OM (SELECT a.tablespace_name,to_char(a.bytes / 1024 / 1024, 99,999.999) total_bytes,to_char(b.bytes / 1024 / 1024, ) free_bytes,to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,) use_bytes,to_char(1 - b.bytes / a.bytes) * 100, 99.99) | USEFROM (SELECT tablespace_name, SUM(bytes) bytesFROM dba_data_filesGROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) bytesFROM dba_free_spaceGROUP BY tablespace_name) bWHERE a.tablespace_name = b.tablespace_nameUNION ALLSELECT c.tablespace_name,to_char(c.bytes / 1024 / 1024, to_char(c
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1