数据库常用sql

==1、基础查询==================================================

--查看所有用户


--查看数据库版本
select * from v$version;

--查看数据库字符集
select * from nls_database_parameters;

--查看当前的数据库连接数  
select count(*) from v$process ;    
--数据库允许的最大连接数  
select value from v$parameter where name ='processes';  
--修改数据库最大连接数  
alter system set processes = 300 scope = spfile;  --重启生效:

--解决无法导出空表的方法，设置deferred_segment_creation参数
alter system set deferred_segment_creation=false;
--设置密码不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


--查看内存
show parameter sga;
show parameter pga;


--查询当前用户的视图、表、触发器、存储函数和存储过程、索引等信息
select * from user_views;
select * from user_tables;
select * from user_triggers;
select * from user_procedures;
select * from user_indexes;
select * from user_sequences;

==锁表=========================================================
--查看是否有被锁的表：
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id

--查看是哪个进程锁的
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time

-- 杀掉进程
alter system kill session 'sid,serial#';


==查看执行的sql=========================================================
--查看正在执行的SQL:
select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value  and a.username is not null;

--正在执行的
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  from v$session a, v$sqlarea b 
where a.sql_address = b.address ;

--执行过的
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between '2001-10-15/09:24:47' and
       '2001-10-15/09:24:47' order by b.FIRST_LOAD_TIME 

--查找前十条性能差的sql.
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, 
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea 
order BY disk_reads DESC )where ROWNUM<10 ; 

--查看占io较大的正在运行的session
col sql_address format a20
col event format a20
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status, 
se.terminal,se.program,se.MODULE,se.sql_address,st.event,st. 
p1text,si.physical_reads, 
si.block_changes FROM v$session se,v$session_wait st, 
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st. 
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st. 
wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC

==2、表空间操作=================================================

--删除表空间
drop tablespace 表空间名称 including contents and datafiles cascade constraint;
drop user 用户 cascade;
--创建表空间、用户
select name from v$datafile; --查出来表空间文件默认存储的路径
--建立表空间
CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M;             
--建立UNDO表空间
CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
--建立临时表空间
CREATE TEMPORARY TABLESPACE temp_data TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M
--修改表空间文件位置
alter database rename file '/u01/app/oracle/orcl/data.dbf' to '/oracle/data.dbf'

==改变表空间状态==
--使表空间脱机
ALTER TABLESPACE TEST OFFLINE;
--如果是意外删除了数据文件，则必须带有RECOVER选项
ALTER TABLESPACE game OFFLINE FOR RECOVER;
--使表空间联机
ALTER TABLESPACE TEST ONLINE;
--使表空间只读
ALTER TABLESPACE game READ ONLY;
--使表空间可读写
ALTER TABLESPACE game READ WRITE;
==改变数据文件状态==
--使数据文件脱机
ALTER DATABASE DATAFILE 3 OFFLINE;
--使数据文件联机
ALTER DATABASE DATAFILE 3 ONLINE;

--扩展表空间

1.增加数据文件
ALTER TABLESPACE game
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
RESIZE 4000M;

3.设定数据文件自动扩展
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;