--查询表空间
Select distinct a.tablespace_name From Dba_Tablespaces a ;
--查询表空间对应的文件路径
select file_name , tablespace_name from dba_data_files;
select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;
--查询用户缺省表空间
select username,default_tablespace from user_users
select username,default_tablespace from dba_users;--dba
查看当前用户的角色
select * from user_role_privs;
--查看角色对应的权限；
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='DBA';
查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
--查询表空间名称，大小，剩余空间，已使用空间（M）
select a.tablespace_name, total, free, total - free as used
from (select tablespace_name, sum(bytes) / 1024 / 1024 as total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
--创建用户
create user xy identified by xy default tablespace "XY" temporary tablespace temp;
--授予用户特权
grant all privileges to djcheck;
--取消用户权限
revoke create view from sam;
--用户授权
grant connect,resource to xy;
grant dba to xy;
revoke unlimited tablespace from xy;
alter user xy quota 0 on "USERS";
alter user xy quota unlimited on "USERS";
--删除用户
alter user XY account unlock;
select username,sid,serial# from v$session where username='XY';
select 'alter system kill session ' || '''' || sid || ',' || serial# || ''' ;' from v$session where username='XY' and status!='KILLED';
select count(*) from v$session where username='XY' and status!='KILLED';
select saddr,sid,serial#,paddr,username,status from v$session where username='XY' and status!='KILLED';
alter system kill session '553,19';
drop user xy cascade;
--删除用户的表空间
--DROP TABLESPACE "USERS" INCLUDING CONTENTS AND DATAFILES;
impdp system/oracle@10.16.16.1:1521/hxdb directory=DATA_PUMP_DIR dumpfile=djcheck.dmp logfile=djcheck.log remap_schema=djcheck:djcheck
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
参考博客：http://blog.csdn.net/oscar999/article/details/7468153
--查询用户名，用户ID，创建时间
select * from all_users;
--查询版本信息
select * from v$version;
--查询（nls_database_parameters来源于props$，是表示数据库的字符集。在创建数据库的时候设定的,一般不会改变.）
select * from nls_database_parameters;
--查询数据库编码
select userenv('language') from dual;
select * from V$NLS_PARAMETERS WHERE PARAMETER='NLS_LANGUAGE';
两者不一致时会出现乱码，需要设置环境变量：NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK
--查询所有同义词
select * from user_synonyms;
--查询所有存储过程
select object_name,status from user_objects where object_type='PROCEDURE';
--查询所有函数
select object_name,status from user_objects where object_type='FUNCTION';
--查询所有视图
select view_name from user_views;
--查询所有表名：
select t.table_name from user_tables t;
--查看触发器(系统管理员)
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
--查看触发器(系统管理员)(特定用户)
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND OWNER='SCOTT'
--查看触发器(普通用户)
SELECT NAME FROM USER_SOURCE WHERE TYPE='TRIGGER' GROUP BY NAME
-- 查all_triggers表得到trigger_name
select trigger_name from all_triggers where table_name='XXX';
根据trigger_name查询出触发器详细信息
select text from all_source where type='TRIGGER' AND name='TR_XXX';
--查看名称包含log字符的表
select object_name,object_id from user_objects
where instr(object_name,’LOG’)>0;
select table_name from user_tables where table_name like '%LOG%';
查看某表的创建时间
select object_name,created from user_objects where object_name=upper(‘&table_name’);
查看某表的大小
select sum(bytes)/(1024*1024) as “size(M)” from user_segments
where segment_name=upper(‘&table_name’);
查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,’Y')>0;
--查询某个字段的相关信息(列名，表名，数据类型，长度，精度，比例)
select column_name,
table_name,data_type ,data_length,data_precision,data_scale from user_tab_columns where column_name='FEETYPE';
参考博客： https://www.cnblogs.com/yongtaiyu/archive/2013/09/13/3318539.html