1z0-060 Q10 – bushmelev-aa.ru

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.

[code language=”sql”]
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;
[/code]

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

[code]

[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

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
[/code]

Display status for global and local index
[code language=”sql”]
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%’;

A. TRUE
B. FALSE
E. FALSE

now lets check C
[code language=”sql” highlight=”11″]
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 ;

[/code]

check D
[code language=”sql” highlight=”14″]
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 ;

[/code]

check for unusable index

[code language=”sql”]
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’)
[/code]

C +D TRUE

[свернуть]

Primary Sidebar