Oracle数据库面试题 用户的权限都有哪些

系统权限: 允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等（对用户而言）

对象权限: 允许用户 *** 纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等（是针对表或视图而言的）

1系统权限

超过一百多种有效的权限（SELECT FROM SYSTEM_PRIVILEGE_MAP查）

数据库管理员具有高级权限以完成管理任务，例如:

–创建新用户

–删除用户

–删除表

–备份表

系统权限分类：

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。

对于DBA管理用户：授予connect，resource, dba权限。

a常用的系统权限：

CREATE SESSION 创建会话

CREATE SEQUENCE 创建序列

CREATE SYNONYM 创建同名对象

CREATE TABLE 在用户模式中创建表

CREATE ANY TABLE 在任何模式中创建表

DROP TABLE 在用户模式中删除表

DROP ANY TABLE 在任何模式中删除表

CREATE PROCEDURE 创建存储过程

EXECUTE ANY PROCEDURE 执行任何模式的存储过程

CREATE USER 创建用户

DROP USER 删除用户

CREATE VIEW 创建视图

2对象权限

不同的对象具有不同的对象权限

对象的拥有者拥有所有权限

对象的拥有者可以向外分配权限

ORACLE一共有种对象权限

对象权限 表 视图 序列 过程

修改(alter) √ √

删除(delete) √ √

执行(execute) √

索引(index) √

插入(insert) √ √

关联(references) √ √

选择(select) √ √ √

更新(update) √ √

试下这样写：

select aid,

(select cost from cost b where bid=aid and bday=1) as 周1,

(select cost from cost b where bid=aid and bday=2) as 周2,

(select cost from cost b where bid=aid and bday=3) as 周3,

(select cost from cost b where bid=aid and bday=4) as 周4,

(select cost from cost b where bid=aid and bday=5) as 周5,

(select cost from cost b where bid=aid and bday=6) as 周6,

(select cost from cost b where bid=aid and bday=7) as 周日

from (select distanct id from cost ) a

查询下看正确不，正确再加上建表语句把数据存入新表中

1: delet from cw_dw_pz where A003='000000';

2: select from gz_gr_zz where A044<0;

3: delete from cw_dw_pz where p002<to_date('2015-01-01','yyyy-mm-dd');

4

--建立表 和删除表;

DROP TABLE watest；

CREATE TABLE watest

(

A001 VARCHAR2(10),

A002 VARCHAR2(10),

A003 VARCHAR2(10),

A008 VARCHAR2(10)

);

---插入watest语句块;

INSERT INTO DEPT VALUES

('test','test','test','test');

--增加A004字段

alter table watest add A004 VARCHAR2(10); #给表watest增加一名为A004的列。

Q : This symbol When you put infront of a line in the parameter file signifies a ment $ @ # ! Q : When you change a parameter value in the parameter file when will that change takes affect Immediately after saving the parameter file At the first CHECKPOINT after saving the paramter file When the DBWR finishes writing all the dirty buffers to the disk At the next instance startup Q : ALTER SYSTEM DEFFERED mand modifies the global parameters for existing sessions after a certain amount of time new sessions only existing and new sessions depends on the SPIN_COUNT initialization parameter Q : The location where debugging trace files for back ground processes are written is specified by LOGFILE_DEST ORACLE_HOME BACKGROUND_DUMP_DEST CORE_DUMP_DEST Q : In case of heavy contention for latches set the LOG_SIMULTANEOUS_COPIES initialization parameter to Twice the number of CPUs Same as the DB Block Buffers Same as the Shared Pool Size None of the above Q : What is the first step in manually creating a new database Startup an instance Start SQLPlus and connect to Oracle as SYSDBA Check the instance identifier for your system Create a parameter file Q : Which of the following is true regarding control files Oracle remeds atleast o control files stored on o separate disks Oracle remeds atleast o control files stored on one disk Oracle remeds to store one control file One control file is not enough to run a database Q : Tom created a database with a DB_BLOCK_SIZE of k he wants to increase this to k what is his next step Issue ALTER SYSTEM SET DB_BLOCK_SIZE= k mand recreate the database with the new setting It can be done in both the ways the DB_BLOCK_SIZE cannot be k Q : Howmany rollback segments are required for Oracle to startup apart from SYSTEM rollback segment Oracle can start with just the system rollback segment Oracle Needs atleast rollback segments before it can start Oracle Needs a Temp Rollback Segment before it can start None of the above Q : The unit of measurement for DB_BLOCK_SIZE intialization parameter is BLOCKS BYTE PAGE ROW Q : This tablespace is a must before you run the database instance ROLLBACK TOOLS TEMP SYSTEM Q : Which initialization parameter determines the rollback segments that can be used by Oracle ROLLBACKS LOGFILE GROUP ROLLBACK_SEGMENTS DBA_ROLLBACK_SEGS Q : Which of the following is a valid but undocumented parameter in Oracle _CORRUPT_RBS _CORRUPT_REDO _CORRUPT_ROLLBACK_SEGMENTS None of the above lishixinzhi/Article/program/Oracle/201311/17773

a 找出最贵的商品(item)的名称和价格

SELECT

name, price

FROM

item

WHERE

price = ( SELECT MAX(itprice) FROM item it);

b 找出每个月每个商品的销售总金额；

SELECT

TO_CHAR(transactiondate, 'MM') AS 月,

itemname AS 商品名,

SUM( itemSalequantity itemprice ) AS 销售额

FROM

transaction, itemSale, item

WHERE

transactiontransid = itemSaletransid

AND itemSaleitemid = itemitemid

GROUP BY

TO_CHAR(transactiondate, 'MM') ,

itemname

c 找出从来也没有销售过的商品；

SELECT

FROM

item

WHERE

itemid NOT IN ( SELECT DISTINCT itemid FROM itemSale)

-- 上面这个估计执行效率不高

d找出从来也没有买过“酒”的所有客户。

SELECT

FROM

customer

WHERE

customercustid NOT IN

( SELECT transactioncustid

FROM

transaction, itemSale, item

WHERE

transactiontransid = itemSaletransid

AND itemSaleitemid = itemitemid

AND itemname LIKE '%酒%'

)

-- 上面这个估计执行效率也是不高

select decode(性别字段,'A','男','B','女','未知') as 性别  from 表名     （通过decode函数得到想要的值）

select 购物人 from 购物信息 having count(购物人||商品名称)>1    （count求出购物人购买商品大于1的）

select 姓名，课程，分数 from 成绩表 where 姓名 not in(select 姓名 from 成绩表 where 分数<=60)   （姓名不包含小于60的）

select  t from （select name,avg(score)  score from  student)  s,student t

    where sname=tname and sname like '张%'  and  sscore>60   （姓张的大于60的，使用模糊查询，like 以张开头的字段）

select rownum,表 from 表 where id=要查的id号     （rownum 记录行数）

table3没有图看不到

以上就是关于Oracle数据库面试题 用户的权限都有哪些全部的内容，包括:Oracle数据库面试题 用户的权限都有哪些、数据库问题 求高手解答 oracle、Oracle数据库测试卷，求高手解答，最好详解，跪求，谢谢，你先答，我过后追加100财富值等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！