Oracle日常使用心得及sql语句

--创建表空间CREATE TABLESPACE MDM   DATAFILE 'C:/oracle1/product/10.2.0/oradata/dataissued/MDM.dbf'   SIZE 2048M AUTOEXTEND ON NEXT 5M MAXSIZE 4096M; --删除表空间DROP TABLESPACE MDM INCLUDING CONTENTS AND DATAFILES;--索引表空间  CREATE TABLESPACE MDM DATAFILE 'F:\tablespace\MDM' SIZE 2048M AUTOEXTEND ON NEXT 5M MAXSIZE 4096M;         --创建用户并指定表空间CREATE USER MDM IDENTIFIED BY dataissued DEFAULT TABLESPACE MDM --用户名 MDM 密码 dataissued--修改用户密码ALTER USER MDM IDENTIFIED BY mdm--分配权限给新创建的用户GRANT CONNECT,RESOURCE,DBA TO MDM--对表进行分配extentALTER TABLE TABELNAME ALLOCATE EXTENT--查询没有分配extent的表SELECT * FROM USER_TABLES WHERE TABLE_NAME NOT IN (SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE')--ip导出方式： exp demo/demo@127.0.0.1:1521/orcl file=f:/f.dmp full=y  exp demo/demo@orcl file=f:/f.dmp full=y  imp demo/demo@orcl file=f:/f.dmp full=y ignore=y  imp MDM/dataissued@dataissued file=c:/mdm.dmp fromuser=mdm touser=MDM--查看表空间情况SELECT * FROM DBA_FREE_SPACE;SELECT * FROM DBA_DATA_FILES--Oracle用户解锁ALTER USER mdm ACCOUNT UNLOCK;--DBA命令--管理员链接数据库conn /as sysdba--停止数据库shutdown immediate--启动数据库startup--显示用户show user--切换用户conn system/password--执行oracle存储过程DECLARE O_RETURN VARCHAR2(200);BEGIN  p_mdm_updt_cstr(O_RETURN);END p_mdm_updt_cstr;--查看数据库执行了哪些sqlselect last_active_time, SQL_FULLTEXT, SQL_TEXT from v$sql k where k.module='JDBC Thin Client'  and k.parsing_schema_name='ROEEE' order by k.last_active_time desc --对比两张表中所有字段的异同SELECT ID, ALL_NGHT_IDNY, COUNT(*) FROM (  SELECT ID, ALL_NGHT_IDNY FROM ZMYX_NEW_CSTR_HZ M  UNION ALL  SELECT ID, ALL_NGHT_IDNY FROM CSTR Y)   GROUP BY ID, ALL_NGHT_IDNY HAVING COUNT(*) =1 ORDER BY ID