oracle创建用户及权限分配

发表于 更新于 阅读次数：

创建用户、分配权限

不创建表空间，使用默认表空间USERS(TEMP临时表空间)

1
2
3
4
5
CREATE USER test1 IDENTIFIED BY test1 ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE, CREATE VIEW TO test1;

--删除用户
DROP USER test1 CASCADE;

查看用户表空间信息(dba权限的用户)

1
2
3
4
-- 查看表空间信息
SELECT * FROM DBA_DATA_FILES;
-- 查看用户信息
SELECT * FROM DBA_USERS;

查询dbf文件路径

system表空间路径

1
SELECT FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=1

创建临时表空间

1
2
3
4
CREATE TEMPORARY TABLESPACE p2m_624_20171201_temp TEMPFILE
    '/home/oracle/app/oracle/oradata/helowin/p2m_624_20171201_temp2.dbf' SIZE 32 M
        AUTOEXTEND ON NEXT 32 M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

创建表空间

1
2
CREATE TABLESPACE p2m_624_20171201 LOGGING DATAFILE '/home/oracle/app/oracle/oradata/helowin/p2m_624_20171201.dbf' SIZE 32M 
AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

创建用户及分配存储空间

1
2
3
CREATE USER p2m_624_20171201 IDENTIFIED BY p2m_624_20171201 ACCOUNT UNLOCK
 DEFAULT TABLESPACE p2m_624_20171201
    TEMPORARY TABLESPACE p2m_624_20171201_temp;

给该用户授权

1
GRANT CONNECT, RESOURCE, DBA TO p2m_624_20171201;

删除用户及表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP USER p2m_624_20171201 CASCADE;

DROP TABLESPACE p2m_624_20171201 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE p2m_624_20171201_temp;



-- 查询用户拥有的角色
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE=${userName};
-- 回收权限权限
REVOKE CONNECT,RESOURCE FROM ${userName};
-- 删除用户及数据
DROP USER ${userName} CASCADE;
-- 删除表空间及dbf文件
DROP TABLESPACE ${tableSpace} INCLUDING CONTENTS AND DATAFILES;

-- 处于会话中的用户，强制下线后才能删除(DBA用户才能执行)
SELECT SID||','||SERIAL# FROM V$SESSION WHERE USERNAME = ${userName};
ALTER SYSTEM DISCONNECT SESSION '${SID,SERIAL#}' IMMEDIATE;

SQL读取表信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查询用户表
SELECT * FROM USER_TABLES WHERE TABLESPACE_NAME='P2M_P624_20180108';
-- 查询表结构定义
SELECT DBMS_METADATA.GET_DDL('TABLE','PM_DEMANDPLAN') FROM DUAL;

--查询表索引
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = UPPER('COMMNCT_DISCUSSION');
--查询索引定义
SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000074598C00003$$') FROM DUAL;

--查询视图
SELECT * FROM USER_VIEWS; 

--查询序列
SELECT * FROM USER_SEQUENCES;
--查询序列定义
SELECT DBMS_METADATA.GET_DDL('SEQUENCE','SYSWARE_SEQUENCE') FROM DUAL;

--查询函数
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION';
--查询函数定义
SELECT DBMS_METADATA.GET_DDL('SEQUENCE','FUNCTION_NAME') FROM DUAL;

sqlplus执行sql脚本

$sqlplus user/pass@servicename<file_name.sql

exp/imp备份数据库

可以在sqlplus客户端操作(优先使用这种方式备份)

  1. exp导出用户下所有数据到dmp文件

    1
    exp P2M_SF_20200310/P2M_SF_20200310@192.168.5.200:1521/sysware1 OWNER=P2M_SF_20200310 file=P2M_SF_20200310.dmp

  2. imp导入到新用户

    1
    imp P2M_SF_20200310/P2M_SF_20200310 file=P2M_SF_20200310.dmp full=y ignore=y

expdp/impdp备份数据库

能访问数据库所在的服务器时，可以使用这种方式备份

  1. expdp导出数据库
    1
    2
    3
    SQL>create directory dmpdir as '/data/app/dmpdir';
    SQL>grant read,write on directory dmpdir to P2M_SF_DEV_20200305;
    expdp P2M_SF_DEV_20200305/P2M_SF_DEV_20200305 DUMPFILE=P2M_SF_DEV_20200305_02.dmp DIRECTORY=dmpdir

2.impdp导入数据

1
impdp BK_SF_DEV_20200305/BK_SF_DEV_20200305 directory=dmpdir dumpfile=P2M_SF_DEV_20200305_02.dmp remap_schema=P2M_SF_DEV_20200305:BK_SF_DEV_20200305