最近我发现与其写一些没有营养的基础理论，不如去写一些盲区的东西，所以我打算改变一下写文章的风格，转变一下多些实操例子 来说明一些问题
本章简单涉及内容
创建用户，表，索引，视图，同义词
授权用户
删除表后索引 视图 同义词的情况
1、创建用户，表，索引，视图，同义词 约束
1.1 创建用户 授权
SYS@ prod>create user test identified by 123456;
用户已创建。
SYS@ prod>grant create session to test ;
授权成功。
SYS@ prod>conn test/123456
已连接。
TEST@ prod>select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ---------
TEST CREATE SESSION NO
TEST@ prod>select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ---------
TEST CREATE SESSION NO
TEST UNLIMITED TABLESPACE NO
TEST@ prod>select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ---------
TEST CREATE VIEW NO
TEST SELECT ANY TABLE NO
TEST CREATE TABLE NO
TEST CREATE SESSION NO
TEST CREATE SYNONYM NO
TEST UNLIMITED TABLESPACE NO
已选择6行。
TEST@ prod>1.2创建表 视图 同义词 约束 索引
创建表和主键约束（主键索引自动创建）
TEST@ prod>create table emp as select * from scott.emp;
表已创建。
TEST@ prod>
TEST@ prod>
TEST@ prod>
TEST@ prod>
TEST@ prod>
TEST@ prod>
TEST@ prod>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已选择14行。
TEST@ prod>alter table emp add constraint empno_pk primary key (empno);
表已更改。
TEST@ prod>select index_name,index_type,table_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------
EMPNO_PK NORMAL EMP创建视图
1）限制数据的存取：
用户只能看到基表的部分信息。方法：赋予用户访问视图对象的权限，而不是表的对象权限。
2）使得复杂的查询的书写变得容易：
隐藏或简化书写多表连接等复杂语句（将一网打尽部分做成视图）。
3）提供数据的独立性：
基表的多个独立子集的映射
如果建立了视图 想查看其中的定义，可以访问如下视图dba_views和user_views中的text字段(long型)；
同义词
public同义词 权限不足报错（同义词通常是数据库对象的别名公有同义词一般由DBA创建，使所有用户都可使用, 创建者需要create public synonym权限。）
因此我们只创建一个私有同义词就可以了
查看同义词的视图：dba_synonyms
删除私有同义词：drop synonym 同义词名
删除公有同义词：drop public synonym 同义词名
TEST@ prod>create view v1 as select * from emp where deptno=10;
视图已创建。
TEST@ prod>
TEST@ prod>
TEST@ prod>
TEST@ prod>
TEST@ prod>select * from v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
TEST@ prod>create public synonym syn1 for v1;
create public synonym syn1 for v1
*
第 1 行出现错误:
ORA-01031: 权限不足
TEST@ prod>create synonym syn1 for v1;
同义词已创建。
TEST@ prod>select view_name,text from user_views;
VIEW_NAME TEXT
------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------
V1 select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp whe
TEST@ prod>desc user_synonyms;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
TEST@ prod>selecct synonym_name,table_name from user_synonyms;
SP2-0734: 未知的命令开头 "selecct sy..." - 忽略了剩余的行。
TEST@ prod>select synonym_name,table_name from user_synonyms;
SYNONYM_NAME TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
SYN1 V1
TEST@ prod>1.3查看视图和同义词及索引情况
TEST@ prod>select * from v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
TEST@ prod>select * from syn1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
TEST@ prod>select index_name,table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
EMPNO_PK EMP
TEST@ prod>2、删除表及视图 同义词 索引的查看
结果可见视图和同义词都还在 但是查不到任何信息了
索引则随着表一起被drop
TEST@ prod>drop table emp;
表已删除。
TEST@ prod>select * from v1;
select * from v1
*
第 1 行出现错误:
ORA-04063: view "TEST.V1" 有错误
TEST@ prod>select * from syn1;
select * from syn1
*
第 1 行出现错误:
ORA-04063: view "TEST.V1" 有错误
TEST@ prod>select index_name,table_name from user_indexes;
未选定行
TEST@ prod>select synonym_name,table_name from user_synonyms;
SYNONYM_NAME TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
SYN1 V1
TEST@ prod>select view_name,text from user_views;
VIEW_NAME TEXT
------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------
V1 select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp whe
TEST@ prod>3、恢复表查看结果
表恢复后视图和同义词正常访问
索引名出现乱码，解决方法重命名索引即可
TEST@ prod>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$veflJfy4YCfgVYv2vTElrw==$0 TABLE 2021-03-20:01:17:58
TEST@ prod>
TEST@ prod>
TEST@ prod>flashback table 'emp' to before drop;
flashback table 'emp' to before drop
*
第 1 行出现错误:
ORA-00903: 表名无效
TEST@ prod>flashback table "emp" to before drop;
flashback table "emp" to before drop
*
第 1 行出现错误:
ORA-38305: 对象不在回收站中
TEST@ prod>flashback table "BIN$veflJfy4YCfgVYv2vTElrw==$0" to before drop;
闪回完成。
TEST@ prod>
TEST@ prod>
TEST@ prod>select * from v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
TEST@ prod>select * from syn1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
TEST@ prod>select index_name,table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
BIN$veflJfy3YCfgVYv2vTElrw==$0 EMP
TEST@ prod>
Jrojyun
2021-03-22
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
