sql基础(3)

//查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;

//查看当前用户的角色
SQL>select * from user_role_privs;

//查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

//查看表的相关信息，如大小，所属表空间
select * from user_segments t order by t.segment_type;

//查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments t where t.segment_name = upper(索引名);

//查看表的创建时间
select t.object_name , t.created from user_objects t;
select * from dba_objects;
select * from all_objects;

//查看是否为内存表，user_tables表很常用
select t.table_name, t.cache  from user_tables t where instr(t.cache,'N') >0;

//查看索引与索引的字段的关系
select * from user_ind_columns ;

//查看约束
select * from user_cons_columns;

//查看用户的序列信息
select * from user_sequences;

//查看当前用户所有的存储过程或函数
select * from user_objects t where t.object_type = upper('Function');

//查看表空间的剩余大小
select sum(bytes)/(1024*1024) as free_space,tablespace_name
    from dba_free_space
    group by tablespace_name;

//查看数据库的创建日期和归档方式
select t.LOG_MODE from V$database t;

//查看当前数据库有几个用户连接
select * from v$session;

//kill某个连接
alter system kill session 'sid,serial#';

//更改表名
rename t_a to t_b;

//把表放在或取出数据库的内存区
alter table t_a cache;
alter table t_a nocache;

//删除表和它所有的约束条件
dro table t_a cascade constraints;

//并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS, 交集INTERSECT
select * from emp union select * from empdemo;
select * from emp union all select * from empdemo;
select * from emp minus select * from empdemo;
select * from emp intersect select * from empdemo;

//索引：一个表的索引最好不大于3个，最好不要多字段索引
create index indexdemo on emp(deptno);
//修改索引
alter index indexdemo rebuild;
//创建索引后，必须执行分析，才能生效
analyze index indexdemo compute statistics;

//同义词
CREATE SYNONYM 同义词名 FOR 表名;
CREATE SYNONYM 同义词名 FOR 表名@数据库链接名;


//查询数据库的全局名(简便)
select * from global_name;

//修改数据文件名
alter dataspace dbname
rename file '/opt/oracle/oradata/db01.dbf' to '/opt/oracle/oradata/db02.dbf';
//增加数据文件
alter dataspace dbname
add datafile '/opt/oracle/oradta/dbnew.dbf' size 100M
autoextend on
maxsize 1000M;


select length('xx') from dual;
select dbtimezone from dual;