Oracle Connect By用法
oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：
select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;
<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：查询结果重起始根结点的限定条件。
<Conditional-3>：连接条件
数据组织结构如下图：
数据库表结构如下：
create table t2(
root_id number,
id number,
name varchar(5),
description varchar(10)
);
insert into t2(root_id,id,name,description) values(0,1,'a','aaa');
insert into t2(root_id,id,name,description) values(1,2,'a1','aaa1');
insert into t2(root_id,id,name,description) values(1,3,'a2','aaa2');
insert into t2(root_id,id,name,description) values(0,4,'b','bbb');
insert into t2(root_id,id,name,description) values(4,5,'b1','bbb1');
insert into t2(root_id,id,name,description) values(4,6,'b2','bbb2');
获取完整树： ......
1. Oracle安装完成后的用户名和密码
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
internal/oracle
2.修改用户的密码
SQL> conn sys/change_on_install as sysdba
Connected.
SQL> alter user sys identified by ******;
User altered.
3.为用户解锁
SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL>conn sys/change_on_install as sysdba
Connected.
SQL> alter user scott account unlock;
User altered.
锁定用户：
SQL> alter user scott account lock;
4.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
5.查看用户或角色系统权限：
select * from dba_sys_privs;
select * from user_sys_privs;
6.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
7..查看所有角色：
select * from dba_roles;
......
最新文章 :