oracle新建个表空间,Oracle新建表空间及用户

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

扩展阅读：