Skip to content

1z0-060 Q10

In order to exploit some new storage tiers that have been provisioned by a storage administrator, the partitions of a large heap table must be moved to other tablespaces in your Oracle 12c database?
Both local and global partitioned B-tree Indexes are defined on the table.
A high volume of transactions access the table during the day and a medium volume of transactions access it at night and during weekends.
Minimal disrupt ion to availability is required. Which three statements are true about this requirement?

A.The partitions can be moved online to new tablespaces.

B.Global indexes must be rebuilt manually after moving the partitions.

C.The partitions can be compressed in the same tablespaces.

D.The partitions can be compressed in the new tablespaces.

E.Local indexes must be rebuilt manually after moving the partitions.

Answer

Answer: A C D
usefull for prepare
Oracle Database 12c: What’s New in Partitioning?
our topic explained by «Moving Partitions Online»

Create a fairly large test table + Create a global and a local index on the table.

SQL> @t3create

CREATE TABLE pt (col1, col2, col3, col4)
PARTITION by range (col1) interval (150000)
(PARTITION pdml values less than (500000),
 PARTITION px values less than (600000),
 PARTITION py values less than (700000))
as
SELECT rownum, rownum*mod(rownum,10), 1, 'aaaaaaaaaaaaaaaaaaaaaa'
 FROM dual connect by level < 1000000;

CREATE INDEX i1_pt_global on pt (col2);
CREATE INDEX i1_pt_local on pt (col1) local;

direct connect to pdb
usefull for test like this:
need to export TWO_TASK=$TNS_ENTRY
export TWO_TASK=12ee
and all works fine )

now we run script
onlinePartMove
wich modify data and move partition


[oracle@oel7-2 ora01]$ sh onlinePartMove.sh
Will update  444 records, starting date and time:
Tue Dec  1 06:18:07 EST 2015

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 06:18:09 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> Connected.
SQL> SQL>
'ONLINEMOVESTARTSAT'||TO_CHAR(
------------------------------
Online move starts at 06:18:09

SQL>
Table altered.

SQL>
'ONLINEMOVEENDSAT'||TO_CHAR(
----------------------------
Online move ends at 06:18:15

SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

Display status for global and local index

SELECT  index_name, status
   FROM  user_indexes
  WHERE index_name like 'I1_PT_G%';

INDEX_NAME                     | STATUS
------------------------------ | ------------------------
I1_PT_GLOBAL                   | VALID

SELECT  index_name, status
  FROM  user_indexes
  WHERE index_name like 'I1_PT_G%';
SELECT  index_name, status
  FROM  user_ind_partitions
  WHERE index_name like 'I1_PT_L%';

INDEX_NAME                     | STATUS
------------------------------ | ------------------------
I1_PT_LOCAL                    | USABLE
I1_PT_LOCAL                    | USABLE
I1_PT_LOCAL                    | USABLE
I1_PT_LOCAL                    | USABLE
I1_PT_LOCAL                    | USABLE

A. TRUE
B. FALSE
E. FALSE

now lets check C

15:03:03 (1)[PDBORCL]part12c@orcl> ALTER TABLE PT MOVE PARTITION SYS_P305 COMPRESS FOR ALL OPERATIONS online update indexes; ;

Table altered.

Elapsed: 00:00:00.23
15:05:16 (1)[PDBORCL]part12c@orcl> select table_name,partition_name,compression, compress_for,TABLESPACE_NAME from user_tab_partitions ;

TABLE_NAME | PARTITION_ | COMPRESSION              | COMPRESS_F | TABLESPACE_NAME
---------- | ---------- | ------------------------ | ---------- | ---------------
PT         | SYS_P306   | DISABLED                 | <NULL>     | USERS
PT         | SYS_P305   | ENABLED                  | ADVANCED   | USERS
PT         | PY         | DISABLED                 | <NULL>     | USERS
PT         | PX         | DISABLED                 | <NULL>     | USERS
PT         | PDML       | DISABLED                 | <NULL>     | USERS

check D

15:06:26 (1)[PDBORCL]part12c@orcl> ALTER TABLE PT MOVE PARTITION SYS_P306 tablespace part_p1 COMPRESS online update indexes; ;

Table altered.

Elapsed: 00:00:00.20
15:06:37 (1)[PDBORCL]part12c@orcl> select table_name,partition_name,compression, compress_for,TABLESPACE_NAME from user_tab_partitions ;

TABLE_NAME | PARTITION_ | COMPRESSION              | COMPRESS_F | TABLESPACE_NAME
---------- | ---------- | ------------------------ | ---------- | ---------------
PT         | SYS_P305   | ENABLED                  | ADVANCED   | USERS
PT         | PY         | DISABLED                 | <NULL>     | USERS
PT         | PX         | DISABLED                 | <NULL>     | USERS
PT         | PDML       | DISABLED                 | <NULL>     | USERS
PT         | SYS_P306   | ENABLED                  | BASIC      | PART_P1

check for unusable index

15:10:04 (1)[PDBORCL]part12c@orcl> @unusable_index

no rows selected

Elapsed: 00:00:00.03
15:10:07 (1)[PDBORCL]part12c@orcl> l
  1  select ' alter index '||OWNER||'.'|| index_name||' rebuild online ;' as rebuild_script from DBA_INDexes where (status  not in ( 'USABLE','VALID','N/A') or (DOMIDX_OPSTATUS<>'VALID' or DOMIDX_STATUS<>'VALID' or FUNCIDX_STATUS ='DISABLED')) and owner not like '%SYS%'
  2  union all
  3  select ' alter index '||INDEX_owner||'.'|| index_name||' rebuild PARTITION '|| PARTITION_NAME ||' online ;' from  DBA_IND_PARTITIONS     where status  not in ( 'USABLE','VALID','N/A')
  4  union all
  5* select  'alter index '||INDEX_owner||'.'|| index_name||' rebuild SUBPARTITION '|| SUBPARTITION_NAME ||' online ;' from  DBA_IND_SUBPARTITIONS     where status  not in ( 'USABLE','VALID','N/A')

C +D TRUE

[свернуть]

Primary Sidebar

Яндекс.Метрика