Oracle Enterprise Manager Cloud Control 13c Release 4 Certification Matrix on Linux
Category: Oracle
Oracle Fails to Drop Unused Columns
When trying to drop an Oracle table unused columns, the sql command executes and shows successful, but actually the unused columns are not dropped.
SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';
OWNE TABLE_NAME COUNT
------- -------------------- ----------
TESTER TRANSACTIONS 1
SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;
Table altered.
SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';
OWNE TABLE_NAME COUNT
------- -------------------- ----------
TESTER TRANSACTIONS 1
Further investigation shows the table is created with compression :
SQL>select OWNER, TABLE_NAME, COMPRESSION, COMPRESS_FOR from dba_tables where table_name='TRANSACTIONS';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
-------- ------------ --------- -------------
TESTER TRANSACTIONS ENABLED ADVANCED
Change table to uncompressing status without moving data:
SQL> alter table TRANSACTIONS NOCOMPRESS;
Table altered.
SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;
ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
Have to move table with nocompress option, then drop unused columns successfully.
SQL> alter table TRANSACTIONS move nocompress;
Table altered.
SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;
Table altered.
SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';
no rows selected
Oracle Enterprise Manager Corrective Action (CA) Execution Stats
How many times Corrective Action (CA) has been executed one year so far ?
SQL> select CA_NAME, count(*)
from sysman.MGMT$CA_EXECUTIONS
where START_TIME>=sysdate-365
group by CA_NAME order by 1
CA_NAME COUNT(*)
---------------------------------------- ----------
...
..
.
EXTEND_TABLESPACE_NORAC 3
EXTEND_TABLESPACE_PDB 16
EXTEND_TABLESPACE_RAC 70
NON-RAC_RMAN_AUTO_BACKUP_ARCHIVELOGS 56
RMAN_AUTO_BACKUP_ARCHIVELOGS 533
...
..
.
10 rows selected.
About view MGMT$CA_EXECUTIONS , Oracle official documentation is here or just click the following link.
How to Get All Oracle Databases Size From OEM Repository
Database manager asks you as a DBA about all the database size in the supported environment .
On 13.1 OMS repository:
SQL> select host_name,target_name, sum( FILE_SIZE/1024/1024/1024)
from mgmt$db_datafiles
group by host_name,target_name
order by host_name, target_name;
HOST_NAME TARGET_NAME SUM(FILE_SIZE/1024/1024/1024)
---------------------------------------- ---------------------------------------- -----------------------------
HOST1 DB1 38.65
HOST2 DB2 385.88
HOST3 DB3 27.52
...
..
.
OR we can use another another view mgmt$db_tablespaces :
SQL> select host_name,target_name,sum( TABLESPACE_SIZE/1024/1024/1024)
from mgmt$db_tablespaces
group by host_name,target_name
order by 1,2;
How to Know When Oracle User Password Changed?
Oracle database internal table SYS.USER$ contains useful information about users and roles. Some of the columns have got following meaning:
- NAME – name for user or role
- TYPE# – 0 for role or 1 for user
- CTIME – the date of creation
- PTIME – the date the password was last changed
- EXPTIME – the date the password has last expired
- LTIME – the date the resource was last locked
- LCOUNT – number of failed logon
SQL> SELECT
name,
ctime,
ptime
FROM
sys.user$
WHERE
name = 'SYS';
NAME CTIME PTIME
-------------------- --------- ---------
SYS 31-MAR-16 11-MAY-21
Make DBA Life Easy 