REFORMEREMS

-- 1.创建临时表空间
-- 表空间名 BOEREms_TEMP
-- 数据文件名 D:\oracledata\BOEREms_TEMP.DBF
-- 表空间大小 50M
CREATE TEMPORARY TABLESPACE REFORMEREMS_TEMP TEMPFILE 'D:\oracledata\REFORMEREMS_TEMP.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M   -- 让已经存在的数据文件自动增长
EXTENT MANAGEMENT LOCAL;  

-- 2.创建数据表空间
CREATE TABLESPACE REFORMEREMS_DATA LOGGING DATAFILE 'D:\oracledata\REFORMEREMS_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 

-- 3.创建用户并指定表空间
-- 用户名:BOEREms  密码:BOEREms
CREATE USER BOEREms IDENTIFIED BY BOEREms
DEFAULT TABLESPACE REFORMEREMS_DATA  
TEMPORARY TABLESPACE REFORMEREMS_TEMP;  

-- 4.给用户授予权限
GRANT CONNECT,RESOURCE TO BOEREms;      -- 表示把CONNECT,RESOURCE权限授予用户
GRANT DBA TO BOEREms;                   -- 表示把DBA权限授予用户
GRANT SELECT_CATALOG_ROLE TO BOEREms;
GRANT SELECT ANY SEQUENCE TO BOEREms;
GRANT SELECT ANY TABLE TO BOEREms;
GRANT UNLIMITED TABLESPACE TO BOEREms;

--- 备份
exp BOEREms/BOEREms1703@XE file=F:ORACLE_DB\BOEREms171101.dmp owner=BOEREms

-- 还原
imp BOEREms/BOEREms1703@XE file=d:oracledata\BOEREms171101.dmp full=y

-- 删除用户以及用户所有的对象
DROP USER BOEREMS CASCADE;
-- 删除表空间与表空间文件
DROP TABLESPACE REFORMEREMS_TEMP INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE REFORMEREMS_DATA INCLUDING CONTENTS AND DATAFILES;

-- Oracle新建表空间及用户

/* 0:Oracle Logon:
Username:system
Password:system/master
Database:XE
Connect as SYSDBA*/

-- 1.创建临时表空间
-- 表空间名 QP_TEMP
-- 数据文件名 E:\ORACLE_DB\QP_TEMP.DBF
-- 表空间大小 50M
CREATE TEMPORARY TABLESPACE QP_TEMP TEMPFILE 'E:\ORACLE_DB\QP_TEMP.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M   -- 让已经存在的数据文件自动增长
EXTENT MANAGEMENT LOCAL;  

-- 2.创建数据表空间
CREATE TABLESPACE QP_DATA LOGGING DATAFILE 'E:\ORACLE_DB\QP_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 

-- 3.创建用户并指定表空间
-- 用户名:QP  密码:QP1607
CREATE USER QP IDENTIFIED BY QP1607
DEFAULT TABLESPACE QP_DATA  
TEMPORARY TABLESPACE QP_TEMP;  

-- 4.给用户授予权限
GRANT CONNECT,RESOURCE TO QP;      -- 表示把CONNECT,RESOURCE权限授予QP用户
GRANT DBA TO QP;                   -- 表示把DBA权限授予QP用户
GRANT SELECT_CATALOG_ROLE TO QP;
GRANT SELECT ANY SEQUENCE TO QP;
GRANT SELECT ANY TABLE TO QP;
GRANT UNLIMITED TABLESPACE TO QP;

-- 查询所有表空间的大小
SELECT TABLESPACE_NAME
      ,SUM(BYTES)/1024/1024 
  FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME;
 
 ---已经使用表空间的大小
SELECT TABLESPACE_NAME
      ,SUM(BYTES)/1024/1024 
      FROM DBA_FREE_SPACE
  GROUP BY TABLESPACE_NAME;

-- 备份
exp qaoe/qaoe1605@XE file=e:ORACLE_DB\qaoe160705.dmp owner=qaoe

-- 还原
imp QP/QP1607@XE file=e:ORACLE_DB\qaoe160705.dmp full=y

BoerCloud

-- 1.创建临时表空间
-- 表空间名 BoerCloud_TEMP
-- 数据文件名 E:\oracledata\BoerCloud_TEMP.DBF
-- 表空间大小 50M
CREATE TEMPORARY TABLESPACE BoerCloud_TEMP TEMPFILE 'E:\oracledata\BoerCloud_TEMP.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M   -- 让已经存在的数据文件自动增长
EXTENT MANAGEMENT LOCAL;  

-- 2.创建数据表空间
CREATE TABLESPACE BoerCloud_DATA LOGGING DATAFILE 'E:\oracledata\BoerCloud_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 

-- 3.创建用户并指定表空间
-- 用户名:Boer  密码:Boer1608
CREATE USER Boer IDENTIFIED BY Boer1608
DEFAULT TABLESPACE BoerCloud_DATA  
TEMPORARY TABLESPACE BoerCloud_TEMP;  

-- 4.给用户授予权限
GRANT CONNECT,RESOURCE TO Boer;      -- 表示把CONNECT,RESOURCE权限授予QP用户
GRANT DBA TO Boer;                   -- 表示把DBA权限授予QP用户
GRANT SELECT_CATALOG_ROLE TO Boer;
GRANT SELECT ANY SEQUENCE TO Boer;
GRANT SELECT ANY TABLE TO Boer;
GRANT UNLIMITED TABLESPACE TO Boer;

--- 备份
exp Boer/Boer1608@XE file=e:ORACLE_DB\boerCloud161107.dmp owner=Boer

-- 还原
imp Boer/Boer1608@XE file=e:oracledata\boerCloud161107.dmp full=y

dqzw

-- 1.创建临时表空间
CREATE TEMPORARY TABLESPACE dqzw_TEMP TEMPFILE 'E:\oracledata\dqzw_TEMP.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M   -- 让已经存在的数据文件自动增长
EXTENT MANAGEMENT LOCAL;  

-- 2.创建数据表空间
CREATE TABLESPACE dqzw_DATA LOGGING DATAFILE 'E:\oracledata\dqzw_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 



-- 3.创建用户并指定表空间
CREATE USER dqzw IDENTIFIED BY dqzw1607
DEFAULT TABLESPACE dqzw_DATA  
TEMPORARY TABLESPACE dqzw_TEMP;  

-- 4.给用户授予权限
GRANT CONNECT,RESOURCE TO dqzw;      -- 表示把CONNECT,RESOURCE权限授予QP用户
GRANT DBA TO dqzw;                   -- 表示把DBA权限授予QP用户
GRANT SELECT_CATALOG_ROLE TO dqzw;
GRANT SELECT ANY SEQUENCE TO dqzw;
GRANT SELECT ANY TABLE TO dqzw;
GRANT UNLIMITED TABLESPACE TO dqzw;

-- 还原
imp dqzw/dqzw1607@XE file=e:oracledata\dqzw20161107.dmp full=y

-- 删除用户
drop user dqzw cascade;

wx

-- 1.创建临时表空间
CREATE TEMPORARY TABLESPACE wx_TEMP TEMPFILE 'E:\oracledata\wx_TEMP.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M   -- 让已经存在的数据文件自动增长
EXTENT MANAGEMENT LOCAL;  

-- 2.创建数据表空间
CREATE TABLESPACE wx_DATA LOGGING DATAFILE 'E:\oracledata\wx_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 



-- 3.创建用户并指定表空间
CREATE USER wx IDENTIFIED BY wx1607
DEFAULT TABLESPACE wx_DATA  
TEMPORARY TABLESPACE wx_TEMP;  

-- 4.给用户授予权限
GRANT CONNECT,RESOURCE TO wx;      -- 表示把CONNECT,RESOURCE权限授予QP用户
GRANT DBA TO wx;                   -- 表示把DBA权限授予QP用户
GRANT SELECT_CATALOG_ROLE TO wx;
GRANT SELECT ANY SEQUENCE TO wx;
GRANT SELECT ANY TABLE TO wx;
GRANT UNLIMITED TABLESPACE TO wx;

-- 还原
imp wx/wx1607@XE file=e:oracledata\wx20161107.dmp full=y

Oracle服务启动后又停止问题

SELECT COUNT(*) FROM V$SESSION
SELECT * FROM V$PARAMETER
SHOW PARAMETER PROCESSES
SHOW PARAMETER SESSIONS
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
ALTER SYSTEM SET SESSIONS=335 SCOPE=SPFILE;

-- 查看procress&session的参数值和占用值

show parameter processes;
select count(*) from v$process;

show parameter sessions;
select count(*) from v$session;

alter system set processes=1000 scope=spfile;
alter system set sessions=1105 scope=spfile;

注意一下processes的值和sessions的值，Oracle官方文档中要求：
sessions=processes*1.5+5
纠正sessions=(1.1*processes+5)

查看表空间使用率

-- 查看表空间使用率
SELECT * FROM (
 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

UNION ALL

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
  )

ORDER BY TABLESPACE_NAME

查询Oracle 版本号

select * from v$version;

ORACLE多表查询优化示例1

-- ORACLE多表查询优化示例1
-- Oracle执行计划 讲解
-- TABLE ACCESS FULL  全表扫描
SELECT 
    T4.KNOWLEDGE_ID
    ,T4.KNOWLEDGE_NAME
    ,ROUND(SUM(T2.SCORE)/SUM(T1.FULL_SCORE) * 100,2) SCHOOL_RATE 
FROM T_PQA_QUESTIONS T1 
  INNER JOIN T_PQA_SCORE T2 ON T1.QUESTIONS_ID = T2.EXAM_QUESTIONS_ID
  INNER JOIN T_PQA_CLASS T3 ON T2.CLASS_ID = T3.CLASS_ID
  INNER JOIN T_PQA_KNOWLEDGE T4 ON T1.KNOWLEDGE_ID = T4.KNOWLEDGE_ID
WHERE T3.ORG_ID = 'DF4A55786F734E5F9B48B91C36BA7A1A'
  AND T1.EXAM_COURSE_ID = '5878367D5D044A46945A647875F94B1F'
 GROUP BY  T4.KNOWLEDGE_ID
          ,T4.KNOWLEDGE_NAME       -- 这里执行要1.123 seconds

/*SELECT COUNT(1) FROM T_PQA_QUESTIONS T1 -- 194条记录 
SELECT COUNT(1) FROM T_PQA_SCORE T2 -- 1779426条记录 
SELECT COUNT(1) FROM T_PQA_CLASS T3 -- 574条记录 
SELECT COUNT(1) FROM T_PQA_KNOWLEDGE T4 -- 2934条记录 */

SELECT 
    T4.KNOWLEDGE_ID
    ,T4.KNOWLEDGE_NAME
    ,ROUND(SUM(M.SCORE)/SUM(T1.FULL_SCORE) * 100,2) SCHOOL_RATE 
FROM T_PQA_QUESTIONS T1 
  
  LEFT JOIN (SELECT M1.EXAM_QUESTIONS_ID
                   ,M1.SCORE
                FROM T_PQA_SCORE M1 
                INNER JOIN T_PQA_CLASS M2 
                ON M1.CLASS_ID = M2.CLASS_ID AND M2.ORG_ID = 'DF4A55786F734E5F9B48B91C36BA7A1A' ) M 
        ON T1.QUESTIONS_ID = M.EXAM_QUESTIONS_ID
                
  INNER JOIN T_PQA_KNOWLEDGE T4 ON T1.KNOWLEDGE_ID = T4.KNOWLEDGE_ID
WHERE T1.EXAM_COURSE_ID = '5878367D5D044A46945A647875F94B1F'
 GROUP BY  T4.KNOWLEDGE_ID
          ,T4.KNOWLEDGE_NAME       -- 这里执行要0.156 seconds

扩展阅读：

• ORA-12516 "TNS监听程序找不到符合协议堆栈要求的可用处理程序" 解决方案
• ORA-12516 TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
• Oracle 内存分配和调优总结
• ORA-01652 无法通过128 (在表空间 TEMP中)扩展temp段 剖析解决
• ORA-28001: the password has expired解决办法
• Oracle数据库忘记用户名和密码怎么办
• Oracle数据库找回密码
• 本地oracle数据库忘记用户名密码解决方案