Oracle Category

How to convert milliseconds to date string in Oracle SQL

If you store the date in milliseconds, for instance, 1126483200000, its milliseconds since January 1, 1970, 00:00:00 GMT in Oracle, we can see, even one second converts to thousand milliseconds, its just a huge number which means nothing and hard to read. So usually we have to write Oracle SQL query statement to get this milliseconds and convert to a […]

Oracle Error :: DRG-10758 Index owner does not have the privilege to use file or URL datastore Cause. Error details: When you tried to create an index, if the index owner does not have the privilege to use file or URL datastore, it will occur the following error stack: IMP-00017: following statement failed with ORACLE […]

Oracle DBA_TABLESPACES: List All Tablespaces

DBA_TABLESPACES Introduction The view Oracle DBA_TABLESPACES describes all tablespaces in the database. so if you select it, it returns the list of the tablespaces including system and user-defined in the database. it is unlike USER_TABLESPACES, which describes the tablespaces only accessible to the current user, the following table lists the columns and description for DBA_TABLESPACES.

The size of Tablespace is combination of  size of ALL individual datafiles, You can enlarge a database in four ways: Add a datafile to a tablespace You had ready created a tablespace, due to the restriction of size of  existing datafile is reached largest size you defined before or the drive where the file is […]

Example query to check free,used space and Percent used per tablespace: select a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ) a, ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc Sample […]

Create Tablespaces in Oracle

A tablespace is a storage location where the actual data underlying database objects can be kept, it is a logical storage unit within an Oracle database and not visible in the file system of the machine on which the database resides. We can simply Use the CREATE TABLESPACE statement to create a tablespace, which is […]

Oracle’s export (exp/expdp) and import (imp/impdp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database. These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary […]