当前位置: 代码迷 >> SQL >> sql基础(三)
  详细解决方案

sql基础(三)

热度:56   发布时间:2016-05-05 13:45:42.0
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, 交集INTERSECTselect * 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 dbnamerename file '/opt/oracle/oradata/db01.dbf' to '/opt/oracle/oradata/db02.dbf';//增加数据文件alter dataspace dbnameadd datafile '/opt/oracle/oradta/dbnew.dbf' size 100Mautoextend onmaxsize 1000M;select length('xx') from dual;select dbtimezone from dual;

?

  相关解决方案