How to check tablespace in Oracle Database

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 »

How to Alter Table 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 »

Creating a Global Temporary 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 »

DROP TABLE ORACLE statement

-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 »

How to check Patch application in Oracle E-Business Suite instance R12.1/R12.2

We often need to find out what all patches are applied to the EBS instance. Now this vary as per the EBS instance version. We have different queries for R12.1 and R12.2. For R12.0/R12.1 ad_bugs tables contains the information for all the patches applied to the system. we can used below query to find out the patch application in the instance sqlplus apps/<APPS PASSWORD> SELECT DISTINCT RPAD(a.bug_number, … Continue reading How to check Patch application in Oracle E-Business Suite instance R12.1/R12.2 »

Oracle Performance tuning Glossary

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 »

How to use Oracle LISTAGG Function

Last updated on September 5th, 2018 at 04:37 amOracle LISTAGG function is an analytical function which allows us to concatenate the strings for measure_column for each GROUP based on the order_by_clause. This is present in Oracle from 11gR2 The syntax for the LISTAGG function in Oracle is LISTAGG (measure_column [, ‘delimiter’]) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)] Explanation of terms measure_column The column or expression whose … Continue reading How to use Oracle LISTAGG Function »

How to Solve ORA-00942 table or view does not exist

Last updated on September 4th, 2018 at 05:29 pmORA-00942 is one of the many errors which Oracle developer ,DBA often gets. Lets first look at the OERR output Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying … Continue reading How to Solve ORA-00942 table or view does not exist »

How to resolve the ORA-00936 missing expression

Last updated on July 12th, 2018 at 05:51 pmDescription : ORA-00936 missing expression is one of the common error everybody working in Oracle Sql must have faced some time. This generally happens when you omit important thing in the Sql statement i.e you left out an important chunk of what you were trying to run Reference : Oracle documentation Cause This Oracle error is mainly related … Continue reading How to resolve the ORA-00936 missing expression »