Find database size in Oracle

To find database size (GB) in Oracle, use below query.
select round((sum(bytes)/1048576/1024), 2) from v$datafile;

Comments

Alter auto extent on existing datafile in Oracle

To alter the existing datafile in Oracle database, use below query. alter database datafile '/u01/app/oracle/oradata/XE/system.dbf' autoextend on maxsize unlimited;

What need to do when encounter the error ORA-28001: the password has expired

When you encounter the error ORA-28001: the password has expired, execute below queries to resolve this. export ORACLE_SID=< databaseName> sqlplus / as SYSDBA SQL> alter profile default limit password_life_time unlimited; SQL> alter user <userName> identified by <password>; SQL> commit; SQL> exit;

Table and its count

Table and its count To view all tables and its count by executing command and immediately, use below commands - MSSQL SELECT sc. name + '.' + ta. name TableName, SUM (pa. rows ) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY sc. name ,ta. name ORDER BY SUM (pa. rows ) DESC

Databases

Search This Blog