|
1Z0-053第18章,1Z0-063第1章
1Z0-052共19章(上完19章),1Z0-053共21章(上完21章)和1Z0-063多租户部分共9章(上完1章)
总共上完全部49章中的41章
索引不可用,11g会自动删除索引的存储空间(段):
- [oracle@station26 ~]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 22 09:26:01 2017
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> create table t05318_a ( a number ) ;
- Table created.
- SQL> create index i05318_a on t05318_a ( a ) ;
- Index created.
- SQL> insert into t05318_a values ( 1 ) ;
- 1 row created.
- SQL> rollback;
- Rollback complete.
- SQL> insert into t05318_a values ( 1 ) ;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> alter table t05318_a move tablespace users;
- Table altered.
- SQL>
复制代码- select * from dba_segments s
- where s.OWNER='HR' and s.SEGMENT_NAME='I05318_A';
-
- select i.STATUS
- from dba_indexes i where i.TABLE_OWNER='HR'
- and i.TABLE_NAME='T05318_A';
复制代码 对于11.2.0.2含以后版本:1Z0-053的18-14页不对,分区表也是延迟段建立:
关于索引组织表,书上是对的,不能段延迟:
- SQL> create table iot1 ( a number constraint pk_iot1 primary key ,
- 2 b varchar2(20))
- 3 organization index;
- Table created.
复制代码- select * from dba_tables t
- where t.OWNER='HR' and t.TABLE_NAME='IOT1';
- select * from dba_indexes i
- where i.table_OWNER='HR' and i.TABLE_NAME='IOT1';
-
- select * from dba_segments s
- where s.OWNER='HR' and s.SEGMENT_NAME='PK_IOT1';
复制代码 补充:索引的压缩是OLTP压缩出现之前很多年就有的(OLTP表压缩的前身):
- SQL> create table t05318_oltp ( a char(2000)) ;
- Table created.
- SQL> create index i05318_oltp on t05318_oltp (a) ;
- Index created.
- SQL> begin
- 2 for i in 1..10000
- 3 loop
- 4 insert into t05318_oltp values ('A');
- 5 end loop;
- 6 commit;
- 7 end;
- 8 /
- PL/SQL procedure successfully completed.
- SQL> SQL>
- SQL>
- SQL> drop index t05318_oltp;
- drop index t05318_oltp
- *
- ERROR at line 1:
- ORA-01418: specified index does not exist
- SQL> drop index i05318_oltp;
- Index dropped.
- SQL> create index i05318_oltp on t05318_oltp (a) compress;
- Index created.
- SQL>
复制代码- select bytes/1024/1024 from dba_segments s
- where s.OWNER='HR' and
- s.SEGMENT_NAME='I05318_OLTP';
- select i.COMPRESSION from dba_indexes i
- where i.TABLE_OWNER='HR'
- and i.TABLE_NAME='T05318_OLTP';
复制代码 压缩:
Oracle11gR2的表压缩特性通过压缩表中的数据以减少对空间的占用。Oracle11gR2支持三种不同的压缩方式:
· 支持直接路径加载的Basic压缩方式(10x)
· 支持针对所有DML操作的OLTP压缩方式(2-4x)
· Exadata专属的Hybrid columnar compression压缩方式
Oracle公司建议我们采用以上三种不同的压缩方式,针对特定的应用场合来进行表压缩。如果该表包含大量冗长的和重复的值,通过被压缩可以减少该表对磁盘空间的占用和对SGA中数据库缓冲区缓存的占用。
先从图形界面入手,我们发现在新建表空间时Oracle11gR2的界面出现压缩选项:
在 下面各个部分所有的实验中我们都创建两个表:一个表在TBS_NOCOMPRESSION表空间,另一个表在有对应默认压缩选项的表空间。最后我们把在 TBS_NOCOMPRESSION表空间上的那个表转换成对应压缩选项表。通过对比实验来弄清Oracle11gR2这方面的新特性。
--sys--
create tablespace tbs_nocompression datafile size 10M autoextend on;
create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
create tablespace tbs_query datafile size 10M autoextend on default compress for query;
create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
select t.tablespace_name, t.def_tab_compression, t.compress_for from dba_tablespaces t
where t.tablespace_name in ('TBS_NOCOMPRESSION',
'TBS_BASIC', 'TBS_OLTP',
'TBS_QUERY',
'TBS_ARCHIVE');
--hr--
create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
create table t_basic (a varchar2(200)) tablespace tbs_basic;
create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
--hr-error--
create table t_query (a varchar2(200)) tablespace tbs_query;
create table t_archive (a varchar2(200)) tablespace tbs_archive;
--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
from hr.T_OLTP group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);
--hr--
create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
--hr-error--
create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;
--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
--hr--
begin
for i in 1..400
loop
insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic2 group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp2 group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
from hr.T_BASIC2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid)
from hr.T_OLTP2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid);
--hr--
alter table t_nocompression compress for oltp;
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
alter table t_nocompression move tablespace tbs_nocompression;
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
insert /*+ append */ into t_basic select * from t_basic;
commit;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--hr--
alter table t_basic move tablespace TBS_NOCOMPRESSION;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys-evolution--
drop table hr.t_basic_big;
create table hr.t_basic_big compress as select * from dba_source;
由于basic压缩是个迭代过程
所以有5000多行没有压缩
--sys-advisor11g的语法--
declare
v_blkcnt_cmp number;
v_blkcnt_uncmp number;
v_row_cmp number;
v_row_uncmp number;
v_cmp_ratio number;
v_comptype_str varchar2(200);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
ownname =>'HR',
tabname =>'T_BASIC_BIG',
partname =>null,
comptype => 2,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp =>v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str =>v_comptype_str);
DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
end;
--输出--
Blk count compressed = 1785
Blk count uncompressed = 2340
Row count per block compressed = 68
Row count per block uncompressed = 51
ratio: 1.31092436974789915966386554621848739496
Compression type = "Compress For OLTP"
压缩建议者12c的语法:
- v_blkcnt_cmp number;
- v_blkcnt_uncmp number;
- v_row_cmp num
- declare
- ber;
- v_row_uncmp number;
- v_cmp_ratio number;
- v_comptype_str varchar2(200);
- BEGIN
- DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
- ownname =>'HR',
- OBJNAME =>'T_BASIC_BIG',
- SUBOBJNAME =>null,
- comptype => 16,
- blkcnt_cmp => v_blkcnt_cmp,
- blkcnt_uncmp => v_blkcnt_uncmp,
- row_cmp =>v_row_cmp,
- row_uncmp => v_row_uncmp,
- cmp_ratio => v_cmp_ratio,
- comptype_str =>v_comptype_str);
- DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
- DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
- DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
- DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
- DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
- DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
- end;
--OLTP压缩一下表--
验证表大小
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
from hr.T_BASIC_BIG group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);
--hr--
create table t_basic_col( a number , b varchar2(20)) compress ;
insert into t_basic_col values ( 1,'A') ;
commit;
create table t_oltp_col( a number , b varchar2(20)) compress for oltp;
insert into t_oltp_col values ( 1,'A') ;
commit;
select * from t_basic_col;
select * from t_oltp_col;
alter table t_basic_col drop column b;
alter table t_basic_col drop ( b);
alter table t_oltp_col drop column b;
select * from t_oltp_col;
在非exadata机器上,是建不了默认HCC的表空间的:
- SQL> create tablespace tbs_query datafile size 10M autoextend on default compress for query;
- create tablespace tbs_query datafile size 10M autoextend on default compress for query
- *
- ERROR at line 1:
- ORA-64307: Exadata Hybrid Columnar Compression is not supported for
- tablespaces on this storage type
- SQL> create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
- create tablespace tbs_archive datafile size 10M autoextend on default compress for archive
- *
- ERROR at line 1:
- ORA-64307: Exadata Hybrid Columnar Compression is not supported for
- tablespaces on this storage type
- SQL>
复制代码
在shrink space时,偏门知识:
shrink IOT时,要重建映射表和第二索引:
段建议者:
空间悬挂:
- create tablespace tbs05318 datafile size 5M autoextend off;
- grant resumable to hr;
复制代码- SQL> create table iot1 ( a number constraint pk_iot1 primary key ,
- 2 b varchar2(20))
- 3 organization index;
- Table created.
- SQL> show user
- USER is "HR"
- SQL> create table t05318 ( a number ) tablespace tbs05318 storage ( initial 6M ) ;
- Table created.
- SQL> insert into t05318 values (1) ;
- insert into t05318 values (1)
- *
- ERROR at line 1:
- ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318
- SQL> alter session set resumable_timeout=10;
- ERROR:
- ORA-02097: parameter cannot be modified because specified value is invalid
- ORA-01031: insufficient privileges
- SQL> alter session enable resumable timeout 10;
- ERROR:
- ORA-01031: insufficient privileges
- SQL> alter session set resumable_timeout=10;
- Session altered.
- SQL> insert into t05318 values (1) ;
- insert into t05318 values (1)
- *
- ERROR at line 1:
- ORA-30032: the suspended (resumable) statement has timed out
- ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318
- SQL>
复制代码
trigger:
- create tablespace tbs05318 datafile size 5M autoextend off;
- grant resumable to hr;
- CREATE OR REPLACE TRIGGER SYS.TRG_SUSPEND
- AFTER SUSPEND
- ON DATABASE
- declare
- v_size number;
- pragma AUTONOMOUS_TRANSACTION;
- begin
- select bytes into v_size from dba_data_files
- where file_name='/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_tbs05318_f3s0qml2_.dbf';
- v_size := v_size + 5242880 ;
- execute immediate 'alter database datafile ''/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_tbs05318_f3s0qml2_.dbf'' resize '||v_size;
- commit;
- end;
复制代码
在多组户的插件数据库查v$视图:
- [oracle@station26 admin]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 22 16:09:43 2017
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn sys/oracle_4U@pdb1_1 as sysdba
- Connected.
- SQL> select d.CON_ID,d.NAME,d.OPEN_MODE,d.CDB
- from v$database d; 2
- CON_ID NAME OPEN_MODE CDB
- ---------- --------- -------------------- ---
- 0 CDB1 READ WRITE YES
- SQL> select i.INSTANCE_MODE, i.INSTANCE_NAME,i.CON_ID
- from v$instance i; 2
- INSTANCE_MO INSTANCE_NAME CON_ID
- ----------- ---------------- ----------
- REGULAR cdb1 0
- SQL> select con_id, name from v$datafile;
- CON_ID
- ----------
- NAME
- --------------------------------------------------------------------------------
- 0
- +DATA/CDB1/DATAFILE/undotbs1.260.934887851
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.934888861
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.934888861
- CON_ID
- ----------
- NAME
- --------------------------------------------------------------------------------
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.934888861
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.934888861
- SQL> exit
- Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
- and Real Application Testing options
- [oracle@station26 admin]$ vim tnsnames.ora
- [oracle@station26 admin]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 22 16:11:11 2017
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn sys/oracle_4U@pdb1_1 as sysdba
- Connected.
- SQL> show con_id
- CON_ID
- ------------------------------
- 3
- SQL> show con_name
- CON_NAME
- ------------------------------
- PDB1_1
- SQL> select d.CON_ID,d.NAME,d.OPEN_MODE,d.CDB
- from v$database d; 2
- CON_ID NAME OPEN_MODE CDB
- ---------- --------- -------------------- ---
- 0 CDB1 READ WRITE YES
- SQL> select i.INSTANCE_MODE, i.INSTANCE_NAME,i.CON_ID
- from v$instance i; 2
- INSTANCE_MO INSTANCE_NAME CON_ID
- ----------- ---------------- ----------
- REGULAR cdb1 0
- SQL> select con_id, name from v$datafile;
- CON_ID
- ----------
- NAME
- --------------------------------------------------------------------------------
- 0
- +DATA/CDB1/DATAFILE/undotbs1.260.934887851
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.934888861
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.934888861
- CON_ID
- ----------
- NAME
- --------------------------------------------------------------------------------
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.934888861
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.934888861
- SQL>
- SQL> select s.CON_ID,s.NAME,s.PDB
- from v$services s; 2
- CON_ID NAME
- ---------- ----------------------------------------------------------------
- PDB
- ------------------------------
- 3 pdb1_1
- PDB1_1
- SQL> select p.CON_ID,p.NAME,p.OPEN_MODE
- from v$pdbs p; 2
- CON_ID NAME OPEN_MODE
- ---------- ------------------------------ ----------
- 3 PDB1_1 READ WRITE
- SQL> show con_name
- CON_NAME
- ------------------------------
- PDB1_1
- SQL> show con_id
- CON_ID
- ------------------------------
- 3
- SQL>
- SQL> select c.CON_ID,c.DBID,c.PDB_ID, c.GUID , c.PDB_NAME,c.STATUS
- from cdb_pdbs c; 2
- CON_ID DBID PDB_ID GUID
- ---------- ---------- ---------- --------------------------------
- PDB_NAME
- --------------------------------------------------------------------------------
- STATUS
- ---------
- 3 3404205601 3 4784B01598A17301E0531A00A8C082AB
- PDB1_1
- NORMAL
- SQL>
- select * from v$logfile;SQL>
- GROUP# STATUS TYPE
- ---------- ------- -------
- MEMBER
- --------------------------------------------------------------------------------
- IS_ CON_ID
- --- ----------
- 3 ONLINE
- +DATA/CDB1/ONLINELOG/group_3.264.934887917
- NO 0
- 3 ONLINE
- +FRA/CDB1/ONLINELOG/group_3.259.934887919
- YES 0
- GROUP# STATUS TYPE
- ---------- ------- -------
- MEMBER
- --------------------------------------------------------------------------------
- IS_ CON_ID
- --- ----------
- 2 ONLINE
- +DATA/CDB1/ONLINELOG/group_2.263.934887915
- NO 0
- 2 ONLINE
- +FRA/CDB1/ONLINELOG/group_2.258.934887917
- GROUP# STATUS TYPE
- ---------- ------- -------
- MEMBER
- --------------------------------------------------------------------------------
- IS_ CON_ID
- --- ----------
- YES 0
- 1 ONLINE
- +DATA/CDB1/ONLINELOG/group_1.262.934887911
- NO 0
- 1 ONLINE
- GROUP# STATUS TYPE
- ---------- ------- -------
- MEMBER
- --------------------------------------------------------------------------------
- IS_ CON_ID
- --- ----------
- +FRA/CDB1/ONLINELOG/group_1.257.934887913
- YES 0
- 6 rows selected.
- SQL> select * from v$controlfile;
- STATUS
- -------
- NAME
- --------------------------------------------------------------------------------
- IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
- --- ---------- -------------- ----------
- +DATA/CDB1/CONTROLFILE/current.261.934887907
- NO 16384 1096 0
- +FRA/CDB1/CONTROLFILE/current.256.934887907
- YES 16384 1096 0
- STATUS
- -------
- NAME
- --------------------------------------------------------------------------------
- IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
- --- ---------- -------------- ----------
- SQL>
- SQL> select c.CON_ID,c.FILE_NAME,c.STATUS from
- cdb_data_files c; 2
- CON_ID
- ----------
- FILE_NAME
- --------------------------------------------------------------------------------
- STATUS
- ---------
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.934888861
- AVAILABLE
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.934888861
- AVAILABLE
- CON_ID
- ----------
- FILE_NAME
- --------------------------------------------------------------------------------
- STATUS
- ---------
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.934888861
- AVAILABLE
- 3
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.934888861
- CON_ID
- ----------
- FILE_NAME
- --------------------------------------------------------------------------------
- STATUS
- ---------
- AVAILABLE
- SQL> select * from dba_data_files;
- FILE_NAME
- --------------------------------------------------------------------------------
- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
- ---------- ------------------------------ ---------- ---------- ---------
- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
- ------------ --- ---------- ---------- ------------ ---------- -----------
- ONLINE_
- -------
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.934888861
- 11 EXAMPLE 1304166400 159200 AVAILABLE
- 10 YES 3.4360E+10 4194302 80 1303117824 159072
- ONLINE
- FILE_NAME
- --------------------------------------------------------------------------------
- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
- ---------- ------------------------------ ---------- ---------- ---------
- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
- ------------ --- ---------- ---------- ------------ ---------- -----------
- ONLINE_
- -------
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.934888861
- 10 USERS 5242880 640 AVAILABLE
- 9 YES 3.4360E+10 4194302 160 4194304 512
- ONLINE
- FILE_NAME
- --------------------------------------------------------------------------------
- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
- ---------- ------------------------------ ---------- ---------- ---------
- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
- ------------ --- ---------- ---------- ------------ ---------- -----------
- ONLINE_
- -------
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.934888861
- 9 SYSAUX 629145600 76800 AVAILABLE
- 4 YES 3.4360E+10 4194302 1280 628097024 76672
- ONLINE
- FILE_NAME
- --------------------------------------------------------------------------------
- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
- ---------- ------------------------------ ---------- ---------- ---------
- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
- ------------ --- ---------- ---------- ------------ ---------- -----------
- ONLINE_
- -------
- +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.934888861
- 8 SYSTEM 283115520 34560 AVAILABLE
- 1 YES 3.4360E+10 4194302 1280 282066944 34432
- SYSTEM
- SQL> set linesize 1000
- SQL> select t.CON_ID,t.NAME,d.NAME,d.STATUS
- from v$tablespace t, v$datafile d
- where t.TS#=d.TS# and t.CON_ID=d.CON_ID
- order by 1,2; 2 3 4
- CON_ID NAME NAME STATUS
- ---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------
- 0 UNDOTBS1 +DATA/CDB1/DATAFILE/undotbs1.260.934887851 ONLINE
- 3 EXAMPLE +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.934888861 ONLINE
- 3 SYSAUX +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.934888861 ONLINE
- 3 SYSTEM +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.934888861 SYSTEM
- 3 USERS +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.934888861 ONLINE
- SQL> select c.CON_ID,c.FILE_NAME,c.TABLESPACE_NAME,c.STATUS
- from cdb_temp_files c; 2
- CON_ID FILE_NAME TABLESPACE_NAME STATUS
- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------
- 3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/pdb1_1_temp012017-02-02_11-23-23-am.dbf TEMP ONLINE
- SQL>
- SQL> select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
- c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
- from cdb_users c
- where c.USERNAME='SYSTEM'; 2 3 4
- COM CON_ID DEFAULT_TABLESPACE TEMPORARY_TABLESPACE INITIAL_RSRC_CONSUMER_GROUP USERNAME
- --- ---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
- YES 3 SYSTEM TEMP SYS_GROUP SYSTEM
- SQL> select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
- c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
- from cdb_users c
- where c.COMMON='NO'; 2 3 4
- COM CON_ID DEFAULT_TABLESPACE TEMPORARY_TABLESPACE INITIAL_RSRC_CONSUMER_GROUP USERNAME
- --- ---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
- NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP IX
- NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP SH
- NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP BI
- NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP OE
- NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP HR
- NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP PM
- NO 3 USERS TEMP DEFAULT_CONSUMER_GROUP PDBADMIN
- NO 3 USERS TEMP DEFAULT_CONSUMER_GROUP SCOTT
- 8 rows selected.
- SQL> select u.USERNAME
- from dba_users u
- where u.COMMON='NO'; 2 3
- USERNAME
- --------------------------------------------------------------------------------------------------------------------------------
- IX
- SH
- BI
- OE
- HR
- PM
- PDBADMIN
- SCOTT
- 8 rows selected.
- SQL> select u.USERNAME
- 2 , u.con_id
- 3 from cdb_users u
- 4 where u.COMMON='NO';
- USERNAME CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- ----------
- IX 3
- SH 3
- BI 3
- OE 3
- HR 3
- PM 3
- PDBADMIN 3
- SCOTT 3
- 8 rows selected.
- SQL> select r.ROLE,r.CON_ID
- from cdb_roles r
- where r.COMMON='NO'; 2 3
- no rows selected
- SQL> select *
- from cdb_roles r
- order by r.CON_ID, r.ROLE; 2 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- ADM_PARALLEL_EXECUTE_TASK NO NONE YES Y 3
- APEX_ADMINISTRATOR_ROLE NO NONE YES Y 3
- APEX_GRANTS_FOR_NEW_USERS_ROLE NO NONE YES Y 3
- AQ_ADMINISTRATOR_ROLE NO NONE YES Y 3
- AQ_USER_ROLE NO NONE YES Y 3
- AUDIT_ADMIN NO NONE YES Y 3
- AUDIT_VIEWER NO NONE YES Y 3
- AUTHENTICATEDUSER NO NONE YES Y 3
- CAPTURE_ADMIN NO NONE YES Y 3
- CDB_DBA NO NONE YES Y 3
- CONNECT NO NONE YES Y 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- CSW_USR_ROLE NO NONE YES Y 3
- CTXAPP NO NONE YES Y 3
- DATAPUMP_EXP_FULL_DATABASE NO NONE YES Y 3
- DATAPUMP_IMP_FULL_DATABASE NO NONE YES Y 3
- DBA NO NONE YES Y 3
- DBFS_ROLE NO NONE YES Y 3
- DELETE_CATALOG_ROLE NO NONE YES Y 3
- DV_ACCTMGR NO NONE YES Y 3
- DV_ADMIN NO NONE YES Y 3
- DV_AUDIT_CLEANUP NO NONE YES Y 3
- DV_DATAPUMP_NETWORK_LINK NO NONE YES Y 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- DV_GOLDENGATE_ADMIN NO NONE YES Y 3
- DV_GOLDENGATE_REDO_ACCESS NO NONE YES Y 3
- DV_MONITOR NO NONE YES Y 3
- DV_OWNER NO NONE YES Y 3
- DV_PATCH_ADMIN NO NONE YES Y 3
- DV_PUBLIC NO NONE YES Y 3
- DV_REALM_OWNER NO NONE YES Y 3
- DV_REALM_RESOURCE NO NONE YES Y 3
- DV_SECANALYST NO NONE YES Y 3
- DV_STREAMS_ADMIN NO NONE YES Y 3
- DV_XSTREAM_ADMIN NO NONE YES Y 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- EJBCLIENT NO NONE YES Y 3
- EM_EXPRESS_ALL NO NONE YES Y 3
- EM_EXPRESS_BASIC NO NONE YES Y 3
- EXECUTE_CATALOG_ROLE NO NONE YES Y 3
- EXP_FULL_DATABASE NO NONE YES Y 3
- GATHER_SYSTEM_STATISTICS NO NONE YES Y 3
- GDS_CATALOG_SELECT NO NONE YES Y 3
- GLOBAL_AQ_USER_ROLE GLOBAL GLOBAL YES Y 3
- GSMADMIN_ROLE NO NONE YES Y 3
- GSMUSER_ROLE NO NONE YES Y 3
- GSM_POOLADMIN_ROLE NO NONE YES Y 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- HS_ADMIN_EXECUTE_ROLE NO NONE YES Y 3
- HS_ADMIN_ROLE NO NONE YES Y 3
- HS_ADMIN_SELECT_ROLE NO NONE YES Y 3
- IMP_FULL_DATABASE NO NONE YES Y 3
- JAVADEBUGPRIV NO NONE YES Y 3
- JAVAIDPRIV NO NONE YES Y 3
- JAVASYSPRIV NO NONE YES Y 3
- JAVAUSERPRIV NO NONE YES Y 3
- JAVA_ADMIN NO NONE YES Y 3
- JAVA_DEPLOY NO NONE YES Y 3
- JMXSERVER NO NONE YES Y 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- LBAC_DBA NO NONE YES Y 3
- LOGSTDBY_ADMINISTRATOR NO NONE YES Y 3
- OEM_ADVISOR NO NONE YES Y 3
- OEM_MONITOR NO NONE YES Y 3
- OLAP_DBA NO NONE YES Y 3
- OLAP_USER NO NONE YES Y 3
- OLAP_XS_ADMIN NO NONE YES Y 3
- OPTIMIZER_PROCESSING_RATE NO NONE YES Y 3
- ORDADMIN NO NONE YES Y 3
- PDB_DBA NO NONE YES Y 3
- PROVISIONER NO NONE YES Y 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- RECOVERY_CATALOG_OWNER NO NONE YES Y 3
- RECOVERY_CATALOG_USER NO NONE YES Y 3
- RESOURCE NO NONE YES Y 3
- SCHEDULER_ADMIN NO NONE YES Y 3
- SELECT_CATALOG_ROLE NO NONE YES Y 3
- SPATIAL_CSW_ADMIN NO NONE YES Y 3
- SPATIAL_WFS_ADMIN NO NONE YES Y 3
- WFS_USR_ROLE NO NONE YES Y 3
- WM_ADMIN_ROLE NO NONE YES Y 3
- XDBADMIN NO NONE YES Y 3
- XDB_SET_INVOKER NO NONE YES Y 3
- ROLE PASSWORD AUTHENTICAT COM O CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------- ----------- --- - ----------
- XDB_WEBSERVICES NO NONE YES Y 3
- XDB_WEBSERVICES_OVER_HTTP NO NONE YES Y 3
- XDB_WEBSERVICES_WITH_PUBLIC NO NONE YES Y 3
- XS_CACHE_ADMIN NO NONE YES Y 3
- XS_NAMESPACE_ADMIN NO NONE YES Y 3
- XS_RESOURCE NO NONE YES Y 3
- XS_SESSION_ADMIN NO NONE YES Y 3
- 84 rows selected.
- SQL>
- ---------------------------------------------
- select * from cdb_sys_privs sp
- where sp.CON_ID=3 and sp.COMMON='YES';
- 有953个,从根容器上创下来的系统权限通用授权。
- --------
- 除此以外还有57个系统权限的本地授权。
- SQL> select * from cdb_sys_privs sp
- where sp.CON_ID=3 and sp.COMMON='NO'; 2
- GRANTEE PRIVILEGE ADM COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ----------
- IX CREATE RULE SET NO NO 3
- OE QUERY REWRITE NO NO 3
- HR CREATE VIEW NO NO 3
- BI CREATE SYNONYM NO NO 3
- IX CREATE CLUSTER NO NO 3
- IX CREATE INDEXTYPE NO NO 3
- IX CREATE TRIGGER NO NO 3
- IX CREATE PROCEDURE NO NO 3
- SH CREATE SEQUENCE NO NO 3
- OE UNLIMITED TABLESPACE NO NO 3
- HR UNLIMITED TABLESPACE NO NO 3
- GRANTEE PRIVILEGE ADM COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ----------
- IX ALTER SESSION NO NO 3
- SH ALTER SESSION NO NO 3
- IX CREATE OPERATOR NO NO 3
- HR CREATE DATABASE LINK NO NO 3
- IX CREATE SEQUENCE NO NO 3
- SH CREATE SYNONYM NO NO 3
- SCOTT UNLIMITED TABLESPACE NO NO 3
- BI UNLIMITED TABLESPACE NO NO 3
- BI ALTER SESSION NO NO 3
- PDB_DBA CREATE SESSION NO NO 3
- IX CREATE RULE NO NO 3
- GRANTEE PRIVILEGE ADM COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ----------
- IX CREATE VIEW NO NO 3
- IX CREATE SYNONYM NO NO 3
- OE CREATE SYNONYM NO NO 3
- SH CREATE TABLE NO NO 3
- IX UNLIMITED TABLESPACE NO NO 3
- SH UNLIMITED TABLESPACE NO NO 3
- SH CREATE SESSION NO NO 3
- IX CREATE TYPE NO NO 3
- OE CREATE MATERIALIZED VIEW NO NO 3
- OE CREATE DATABASE LINK NO NO 3
- SH CREATE DATABASE LINK NO NO 3
- GRANTEE PRIVILEGE ADM COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ----------
- HR CREATE SEQUENCE NO NO 3
- BI CREATE TABLE NO NO 3
- IX CREATE TABLE NO NO 3
- IX CREATE DATABASE LINK NO NO 3
- SH CREATE CLUSTER NO NO 3
- HR CREATE SESSION NO NO 3
- PDB_DBA SET CONTAINER NO NO 3
- SH CREATE DIMENSION NO NO 3
- BI CREATE DATABASE LINK NO NO 3
- BI CREATE VIEW NO NO 3
- OE CREATE VIEW NO NO 3
- GRANTEE PRIVILEGE ADM COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ----------
- SH CREATE VIEW NO NO 3
- BI CREATE CLUSTER NO NO 3
- PM UNLIMITED TABLESPACE NO NO 3
- HR ALTER SESSION NO NO 3
- BI CREATE SESSION NO NO 3
- OE CREATE SESSION NO NO 3
- PDB_DBA CREATE PLUGGABLE DATABASE NO NO 3
- IX SELECT ANY DICTIONARY NO NO 3
- SH QUERY REWRITE NO NO 3
- SH CREATE MATERIALIZED VIEW NO NO 3
- BI CREATE SEQUENCE NO NO 3
- GRANTEE PRIVILEGE ADM COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ----------
- HR CREATE SYNONYM NO NO 3
- IX CREATE SESSION NO NO 3
- 57 rows selected.
- SQL>
- 在对象权限上,根容器的通用授权8000并不能完全传递给插件,原因很简单:插件上没有一部份对象!!!!
- SQL> select count(*)
- from cdb_tab_privs tp
- where tp.CON_ID=3 and tp.COMMON='YES'; 2 3
- COUNT(*)
- ----------
- 7674
- SQL> select count(*)
- from cdb_tab_privs tp
- where tp.CON_ID=3 and tp.COMMON='NO'; 2 3
- COUNT(*)
- ----------
- 37306
- SQL> select 7674 + 37306 from dual;
- 7674+37306
- ----------
- 44980
- 由于目前系统里没有本地role,所以根容器里通用role的通用授权(174个),会传递给插件容器:
- SQL> select rp.GRANTEE, rp.GRANTED_ROLE, rp.COMMON,rp.CON_ID
- from cdb_role_privs rp
- where rp.CON_ID=3 and rp.COMMON='YES'; 2 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS JMXSERVER YES 3
- SYS XDB_SET_INVOKER YES 3
- SYS XDBADMIN YES 3
- SYS IMP_FULL_DATABASE YES 3
- DBA OLAP_DBA YES 3
- DBA SCHEDULER_ADMIN YES 3
- DBA OPTIMIZER_PROCESSING_RATE YES 3
- DBA DATAPUMP_IMP_FULL_DATABASE YES 3
- SYSTEM AQ_ADMINISTRATOR_ROLE YES 3
- EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE YES 3
- GSMUSER_ROLE CONNECT YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- HS_ADMIN_ROLE HS_ADMIN_EXECUTE_ROLE YES 3
- OEM_MONITOR SELECT_CATALOG_ROLE YES 3
- OJVMSYS RESOURCE YES 3
- DVSYS DV_DATAPUMP_NETWORK_LINK YES 3
- OLAP_XS_ADMIN XS_RESOURCE YES 3
- SPATIAL_CSW_ADMIN_USR CONNECT YES 3
- DVF CONNECT YES 3
- DV_OWNER DV_AUDIT_CLEANUP YES 3
- SYS APEX_GRANTS_FOR_NEW_USERS_ROLE YES 3
- SYS APEX_ADMINISTRATOR_ROLE YES 3
- SYS JAVADEBUGPRIV YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS JAVAIDPRIV YES 3
- SYS EM_EXPRESS_BASIC YES 3
- SYS RECOVERY_CATALOG_USER YES 3
- SYS RECOVERY_CATALOG_OWNER YES 3
- SYS OPTIMIZER_PROCESSING_RATE YES 3
- SYS XS_SESSION_ADMIN YES 3
- SYS XS_RESOURCE YES 3
- SYS GSMUSER_ROLE YES 3
- SYS DELETE_CATALOG_ROLE YES 3
- DBA OLAP_XS_ADMIN YES 3
- DBA DELETE_CATALOG_ROLE YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- DBA EXECUTE_CATALOG_ROLE YES 3
- DBSNMP CDB_DBA YES 3
- XDB DBFS_ROLE YES 3
- GSMCATUSER AQ_ADMINISTRATOR_ROLE YES 3
- JAVASYSPRIV JAVAUSERPRIV YES 3
- DVSYS RESOURCE YES 3
- SPATIAL_CSW_ADMIN_USR RESOURCE YES 3
- SYS DV_REALM_OWNER YES 3
- SYS CSW_USR_ROLE YES 3
- SYS OLAP_XS_ADMIN YES 3
- SYS CAPTURE_ADMIN YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS DBA YES 3
- DBA EM_EXPRESS_ALL YES 3
- DBA CAPTURE_ADMIN YES 3
- SYSBACKUP SELECT_CATALOG_ROLE YES 3
- IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE YES 3
- LOGSTDBY_ADMINISTRATOR RESOURCE YES 3
- DATAPUMP_EXP_FULL_DATABASE EXP_FULL_DATABASE YES 3
- GSM_POOLADMIN_ROLE CONNECT YES 3
- CTXSYS CTXAPP YES 3
- DVSYS DV_GOLDENGATE_ADMIN YES 3
- DVSYS DV_STREAMS_ADMIN YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- DVSYS DV_OWNER YES 3
- SPATIAL_WFS_ADMIN_USR RESOURCE YES 3
- LBACSYS RESOURCE YES 3
- APEX_040200 CONNECT YES 3
- DV_OWNER DV_DATAPUMP_NETWORK_LINK YES 3
- DV_OWNER DV_PUBLIC YES 3
- DV_OWNER DV_SECANALYST YES 3
- DV_OWNER DV_ADMIN YES 3
- SYS WFS_USR_ROLE YES 3
- SYS JAVAUSERPRIV YES 3
- SYS GDS_CATALOG_SELECT YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS GSM_POOLADMIN_ROLE YES 3
- SYS GSMADMIN_ROLE YES 3
- SYS XDB_WEBSERVICES_OVER_HTTP YES 3
- SYS AUTHENTICATEDUSER YES 3
- SYS XS_CACHE_ADMIN YES 3
- SYS ADM_PARALLEL_EXECUTE_TASK YES 3
- DBA WM_ADMIN_ROLE YES 3
- DBA EXP_FULL_DATABASE YES 3
- DBA SELECT_CATALOG_ROLE YES 3
- EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE YES 3
- IMP_FULL_DATABASE SELECT_CATALOG_ROLE YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- GSMADMIN_ROLE AQ_ADMINISTRATOR_ROLE YES 3
- DBSNMP OEM_MONITOR YES 3
- MDSYS RESOURCE YES 3
- GSMCATUSER CONNECT YES 3
- WMSYS WM_ADMIN_ROLE YES 3
- CTXSYS RESOURCE YES 3
- DVSYS DV_PUBLIC YES 3
- OLAPSYS OLAP_DBA YES 3
- SPATIAL_CSW_ADMIN_USR SPATIAL_CSW_ADMIN YES 3
- DV_ACCTMGR CONNECT YES 3
- SYS DV_REALM_RESOURCE YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS OLAP_DBA YES 3
- SYS ORDADMIN YES 3
- SYS HS_ADMIN_ROLE YES 3
- SYS HS_ADMIN_SELECT_ROLE YES 3
- SYS XS_NAMESPACE_ADMIN YES 3
- SYS DATAPUMP_IMP_FULL_DATABASE YES 3
- SYS EXP_FULL_DATABASE YES 3
- SYS AUDIT_VIEWER YES 3
- SYS AUDIT_ADMIN YES 3
- DBA JAVA_DEPLOY YES 3
- DBA GATHER_SYSTEM_STATISTICS YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- EXP_FULL_DATABASE SELECT_CATALOG_ROLE YES 3
- RECOVERY_CATALOG_OWNER RECOVERY_CATALOG_USER YES 3
- HS_ADMIN_ROLE HS_ADMIN_SELECT_ROLE YES 3
- XDB RESOURCE YES 3
- GSMCATUSER GSMADMIN_ROLE YES 3
- ORDSYS JAVAUSERPRIV YES 3
- DVSYS DV_XSTREAM_ADMIN YES 3
- MDDATA CONNECT YES 3
- SPATIAL_WFS_ADMIN_USR SPATIAL_WFS_ADMIN YES 3
- LBACSYS LBAC_DBA YES 3
- DV_ADMIN DV_SECANALYST YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- DV_OWNER DV_XSTREAM_ADMIN YES 3
- DV_OWNER DV_GOLDENGATE_ADMIN YES 3
- DV_OWNER DV_STREAMS_ADMIN YES 3
- SYS SPATIAL_CSW_ADMIN YES 3
- SYS CTXAPP YES 3
- SYS JAVA_ADMIN YES 3
- SYS OEM_MONITOR YES 3
- SYS HS_ADMIN_EXECUTE_ROLE YES 3
- SYS PROVISIONER YES 3
- SYS DATAPUMP_EXP_FULL_DATABASE YES 3
- SYS AQ_ADMINISTRATOR_ROLE YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS DBFS_ROLE YES 3
- DBA JAVA_ADMIN YES 3
- DBA XDB_SET_INVOKER YES 3
- DBA DATAPUMP_EXP_FULL_DATABASE YES 3
- SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE YES 3
- XDB CTXAPP YES 3
- DVSYS DV_PATCH_ADMIN YES 3
- DVSYS DV_ADMIN YES 3
- DVSYS CONNECT YES 3
- DV_OWNER DV_PATCH_ADMIN YES 3
- SYS LBAC_DBA YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS SPATIAL_WFS_ADMIN YES 3
- SYS OLAP_USER YES 3
- SYS JAVA_DEPLOY YES 3
- SYS EJBCLIENT YES 3
- SYS JAVASYSPRIV YES 3
- SYS XDB_WEBSERVICES YES 3
- SYS SCHEDULER_ADMIN YES 3
- SYS AQ_USER_ROLE YES 3
- SYS LOGSTDBY_ADMINISTRATOR YES 3
- SYS PDB_DBA YES 3
- SYS CDB_DBA YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- SYS EXECUTE_CATALOG_ROLE YES 3
- SYS RESOURCE YES 3
- DBA XDBADMIN YES 3
- DBA IMP_FULL_DATABASE YES 3
- SYSTEM DBA YES 3
- GSMUSER GSMUSER_ROLE YES 3
- DATAPUMP_IMP_FULL_DATABASE EXP_FULL_DATABASE YES 3
- DATAPUMP_IMP_FULL_DATABASE IMP_FULL_DATABASE YES 3
- GSMADMIN_ROLE CONNECT YES 3
- EM_EXPRESS_BASIC SELECT_CATALOG_ROLE YES 3
- DBSNMP DV_MONITOR YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- GSMCATUSER GSM_POOLADMIN_ROLE YES 3
- DVSYS DV_AUDIT_CLEANUP YES 3
- DVSYS DV_ACCTMGR YES 3
- DVSYS DV_SECANALYST YES 3
- SYS XDB_WEBSERVICES_WITH_PUBLIC YES 3
- SYS OEM_ADVISOR YES 3
- SYS EM_EXPRESS_ALL YES 3
- SYS GATHER_SYSTEM_STATISTICS YES 3
- SYS SELECT_CATALOG_ROLE YES 3
- SYS CONNECT YES 3
- OUTLN RESOURCE YES 3
- GRANTEE GRANTED_ROLE COM CON_ID
- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- ----------
- EM_EXPRESS_ALL EM_EXPRESS_BASIC YES 3
- MDSYS CONNECT YES 3
- DVSYS DV_GOLDENGATE_REDO_ACCESS YES 3
- DVSYS DV_MONITOR YES 3
- MDDATA RESOURCE YES 3
- SPATIAL_WFS_ADMIN_USR CONNECT YES 3
- APEX_040200 RESOURCE YES 3
- DV_OWNER DV_GOLDENGATE_REDO_ACCESS YES 3
- DV_OWNER DV_MONITOR YES 3
- 174 rows selected.
- SQL>
- 通用角色在根容器上没有完全授权完,剩下17进行通用角色本地授权:
- <div class="blockcode"><blockquote>SQL> select rp.GRANTED_ROLE from cdb_role_privs rp where rp.CON_ID=3 and rp.COMMON='NO';
- GRANTED_ROLE
- --------------------------------------------------------------------------------------------------------------------------------
- CONNECT
- CONNECT
- AQ_ADMINISTRATOR_ROLE
- RESOURCE
- CONNECT
- RESOURCE
- XDBADMIN
- RESOURCE
- RESOURCE
- SELECT_CATALOG_ROLE
- PDB_DBA
- GRANTED_ROLE
- --------------------------------------------------------------------------------------------------------------------------------
- RESOURCE
- RESOURCE
- RESOURCE
- SELECT_CATALOG_ROLE
- AQ_USER_ROLE
- CONNECT
- 17 rows selected.
- SQL>
复制代码
在多租户的根容器上查v$视图:
- select d.CON_ID,d.NAME,d.OPEN_MODE,d.CDB
- from v$database d;
-
- select i.INSTANCE_MODE, i.INSTANCE_NAME,i.CON_ID
- from v$instance i;
-
- select con_id, name from v$datafile;
- select s.CON_ID,s.NAME,s.PDB
- from v$services s;
-
- select p.CON_ID,p.NAME,p.OPEN_MODE
- from v$pdbs p;
-
- select sys_context('userenv','con_id') from dual;
-
- select sys_context('userenv','con_name') from dual;
- select c.CON_ID,c.DBID,c.PDB_ID, c.GUID , c.PDB_NAME,c.STATUS
- from cdb_pdbs c;
-
- select * from v$logfile;
-
- select * from v$controlfile;
- select c.CON_ID,c.FILE_NAME,c.STATUS from
- cdb_data_files c;
-
- select * from dba_data_files;
-
- select t.CON_ID,t.NAME,d.NAME,d.STATUS
- from v$tablespace t, v$datafile d
- where t.TS#=d.TS# and t.CON_ID=d.CON_ID
- order by 1,2;
-
- select c.CON_ID,c.FILE_NAME,c.TABLESPACE_NAME,c.STATUS
- from cdb_temp_files c;
-
- select t.CON_ID,t.NAME,d.NAME,d.STATUS
- from v$tablespace t, v$tempfile d
- where t.TS#=d.TS# and t.CON_ID=d.CON_ID
- order by 1,2;
- select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
- c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
- from cdb_users c
- where c.USERNAME='SYSTEM';
-
-
- select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
- c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
- from cdb_users c
- where c.COMMON='NO';
-
-
- select u.USERNAME
- from dba_users u
- where u.COMMON='NO';
- select r.ROLE,r.CON_ID
- from cdb_roles r
- where r.COMMON='NO';
-
- select *
- from cdb_roles r
- where con_id=3
- order by r.CON_ID, r.ROLE;
- select count(*) from system_privilege_map;
- select count(*) from table_privilege_map;
- select * from cdb_sys_privs sp
- where sp.CON_ID=3 and sp.COMMON='YES';
- select * from cdb_sys_privs sp
- where sp.CON_ID=1 and sp.COMMON='NO';
-
- select tp.GRANTEE,tp.GRANTOR,tp.CON_ID,tp.TABLE_NAME
- from cdb_tab_privs tp
- where tp.CON_ID=1 and tp.COMMON='YES';
- ----根容器上可以有对象权限的本地授权!!!!!!!!!
-
- select tp.GRANTEE,tp.GRANTOR,tp.CON_ID,tp.TABLE_NAME
- from cdb_tab_privs tp
- where tp.CON_ID=1 and tp.COMMON='NO';
- select rp.GRANTEE, rp.GRANTED_ROLE, rp.COMMON,rp.CON_ID
- from cdb_role_privs rp
- where rp.CON_ID=1 and rp.COMMON='YES';
复制代码 临时文件和GUID的关系:
- [oracle@station26 ~]$ . oraenv
- ORACLE_SID = [cdb1] ? +ASM
- The Oracle base remains unchanged with value /u01/app/oracle
- [oracle@station26 ~]$ asmcmd
- ASMCMD> ls
- DATA/
- FRA/
- ASMCMD> cd DATA/
- ASMCMD> ls
- ASM/
- CDB1/
- ORCL2/
- orapwasm
- ASMCMD> cd CDB1/
- ASMCMD> ls
- 47847425558B6E0FE0531A00A8C0F7C0/
- 4784B01598A17301E0531A00A8C082AB/
- CONTROLFILE/
- DATAFILE/
- FD9AC20F64D244D7E043B6A9E80A2F2F/
- FD9BD2B44413096FE043B6A9E80ABC28/
- ONLINELOG/
- PARAMETERFILE/
- TEMPFILE/
- ASMCMD> cd FD9AC20F64D244D7E043B6A9E80A2F2F/
- ASMCMD> ls
- DATAFILE/
- ASMCMD> cd DATAFILE/
- ASMCMD> ls
- SYSAUX.266.934887937
- SYSTEM.267.934887939
- pdbseed_temp012017-02-02_11-06-38-AM.dbf
- ASMCMD> ls -l *
- Type Redund Striped Time Sys Name
- DATAFILE MIRROR COARSE DEC 22 15:00:00 Y SYSAUX.266.934887937
- DATAFILE MIRROR COARSE DEC 22 15:00:00 Y SYSTEM.267.934887939
- TEMPFILE MIRROR COARSE DEC 22 15:00:00 N pdbseed_temp012017-02-02_11-06-38-AM.dbf => +DATA/CDB1/47847425558B6E0FE0531A00A8C0F7C0/TEMPFILE/TEMP.268.934887999
- ASMCMD> cd ..
- ASMCMD> ls
- DATAFILE/
- ASMCMD> cd ..
- ASMCMD> ls
- 47847425558B6E0FE0531A00A8C0F7C0/
- 4784B01598A17301E0531A00A8C082AB/
- CONTROLFILE/
- DATAFILE/
- FD9AC20F64D244D7E043B6A9E80A2F2F/
- FD9BD2B44413096FE043B6A9E80ABC28/
- ONLINELOG/
- PARAMETERFILE/
- TEMPFILE/
- ASMCMD> cd FD9BD2B44413096FE043B6A9E80ABC28/
- ASMCMD> ls
- DATAFILE/
- ASMCMD> cd datafile
- ASMCMD> ls -l
- Type Redund Striped Time Sys Name
- DATAFILE MIRROR COARSE DEC 22 15:00:00 Y EXAMPLE.270.934888861
- DATAFILE MIRROR COARSE DEC 22 15:00:00 Y SYSAUX.271.934888861
- DATAFILE MIRROR COARSE DEC 22 15:00:00 Y SYSTEM.272.934888861
- DATAFILE MIRROR COARSE DEC 22 15:00:00 Y USERS.273.934888861
- TEMPFILE MIRROR COARSE DEC 22 15:00:00 N pdb1_1_temp012017-02-02_11-23-23-AM.dbf => +DATA/CDB1/4784B01598A17301E0531A00A8C082AB/TEMPFILE/TEMP.274.934889005
- ASMCMD>
复制代码
|
|