REFORMEREMS

-- 1.創(chuàng)建臨時表空間
-- 表空間名 BOEREms_TEMP
-- 數(shù)據(jù)文件名 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   -- 讓已經存在的數(shù)據(jù)文件自動增長
EXTENT MANAGEMENT LOCAL;  

-- 2.創(chuàng)建數(shù)據(jù)表空間
CREATE TABLESPACE REFORMEREMS_DATA LOGGING DATAFILE 'D:\oracledata\REFORMEREMS_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 

-- 3.創(chuàng)建用戶并指定表空間
-- 用戶名: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.創(chuàng)建臨時表空間
-- 表空間名 QP_TEMP
-- 數(shù)據(jù)文件名 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   -- 讓已經存在的數(shù)據(jù)文件自動增長
EXTENT MANAGEMENT LOCAL;  

-- 2.創(chuàng)建數(shù)據(jù)表空間
CREATE TABLESPACE QP_DATA LOGGING DATAFILE 'E:\ORACLE_DB\QP_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 

-- 3.創(chuàng)建用戶并指定表空間
-- 用戶名: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.創(chuàng)建臨時表空間
-- 表空間名 BoerCloud_TEMP
-- 數(shù)據(jù)文件名 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   -- 讓已經存在的數(shù)據(jù)文件自動增長
EXTENT MANAGEMENT LOCAL;  

-- 2.創(chuàng)建數(shù)據(jù)表空間
CREATE TABLESPACE BoerCloud_DATA LOGGING DATAFILE 'E:\oracledata\BoerCloud_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 

-- 3.創(chuàng)建用戶并指定表空間
-- 用戶名: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.創(chuàng)建臨時表空間
CREATE TEMPORARY TABLESPACE dqzw_TEMP TEMPFILE 'E:\oracledata\dqzw_TEMP.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M   -- 讓已經存在的數(shù)據(jù)文件自動增長
EXTENT MANAGEMENT LOCAL;  

-- 2.創(chuàng)建數(shù)據(jù)表空間
CREATE TABLESPACE dqzw_DATA LOGGING DATAFILE 'E:\oracledata\dqzw_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 



-- 3.創(chuàng)建用戶并指定表空間
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.創(chuàng)建臨時表空間
CREATE TEMPORARY TABLESPACE wx_TEMP TEMPFILE 'E:\oracledata\wx_TEMP.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 2048M   -- 讓已經存在的數(shù)據(jù)文件自動增長
EXTENT MANAGEMENT LOCAL;  

-- 2.創(chuàng)建數(shù)據(jù)表空間
CREATE TABLESPACE wx_DATA LOGGING DATAFILE 'E:\oracledata\wx_DATA.DBF' 
SIZE 50M  
AUTOEXTEND ON NEXT 50M MAXSIZE 3072M  
EXTENT MANAGEMENT LOCAL; 



-- 3.創(chuàng)建用戶并指定表空間
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的參數(shù)值和占用值

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多表查詢優(yōu)化示例1

-- ORACLE多表查詢優(yōu)化示例1
-- Oracle執(zhí)行計劃 講解
-- 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       -- 這里執(zhí)行要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       -- 這里執(zhí)行要0.156 seconds

擴展閱讀：

• ORA-12516 "TNS監(jiān)聽程序找不到符合協(xié)議堆棧要求的可用處理程序" 解決方案
• ORA-12516 TNS: 監(jiān)聽程序找不到符合協(xié)議堆棧要求的可用處理程序
• Oracle 內存分配和調優(yōu)總結
• ORA-01652 無法通過128 (在表空間 TEMP中)擴展temp段 剖析解決
• ORA-28001: the password has expired解決辦法
• Oracle數(shù)據(jù)庫忘記用戶名和密碼怎么辦
• Oracle數(shù)據(jù)庫找回密碼
• 本地oracle數(shù)據(jù)庫忘記用戶名密碼解決方案