常用oracle SQL语句

1:创建dblink 
create database link dblink_name 
  connect to userA IDENTIFIED BY userA 
  using '192.168.0.1/ora92'; 
select object_name from dba_objects where object_type='DATABASE LINK'

2:查看表空间 
select dbf.tablespace_name, 
dbf.totalspace "总量(M)", 
dbf.totalblocks as 总块数, 
dfs.freespace "剩余总量(M)", 
dfs.freeblocks "剩余块数", 
(dfs.freespace / dbf.totalspace) * 100 "空闲比例" 
from (select t.tablespace_name, 
sum(t.bytes) / 1024 / 1024 totalspace, 
sum(t.blocks) totalblocks 
from dba_data_files t 
group by t.tablespace_name) dbf, 
(select tt.tablespace_name, 
sum(tt.bytes) / 1024 / 1024 freespace, 
sum(tt.blocks) freeblocks 
from dba_free_space tt 
group by tt.tablespace_name) dfs 
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name) 

3:创建表空间 
CREATE TABLESPACE MARRYDB_USER 
DATAFILE '/app/oracle/oradata/user0.dbf ' SIZE 500M 
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT  LOCAL SEGMENT SPACE MANAGEMENT AUTO 

4:查看表空间文件: 
select * from dba_data_files 

5:创建临时表空间: 
CREATE TEMPORARY TABLESPACE temp_data 
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M 

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

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

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

9:查看临时表空间文件 
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(+) 

10:修改字段 
ALTER TABLE tableA 
MODIFY valid_flag NUMBER(1) NOT NULL 

11:查询当前用户下的存储过程 
SELECT * 
FROM user_source 
WHERE type='PROCEDURE' 
AND name='INSERT_DICTIONARY' 

 

12：查询当前用户所属的角色

SELECT * FROM USER_ROLE_PRIVS

 13:查看数据库参数

SELECT *
FROM nls_database_parameters

查看数据库编码

SELECT *
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';

设置bash编码

export NLS_LANG='AMERICAN_AMERICA.UTF8'

14.查询oracle数据库的实例名

select instance_name from v$instance

15.查询邻接表:

从树根向,叶子递归查询.

查询12022701部门和它的子部门:

SELECT * FROM sys_dept START WITH id=12022701 CONNECT BY prior id=pid;

从叶子递归向树根查询:

查询12022766的所有祖先部门:

select * from sys_dept start with id=12022766  connect by prior pid = id;