oracle学习笔记-常用sql（一）

查看当前用户的信息（如表空间）：
select * from user_users

查看所有用户信息（如表空间）：
select * from dba_users
select * from all_users

查看当前用户下所有的表：
select * from user_tables
select * from user_all_tables

查看所有表名（其中包括系统表）
select table_name  from all_tables
查看所有的表:
select  *  from  tab/dba_tables/dba_objects/cat; 
select * from DBA_OBJECTS/ALL_OBJECTS/USER_OBJECTS/OBJ

查看数据库中所有的表空间：
select tablespace_name from dba_data_files
select tablespace_name from dba_tablespaces
select * from v$tablespace;

查看指定用户所拥有的表空间（已经使用了哪些表空间），用户名需大写：
select distinct tablespace_name from dba_tables where owner='USER_NAME'
select distinct tablespace_name from dba_indexes where owner='USER_NAME'

查看指定用户在指定表空间下的表：
select table_name,tablespace_name,owner from dba_tables where tablespace_name='TABSPACE_NAME' and owner='USER_NAME'

查看表空间中数据文件的全路径：
select * from dba_data_files
select * from v$datafile

查看表空间的名字及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

查看哪些用户使用了该表空间
select distinct owner from dba_segments where tablespace_name ='TABLESPACE_NAME'

查看视图的名称
select * from user_views;
select * from all_views;
select * from dba_views;

查看同义词的名称
select * from user_synonyms;
select * from all_synonyms;
select * from dba_synonyms;

查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name='TABLE_NAME';
查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('index_name');

查看序列号，last_number是当前值
select * from user_sequences;

查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,'Y')>0;

删除表空间：
drop tablespace AHTBSPACETEST including contents and datafiles cascade constraints

增加表空间
ALTER TABLESPACE USR_DATA ADD DATAFILE '/oradata/SID/USR_DATA02.DBF' SIZE 10M AUTOEXTEND ON NEXT 10M maxsize unlimited;
ALTER TABLESPACE temptsp ADD TEMPFILE '/oradata/SID/TEMPTSP02.DBF' SIZE 10M AUTOEXTEND ON NEXT 10M maxsize unlimited;

查询当前索引的状态：
select distinct index_name,table_owner,table_name,status from user_indexes

查看索引被索引的字段
select * from user_ind_columns where index_name=upper('index_name');

查看某表的约束条件
select * from user_constraints;
select * from user_cons_columns;

查看函数和过程的状态
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码
select text from all_source where owner=user and name=upper('&plsql_name');
查询所有函数和储存过程
select * from user_source

创建用户：
1、创建临时表空间：
create temporary tablespace temptsp tempfile '/oradata/SID/TEMPTSP01.DBF'size 10m autoextend on next 10m maxsize 100m extent management local
2、创建数据表空间：
create tablespace USR_data logging datafile '/oradata/SID/USR_DATA01.DBF' size 10m autoextend on next 10m maxsize 100m extent management local
3、创建用户并指定表空间：
create user USRNAME identified by usrname default tablespace usr_data temporary tablespace temptsp
4、给用户授予权限：
grant connect,resource to USRNAME

修改密码
alter user USRNAME identified by USRNAME1;

删除用户以及跟用户关联的对象
drop user USRNAME CASCADE;

给用户赋予DBA权限：
grant dba to USRNAME
系统权限unlimited tablespace是隐含在dba, resource角色中的一个系统权限. 当用户得到dba或resource的角色时, unlimited tablespace系统权限也隐式受权给用户.
系统权限unlimited tablespace不能被授予role, 可以被授予用户.
DBA角色，是授权数据库管理员的权限
CONNECT角色， 是授予最终用户的典型权利，最基本的  一个（CREATE SESSION）
RESOURCE角色，是授予开发人员的  默认有八个权限（CREATE SEQUENCE,CREATE TRIGGER,CREATE CLUSTER,CREATE PROCEDURE,CREATE TYPE,CREATE OPERATOR,CREATE TABLE,CREATE INDEXTYPE）
exp_full_database角色，拥有导出数据库的权限
imp_full_database角色，拥有导入数据库的权限
GRANT CONNECT,RESOURCE,DBA,unlimited tablespace,CREATE  SESSION,CREATE ANY SEQUENCE,CREATE ANY TABLE,        
CREATE ANY VIEW ,CREATE ANY INDEX,CREATE ANY PROCEDURE,    
CREATE ANY DIRECTORY,ALTER  SESSION,ALTER ANY SEQUENCE,     
ALTER ANY TABLE,ALTER ANY INDEX,        
ALTER ANY PROCEDURE,DROP ANY SEQUENCE,     
DROP ANY TABLE,DROP ANY VIEW ,DROP ANY INDEX,        
DROP ANY PROCEDURE,DROP ANY DIRECTORY,    
SELECT ANY TABLE,SELECT ANY DICTIONARY,INSERT ANY TABLE, 
UPDATE ANY TABLE,DELETE ANY TABLE,DEBUG ANY PROCEDURE,
DEBUG CONNECT SESSION,exp_full_database,imp_full_database     
TO USER_NAME;

撤销用户对所有表空间都可使用的权限（相当于撤销用户在表空间上的DBA权限）：
revoke unlimited tablespace from USRNAME

为用户取消角色
revoke resource from USRNAME;

将用户在SYSTEM表空间的配额置为0：
alter user USRNAME quota 0 on SYSTEM

设置用户在USR_DATA表空间上配额不受限
alter user USRNAME quota unlimited on USR_DATA

修改表空间：
alter table TABLE_NAME move tablespace USR_TABLESPACE //修改表TABLE_NAME表空间
alter index TABLE_INDEX rebuild tablespace USR_TABLESPACE //修改索引的表空间

查看用户拥有那些角色
select * from dba_role_privs a where a.grantee='USRNAME';
select * from dba_sys_privs a where a.grantee='USRNAME';

查看角色拥有那些权限
select ROLE, PRIVILEGE from role_sys_privs where role='RESOURCE';   --RESOURCE，CONNECT，DBA
select grantee,privilege from dba_sys_privs where grantee='RESOURCE';

查看当前用户权限
select * from session_privs
查看用户系统权限：
select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;

查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

查看所有角色：
select * from dba_roles;
查看用户所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
查看角色所拥有的权限:
select * from role_sys_privs;
select * from role_tab_privs;

查看所有系统权限
select * from system_privilege_map;
查看所有对象权限
select * from table_privilege_map;

查看当前用户连接
select * from v$Session

查看Oracle的版本号。
select * from v$version
SELECT *  FROM product_component_version

获取表字段：
select * from user_tab_columns where Table_Name='TAB_NAME';
select * from all_tab_columns where Table_Name='TAB_NAME';
select * from dba_tab_columns where Table_Name='TAB_NAME';

查看表结构
desc表名

获取表注释：
select * from user_tab_comments
select * from dba_tab_comments
select * from all_tab_comments

获取字段注释：
select * from user_col_comments
select * from dba_col_comments
select * from all_col_comments

查看用户表空间使用情况：
select a.file_id,a.tablespace_name,a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used", 
sum(nvl(b.bytes,0)) "Free", sum(nvl(b.bytes,0))/a.bytes*100 "%free" from dba_data_files a, dba_free_space b 
where a.file_id=b.file_id(+) group by a.tablespace_name, a.file_id,a.bytes order by a.tablespace_name;

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') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name

select 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') || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name

查看sid
select * from v$instance

查看用户下表使用情况
select t.table_name,t.num_rows,t.blocks,t.empty_blocks from user_tables t