DBORCLOUD Blog

Online table partition migration

Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c. When an “online” clause is specified, all dml operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. No dml operations are allowed if the partition|sub-partition is moved offline. some examples … SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name; Move a table partition|sub-partition to a new tablespace offline. SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE; Move a table partition or sub-partitioning online maintaining any local/global Read More

Migration of a table partitioning

Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c. When an “online” clause is specified, all dml operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. No dml operations are allowed if the partition|sub-partition is moved offline. some examples … SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name; Move a table partition|sub-partition to a new tablespace offline. SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE; Move a table partition or sub-partitioning online maintaining any local/global Read More

Step from pid to sqltext of active session on single instance database Oracle

If you have the process ID (PID) you can back to sqltext active session associated on database using the following steps: 1) Run the statement by inserting PID alter session set nls_date_format = ‘dd-mm-yyyy hh24:mi:ss’; col machine for a20 col username for a15 col OSUSER for a15 col spid for a8 col program for a35 col username for a20 col status for a9 col sid for 9999 col serial# for 99999 col sql_id FORMAT A15 col LOGON_TIME for a19 col SQL_TEXT for a60 col event for a33 col SQL_HASH_VALUE for 99999999999 set lines 220 set pages 1000 select a.sid, a.serial#, Read More

Multiple indexes on the same column

Multiple indexes on the same column Until the release Oracle 11g was not possible to create more indexes on the same column of a table. Starting from release Oracle 12c, you can create multiple indexes on the same column as long as the index type is different. However, only one type of index is usable / visible at a given time. To test the indexes invisible, you must set the init.ora parameter: optimizer_use_use_invisible_indexes = true; Not using INVISIBLE clause will result in the error: ORA-01408: such column list already indexed An example: create unique index test_idx1 on test_tab (id1); index Read More