Below Query will convert all the unusable and not valid indexes in Oracle. The query will cover the complete index with partition index and subpartition index.
The result will give you the rebuild command of the invalid or unusable indexes.
You can directly run that and on sqlplus and make them a valid or usable state.
QUERY :
SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.
Flashback version query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.
SQL> CREATE TABLE flashback_version_query_test (id NUMBER(10),description VARCHAR2(50));
Table created.
SQL> INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
4991867 2021-07-20 22:29:52
SQL> UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
4992195 2021-07-20 22:34:30
SQL> SELECT versions_startscn, versions_starttime,versions_endscn, versions_endtime, versions_xid, versions_operation, description from flashback_version_query_test VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2021-07-20 22:29:52', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2021-07-20 22:34:30', 'YYYY-MM-DD HH24:MI:SS')WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
---------- --------------------- ------------------ -------------- ----------- -----------
4992172 20-JUL-21 10.33.41 PM 0D0020001B040000 U THREE
4992136 20-JUL-21 10.32.23 PM 0B00050021040000 U TWO
4992172 20-JUL-21 10.33.41 PM ONE
SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, description FROM flashback_version_query_test
VERSIONS BETWEEN SCN 4991867 and 4992195 WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
4992172 20-JUL-21 10.33.41 PM 0D0020001B040000 U THREE
4992136 20-JUL-21 10.32.23 PM 4992172 20-JUL-21 10.33.41 PM 0B00050021040000 U TWO
4992136 20-JUL-21 10.32.23 PM ONE
SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('06000000FA030000');
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
The recycle bin is a logical collection of previously dropped objects. This feature doesn’t use flashback logs or undo, so it is distinct from the other flashback technologies.
Enable/Disable Recycle Bin
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL> alter session set recyclebin = on;
Session altered.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string ON
(RECYCLE BIN)
SQL> CREATE TABLE flashback_drop_test (id NUMBER(10));
Table created.
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);
1 row created.
SQL> commit;
Commit complete.
SQL> DROP TABLE flashback_drop_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07
EST
SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;
SELECT * FROM flashback_drop_test;
ID
----------
1
Rename Table
SQL> show RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
S BIN$x3mKgoseD/7gUxkBqMAnAA==$0 TABLE 2021-07-19:17:20:17
SQL> create table s(n number);
Table created.
SQL> FLASHBACK TABLE s TO BEFORE DROP;
FLASHBACK TABLE s TO BEFORE DROP
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL> FLASHBACK TABLE s TO BEFORE DROP rename to b;
Flashback complete.
SQL> select * from b;
no rows selected
SQL> show RECYCLEBIN;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality.
SOL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN 4933950; COUNT(*)
----------
1SOL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN4934857 ; COUNT(*)
----------
0
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Checking Tablespaces in Database
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
Create New Tablespace
SQL> create tablespace india datafile
'/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' size 30m;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
INDIA
Check Datafile in whole Database
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORACLEAGENT/system.dbf
SYSAUX /u01/app/oracle/oradata/ORACLEAGENT/sysaux.dbf
UNDOTBS /u01/app/oracle/oradata/ORACLEAGENT/undo1.dbf
DATA /u01/app/oracle/oradata/ORACLEAGENT/data01.dbf
INDIA /u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
Check Datafile's in particular Tablespace.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
CheckTablespace size in particular tablespace
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
2048
How toCheckTablespace size in whole Database
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------- --------------------
SYSTEM 2048
INDIA 30
SYSAUX 2048
UNDOTBS 2048
DATA 60
How toResize Datafiles
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL>alter database datafile '/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' resize60m;
Database altered.
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
60
Add New Datafile
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL> alter tablespace india add datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' size 30m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
Rename or Relocate datafilesafter the 12c method
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
SQL> alter database move datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' to '/u02/india03.dbf';
Database altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
Undo – Written on Blue Keyboard Key. Male Hand Presses Button on Black PC Keyboard. Closeup View. Blurred Background. 3D Render.
Undo tablespace check undo tablespace:
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
Check to undo retention:
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
Check to undo management:
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
SQL> alter system set undo_tablespace='undotbs2' scope=spfile;
System altered.
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string undotbs2
change undo_retention
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> alter system set undo_retention=920;
System altered.
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 920
check db block size
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
change undo management { auto | manual }
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
SQL> alter system set undo_management='auto' scope=spfile;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
Undo stages:
SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb,
count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;
TABLESPACE STATUS SUM_IN_MB COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1 EXPIRED 1399.25 200
UNDOTBS1 UNEXPIRED 168 21
UNDOTBS2 EXPIRED .375 6
UNDOTBS2 UNEXPIRED 5.4375 42
Active, expired and unexpired transaction space usage in Undo Tablespace:-
ACTIVE: Status shows us the active transaction going in the database, utilizing the undo tablespace and cannot be truncated.
EXPIRED: Status shows us the transaction which is completed and complete the undo_retention time and now the first candidate for truncated from undo tablespace.
UNEXPIRED: Status shows us the transaction which is completed but not completed the undo retention time. It can be truncated if required.
Free blocks : At the time of creating new undo tablespace shows empty blocks
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Zero Downtime Migration (ZDM) 21c is available for download! ZDM is Oracle’s premier solution for moving your on-premises Oracle Database workloads to Oracle Cloud, supporting a variety of Oracle Database versions as the source and most Oracle Cloud Database Services as targets. Zero Downtime Migration 21c enhances the existing functionality by adding the long-requested Logical Migration workflow, which provides even more zero downtime migration choices!
For more reference: blogs.oracle.com click the below link
In addition to the Physical Migration workflow which has been supported since the first version of ZDM (see for example: https://blogs.oracle.com/maa/announcing-oracle-zero-downtime-migration-release-197), ZDM 21c offers a Logical Migration to the Oracle Cloud in eight simple steps, which can be scheduled and monitored as needed. The Logical Migration workflow allows you to migrate to Oracle Autonomous Database leveraging ZDM automation.
You can leverage two logical migration workflows with different methodologies:
Logical Offline Migration
Data Pump and Database Links based methodology
Data Pump and Backup Location based methodology
Logical Online Migration
GoldenGate, Data Pump and Database Links based methodology
GoldenGate, Data Pump and Backup Location based methodology
Exadata On-Premises Support
Oracle ZDM 21c includes Exadata Database Machine as a new target. You can now migrate your current database deployments to an on-premises Exadata Database Machine, leveraging the full automation and power of ZDM. Migration to Exadata On-Premises is only supported via ZDM’s physical migration workflow.
Autonomous Database Support
Oracle ZDM 21c can now migrate databases directly to Autonomous Database using any of the logical migration workflows. Supported targets are:
Non-CDB to CDB Migration is now supported in both Oracle ZDM’s physical and logical migration workflows, providing you with more migration flexibility via a simplified migration process that includes the Non-CDB to PDB conversion and hence, a wider choice of cloud targets.
Pre-Migration Advisor
A pre-migration advisor that evaluates the source database for feature compatibility and other limits is now included in the logical migration workflow only. You should leverage this advisor in order to better prepare for your database migration journey.
Support for Database Links
Logical Database Migration offers the possibility to establish direct Database Links between the source and the target, thus avoiding the need for a backup location as part of the migration workflow. Support for Database Links is optional, as you can also leverage a backup location such as Object Storage, NFS or the Recovery Appliance for your migration. Database Links based migration is not supported for Autonomous Database Dedicated.
Lighter ZDM Home
The Oracle ZDM software home is now lighter and more compact. Users will notice the difference already when downloading the binaries. ZDM installation and deployment has also been optimized and trimmed, allowing for an even faster instantiation of your first migration.
