Techgoeasy

How to check tablespace in Oracle To list the names and various other of all tablespaces in a database, use the following query on the DBA_TABLESPACES view: SELECT TABLESPACE_NAME “TABLESPACE”,   EXTENT_MANAGEMENT,FORCE_LOGGING,BLOCK_SIZE,SEGMENT_SPACE_MANAGEMENT   FROM DBA_TABLESPACES; To list the Datafiles and Associated Tablespaces of a Database To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view SELECT  FILE_NAME, BLOCKS, … Continue reading How to check tablespace in Oracle Database »

We sometimes have shrink the datafile to reclaim filesystem space. We can do it like Alter database datafile  <datafile name > resize  <smaller size>; Many times we may get the below error as the extent may spread out on the outer of the file ORA-03297: file contains used data beyond requested RESIZE value”.   To resolve the error,We can find the true resize value for  … Continue reading How to shrink the datafile in Oracle »

-You alter or modify  a table using the ALTER TABLE statement. -The table must be contained in your schema To alter a table, or you should either have  ALTER object privilege for the table or the ALTER ANY TABLE system privilege. -If a view, materialized view, trigger,  function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns … Continue reading How to Alter Table in Oracle »

-Oracle allows us  create a temporary table. -The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. -We have to use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. -The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific DELETE ROWS … Continue reading Creating a Global Temporary Table in Oracle »

We can use truncate table command to delete all the rows  in the table and it releases all the storage space allocated to the table. It resets the high water mark of the table This command cannot be rollback You should be owner of the table to execute the operation -Truncating the table is a DDL command and it does not fire the on delete … Continue reading How to Truncate TABLE in Oracle »

-The DROP TABLE oracle command is used to remove a table from the database. -The dropped table and its data remain no longer available for selection. Dropping a table drops the index and triggers associated with it. Views, synonym are not dropped but they become invalid -Dropped table can be recovered using FLASHBACK utility, if available in recycle bin. This functionality is available from 10g onwards … Continue reading DROP TABLE ORACLE statement »

Last updated on September 5th, 2018 at 04:26 amHere is the glossary on Oracle Performance tuning. It is quite complex and interesting topics. Oracle performance tuning need through analysis  and knowledge of all the aspect of Oracle database Oracle Parser It performs syntax analysis as well as semantic analysis of SQL statements for execution, expands views referenced in the query into separate query blocks, optimizing it … Continue reading Oracle Performance tuning Glossary »