01
Oracle数据库实例、用户、目录及session会话查看
1、ORACLE SID查看设置查看SID、用户名$ env|grep SID 、select * from v$instance、select instance_name,host_name from v$instance;查看数据库所有用户及用户状态：SQL> select usernames,account_status from dba_users;设置SID$ export ORACLE_SID=hisvhfs查看数据库DBID：SQL>select * from v$DBID2、查询、设置Oracle数据库实例最大进程数及最大会话数查看系统最大session：SQL>show parameter session查看当前连接数：SQL>select count(*) from v$bgprocess查看系统最大进程数：SQL>show parameter process查看当前连接到数据库的用户：SQL>select count(*) from v$session查看当前数据库建立的会话情况：SQL> select sid,serial#,username,program,machine,status from v$session;查询应用的连接数SQL:SQL> SELECT b.MACHINE, b.PROGRAM, COUNT (*)FROM v$process a, v$session bWHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULLGROUP BY b.MACHINE, b.PROGRAMORDER BY COUNT (*) DESC;查看当前数据库的并发连接数SQL> 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;查看数据库指定用户的连接情况SQL> select sid,serial# from v$session where username='XX'; XX为用户例如：SQL> select sid,serial# from v$session where username='BSPDEV';SID SERIAL#---------- ----------204 4609399 5841590 6041清除用户下连接进程SQL> alter system kill session '204,4609';SQL> alter system kill session '399,5841';SQL> alter system kill session '590,6041';修改processes和sessions值 SQL> alter system set processes=300 scope=spfile; 系统已更改。 SQL> alter system set sessions=335 scope=spfile; 系统已更改。修改processes和sessions值必须重启oracle服务器才能生效ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下： sessions=(1.1*process+5)3、查看数据库目录SQL> select * from all_directories;4、查看数据库现有模式、是否归档SQl>select name,log_mode from v$database;也可以用下面的语句archive log list；(该方法需要as sysdba)查看数据库的创建日期和归档方式SQL> Select Created, Log_Mode, Log_Mode From V$Database;5、配置用户密码过期时间alter profile "default" limit password_life_time unlimited; 配置用户密码永不过期alter profile "default" limit password_life_time 100; 配置用户密码100天过期6、创建、配置新用户及查看用户属性解锁新用户：SQL> alter user scott account unlock；SQL> alter user scott identified by tiger;删除oracle用户：SQL>drop user username cascade; (删除与用户相关的所有对象)这样该用户包括该用户下的表，试图，同义词，过程，索引，及相关的一切就删除了。创建用户并赋权限以及设置默认表空间。以sysdba用户登陆进行以下设置：-- Create the usercreate user VHFSMidentified by vhnj1fsmdefault tablespace MGRVHFSTBSDEF 此处是设置默认表空间。temporary tablespace TEMPprofile DEFAULTquota unlimited on mgrvhfstbs2010 此处是设置可操作的其他表空间quota unlimited on mgrvhfstbsdef;-- Grant/Revoke role privilegesgrant connect to VHFSM;grant dba to VHFSM;-- Grant/Revoke system privilegesgrant 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.查看角色(只能查看登陆用户拥有的角色)所包含的权限sql>select * from role_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、以下语句可以查看Oracle提供的系统权限select name from sys.system_privilege_map--2、查看一个用户的所有系统权限(包含角色的系统权限)SELECT privilegeFROM dba_sys_privsWHERE grantee = 'DATAUSER'UNIONSELECT privilegeFROM dba_sys_privsWHERE grantee IN (SELECT granted_role FROM dba_role_privs WHERE grantee = 'DATAUSER');
02
创建、管理Oracle表空间
1、先查询空闲空间select tablespace_name,file_id,block_id,bytes,blocks from 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 testdatafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8Mautoextend onnext 5Mmaxsize 10M;create tablespace salesdatafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800Mautoextend onnext 50Mmaxsize unlimitedmaxsize unlimited 是大小不受限制create tablespace salesdatafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800Mautoextend onnext 50Mmaxsize 1000Mextent management local uniform;unform表示区的大小相同，默认为1Mcreate tablespace salesdatafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800Mautoextend onnext 50Mmaxsize 1000Mextent management local uniform size 500K;unform size 500K表示区的大小相同，为500Kcreate tablespace salesdatafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800Mautoextend onnext 50Mmaxsize 1000Mextent management local autoallocate;autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区create tablespace salesdatafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800Mautoextend onnext 50Mmaxsize 1000Mtemporary;temporary创建字典管理临时表空间 ，要创建本地管理临时表空间要加temporary tablespace关键字create temporary tablespace salestempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800Mautoextend onnext 50Mmaxsize 1000M创建本地管理临时表空间，如果是临时表空间，所有语句中的datafile都换为tempfile ，所有语句中的datafile都换为tempfile8i为表空间增加数据文件：alter tablespace sales adddatafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800Mautoextend on next 50Mmaxsize 1000M;5、查看表空间是否自动扩展SQL> select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;更改自动扩展属性：alter database datafile'/home/app/oracle/oradata/oracle8i/sales01.dbf','/home/app/oracle/oradata/oracle8i/sales02.dbf''/home/app/oracle/oradata/oracle8i/sales01.dbfautoextend off;6、表空间的查看与修改查看用户默认表空间SQL> select username,default_tablespace from dba_users;查看所有用户的默认表空间SQL> select username,default_tablespace from user_users;查看某个用户的默认表空间，前提需要connect该用户。SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username = 'hr'; 查看用户对应的默认表空间修改用户默认表空间SQL> alter user zhanghr default tablespace test;设置数据库的默认临时表空间:SQL>Alter database default temporary tablespace temp_tbs_name;查看用户和默认表空间的关系:SQL>select username,default_tablespace from dba_users;查看临时表空间：SQL> select file_name,file_id,blocks,user_blocks from dba_temp_files；SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;查看undo表空间SQL> show parameter undo;NAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1查看undo表空间大小SQL> select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';通过增加数据文件来改变undo表空间大小SQL> alter tablespace undotbs1add datafile '/oracle/oradata/orc6/undo02.dbf' size 10M;通过resize更改数据文件大小SQL> alter database datafile '/oracle/oradata/orc6/undo02.dbf' resize 100M;查看某个表空间的数据文件SQL> select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB" from dba_data_files where tablespace_name='ORA1TBS';指定表空间名要大写查看所有表空间大小SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_filesgroup by tablespace_name;查看已使用的表空间大小SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_spacegroup by tablespace_name;oracle查看表空间大小及使用率：方法一：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.MAX_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 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_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 ( SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,SUM (BLOCKS) BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,( SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)UNION ALLSELECT D.TABLESPACE_NAME,SPACE || 'M' "SUM_SPACE(M)",BLOCKS SUM_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 ( SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,SUM (BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,( SELECT TABLESPACE_NAME,ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)ORDER BY 1;方法三：SELECT *FROM (SELECT a.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') || '%' 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, '99,999.999') total_bytes,to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' USEFROM (SELECT tablespace_name, SUM(bytes) bytesFROM dba_temp_filesGROUP BY tablespace_name) c,(SELECT tablespace_name, SUM(bytes_cached) bytes_usedFROM v$temp_extent_poolGROUP BY tablespace_name) dWHERE c.tablespace_name = d.tablespace_name)ORDER BY tablespace_name;查看表空间使用大小情况一SQL> select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"from(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name ) bwhere a.tablespace_name=b.tablespace_nameorder by ((a.bytes-b.bytes)/a.bytes) desc;TABLESPACE_NAME Sum MB used MB free MB percent_used------------------------------ ---------- ---------- ---------- ------------SYSAUX 490 461.0625 28.9375 94.09UNDOTBS1 75 66.8125 8.1875 89.08USERS 5 1.3125 3.6875 26.25ORA1TBS 400 2 398 .5ORA2TBS 400 2 398 .5查看表空间使用大小情况二SQL> select a.tablespace_name,total,free,total-free used from2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_filesgroup by tablespace_name) a,( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_spacegroup by tablespace_name) bwhere a.tablespace_name=b.tablespace_name; 3 4 5 6TABLESPACE_NAME TOTAL FREE USED------------------------------ ---------- ---------- ----------USERS 5 .9375 4.0625TESTTB 500 499 1SYSAUX 550 30 520EXAMPLE 100 20.75 79.25UNDOTBS1 110 96.625 13.375在SQL命令行情况下将结果输出到指定文件中。SQL> spool out.txtSQL> select * from v$database;SQL> spool off
03
查看、管理ORACLE数据文件
查看数据文件：SQL> select name from v$datafile;更改数据文件大小：SQL> alter database datafile '/oradata2/hisvhfs/undotbs01.dbf' resize 30G;查看用户所有表空间及对应的数据文件和数据文件大小SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;查看数据文件状态及大小SQL> select name,BYTES,STATUS ,FILE# from v$datafile;查看所有数据文件SQL> select name from v$datafile;查看所有表空间对应的数据文件SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
04
查看、管理ORACLE表
查看所有segment的大小。Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name查询表的大小和表空间的大小有两种含义的表大小。一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：当前实例以scott用户登录，并创建dept表的副本dept_copy2为例。SQL> select segment_name, bytes2 from user_segments3 where segment_type = 'TABLE';SEGMENT_NAME BYTES-------------------------------------------------------------------------------- ----------DEPT_COPY2 65536DEPT_COPY 65536BIN$7Sa/taXJEKHgQ2kFqMCxMQ==$0 65536ITEMS 65536SALGRADE 65536EMP 65536DEPT 655367 rows selected或者SQL> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;SEGMENT_NAME SUM(BYTES)/1024/1024-------------------------------------------------------------------------------- --------------------DEPT 0.0625PK_DEPT 0.0625EMP 0.0625DEPT_COPY 0.0625DEPT_COPY2 0.0625ITEMS 0.0625PK_EMP 0.0625SALGRADE 0.06258 rows selected另一种表实际使用的空间。这样查询：SQL> select table_name from user_tables;TABLE_NAME------------------------------DEPTEMPBONUSSALGRADEanalyze table emp compute statistics;ITEMSDEPT_COPYDEPT_COPY2SQL> analyze table DEPT_COPY2 compute statistics;查看某个表属于哪个表空间：SQL> select tablespace_name from all_tables where table_name='SYS_EXPORT_FULL_01';创建表时指定表空间：SQL>create table a (name varchar(10)) tablespace test;查看数据库表结构：SQL> desc dept_copy2;Name Type Nullable Default Comments------ ------------ -------- ------- --------DEPTNO NUMBER(2) YDNAME VARCHAR2(14) YLOC VARCHAR2(13) Y修改表的列名：SQL> alter table users rename column ID to PID;修改表的列的字符大小SQL> alter table student modify class varchar2(10);创建主键：SQL> alter table users add constraint pk_users primary key(name);修改表的列数据类型：SQL> select * from users;NAME AGE PID---------- ---------- ----------SQL> alter table users add(newColumn varchar2(20));SQL> update users set newColumn = PID;SQL> commit;SQL> alter table users drop column PID cascade constraints;SQL> alter table users rename column newColumn to PID;SQL> commit;SQL> desc users;名称 是否为空? 类型----------------------------------------- -------- ---------NAME NOT NULL VARCHAR2(10)AGE NUMBERPID VARCHAR2(20)SQL> select * from users;NAME AGE PID---------- ---------- --------------------创建序列：SQL> create sequence seq_PIDstart with 3201231988001010101maxvalue 3201231999999999999minvalue 3201231988001010101nocyclecache 100;创建触发器：SQL> create or replace trigger trigger_usersbefore insert on usersfor each rowbeginselect seq_PID.nextval into :new.PID from dual;end;/查看用户序列SQL> select sequence_name from USER_SEQUENCES;SEQUENCE_NAME------------------------------SEQ_ID查看用户触发器SQL> select * from user_triggers where table_name = upper('job');通过group by 语句使用rollup运算符汇总ID列SQL> select ID,avg(age),sum(age),max(age),count(*)2 from job group by rollup(ID);ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*)---------- ---------- ---------- ---------- ----------1583 25 25 25 11584 24 24 24 11585 25 25 25 11586 25 25 25 11587 23 23 23 11683 22 22 22 11684 24 24 24 11685 25 25 25 11686 24 24 24 11687 22 22 22 11688 28 28 28 1ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*)---------- ---------- ---------- ---------- ----------1689 26 26 26 11690 26 26 26 11783 35 35 35 11784 11883 24 24 24 11884 125.2 378 35 17having子句与group by 子句一般一起使用，可以在汇总相关数据后进一步筛选汇总的数据。SQL> select ID,avg(age),sum(age),max(age),count(*)2 from job group by id having avg(age)>25;ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*)---------- ---------- ---------- ---------- ----------1690 26 26 26 11783 35 35 35 11689 26 26 26 11688 28 28 28 1多表连接查询SQL> select job.name,job.id,job.job,job.age,users.name,users.age from job,users2 where job.name=users.name;NAME ID JOB AGE NAME AGE-------- ---------- ---------- ---------- ---------- ----------desc 在oracle数据库里有两种用法，一是查询表的结构 格式：desc table_name二是在用排序时(order by)是降序的关键字 格式 ：order by column_name desc;查看表的segment大小，即表实际占用的物理大小，无论是否使用。SQL> select segment_name,partition_name,bytes,blocks,extents from user_segments where segment_name='JOBS'; （segment名，即表明）SQL> select segment_name,partition_name,count(extent_id),sum(bytes),sum(blocks) from user_extents where segment_name='JOBS' group by segment_name,partition_name;
05
查看、管理ORACLE常用的参数、配置等
查看控制文件：SQL> select name from v$controlfile；查看重做日志文件，简称日志文件：SQL> select * from v$logfile;查看表的索引：SQL>select index_name,index_type,table_name from user_indexes where table_name='表名'；修改oracle时间格式：SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';指定开启某个监听：SQL>lsnrctl start orc5_lisenter(此处是当初建监听时创建的监听名)数据库SCN及时间查询。SQL> select sysdate from dual; 查看数据库时间SYSDATE-----------------20131216 23:52:55SQL> select dbms_flashback.get_system_change_number from dual; 查看当前数据库的SCN号GET_SYSTEM_CHANGE_NUMBER------------------------1583042SQL> select to_char(scn_to_timestamp(1583042),'yyyy-mm-dd hh24:mi:ss') from dual; 根据数据库的SCN号查找对应的数据库时间TO_CHAR(SCN_TO_TIME-------------------2013-12-16 23:45:38SQL> select timestamp_to_scn(to_date('2013-12-16 15:30:19','yyyy-mm-dd hh24:mi:ss')) from dual; 根据数据库的时间查找对应的数据库SCN号TIMESTAMP_TO_SCN(TO_DATE('2013-12-1615:30:19','YYYY-MM-DDHH24:MI:SS'))----------------------------------------------------------------------1519388SQL> select * from (select time_dp,scn from smon_scn_time order by time_dp desc) where rownum<10; 条件查找，查找rownum小于10的SCN以对应的时间。TIME_DP SCN----------------- ----------20131216 15:53:02 158457420131216 15:46:45 158309820131216 15:45:20 158302220131216 15:39:08 158239920131216 15:37:01 158199820131216 15:31:08 157991520131216 14:46:02 157721020131216 14:40:16 157619620131216 14:35:12 15755979 rows selected.数据库游标设置SQL> show parameter open_cursors; 查看游标参数SQL> select count(*) from v$open_cursor; 查看打开的游标SQL> select name,value from v$sysstat where name ='opened cursors current';查看当前打开的游标SQL> alter system set open_cursors=3000 scope=both; 修改默认游标数
06
查看、配置ORACLE重做日志
创建日志文件组：SQL> alter database add logfile group 4('/oracle/oradata/orc6/redo04.log')size 10M;查看日志文件组SQL> select group#,status,member from v$logfile;为日志文件组增加日志文件：SQL> alter database add logfile member '/oracle/oradata/orc6/redo05.log' to group 3;查看日志文件大小SQL> select GROUP#,BYTES,BLOCKSIZE,MEMBERS from v$log;
07
desc的常用查询
SQL> desc dba_temp_files; 查询临时表空间SQL> desc v$database; 查看数据库SQL> desc dba_data_files; 查看数据文件SQL> desc user_segments; 查看oracle segment（段）SQL> desc dba_segments; 查看ORACLE segmentSQL> desc dba_tables; 查看表SQL> desc dba_objects 查看对象SQL> desc dba_users; 查看用户SQL> desc dba_tablespaces; 查看表空间SQL> desc user_segments; 查看数据段SQL> desc dba_jobs； 查看jobSQL> desc dba_role_privs； 查看角色权限SQL> desc dba_constraints 查看约束SQL> desc dba_cons_columns 查看列约束SQL> show parameter log_archive_dest; 查看archive log所在位置SQL> archive log list; 查看归档目录以及log sequenceSQL> select * from V$FLASH_RECOVERY_AREA_USAGE; 查看flash_recovery_area 使用情况。SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage; 计算flash_recovery_area已经占用的空间。SQL> show parameter recover; 查找recovery目录SQL> desc v$process 通过v$process视图，我们获的是当前系统中所有进程信息，包括“后台进程”，也包括“服务器进程”。SQL> select addr, program from v$process;SQL> desc v$bgprocess 通过v$bgprocess视图,我们获得当前系统中启动的Oracle“后台进程”信息SQL> select paddr, name from v$bgprocess where paddr<>'00';通过v$bgprocess.paddr与v$process.addr关联起来的，关联后查询结果显而易见只会有Oracle“后台进程”的信息SQL> select a.paddr, a.name, a.description from v$bgprocess a, v$process b where a.paddr=b.addr;
08
查看ASM磁盘组信息
SQL> desc v$asm_disk；SQL> desc v$asm_diskgroup;
09
常用SQL命令行设置
1、setset wrap on/off 查询返回的纪录每行超过默认宽度时，可选择换行（on ）或不换行（off）,默认为换行；set linesize N 设置查询返回的纪录每行的宽度，超过这个宽度则截掉，不过这个宽度则补空格。
10
启动数据库
SQL> startup
11
停止数据库
SQL> shutdown
12
查看oracle版本
SQL> select * from v$version;
13
查看database 相关信息
SQL> select name from v$database;
14
查看用户
SQL> select * from all_users;
15
查看表空间
SELECT t.tablespace_name FROM dba_tablespaces t;
16
删除表空间
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
17
查看并发连接数
SQL> select count(*) from v$session where status='ACTIVE';
18
查询内存分配情况
SQL> select * from v$sga;
19
查看实时连接数
SQL> select count(*) from v$session;
20
查看pfile文件位置
SQL> show parameter pfile;
往期 精彩回顾
Linux环境搭建 | 手把手教你如何安装CentOS7虚拟机
长按二维码
关注我们吧
点击“阅读原文”打开新页面
本文分享自微信公众号 - 杰哥的IT之旅（Jake_Internet）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
