To do a selective data export in a table using expdp, query option can be used, provided if the where condition doesn’t have any subqueries.For any Query Export Dumps, having Sub query should be using a parfile instead of the direct expdp command.All the complex… Read More
SPFILE SPFILE IS SERVER PARAMETER FILE SPFILE IS A SEMI BINARY FILE. WE CANNOT EDIT MANUALLY WE CAN EDIT AS DYNAMICALLY BY USING ALTER SYSTEM ITS SPECIALY MADE FOR RAC (REAL APPLICATION CLUSTER) WE CAN CREATE SPFILE AT ANY STAGE(nomount,mount,open) IF YOUR DATABASE… Read More
USER MANAGEMENT How to create user? Syntax sys>> create user <username> identified by <password>; Create user u1 identified by u1; How to assign the privileges to user? Grant connect,resource to u1; How to assign the tablespace to user? Alter user u1 default tablespace userdata; How… Read More
TABLESPACE MANAGEMENT WHAT IS TABLESPACE ? IT’S A LOGICAL LINK BETWEEN USER AND DATAFILES.IT IS LOGICAL STORAGE OF DATA DICTIONARY: EXTENTS INFORMATION WILL GET FROM BASE TABLES LOCAL: EXTENTS INFORMATION WILL GET FROM DATAFILE HEADERS TABLESPACE MANAGEMENT HOW TO CREATE THE TABLESPACE SYS>>… Read More
set linesize 1000 pagesize 0 feedback off trimspool onwithhwm as ( — get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn),hwmts as… Read More
Rebalance status : column “Diskgroup” format A30 column “Imbalance” format 99.9 Heading “Percent|Imbalance” column “Variance” format 99.9 Heading “Percent|Disk Size|Variance” column “MinFree” format 999.99 heading “Minimum|Percent|Free” column “DiskCnt” format 9999 Heading “Disk|Count” column “Type” format A10 Heading “Diskgroup|Redundancy” SELECT g.name “Diskgroup”, 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) “Imbalance”,100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) “Variance”, 100*(min(d.free_mb/d.total_mb))… Read More
URL to access E-Business Suite : http://<web server host name>.<domain>:<port number> SSL enabled URL to access E-Business Suite : https://<web server host name>.<domain>:<port number> which is more secure How to find above URL in databasesqlplus apps/appsQUERY : SELECT home_url FROM icx_parameters; Usually data transmitted… Read More
What is Oracle Applications/Oracle E-Business Suite ?To facilitate big businesses, Oracle Corporation have created collection of software inthe category of ERP (Enterprise Resource Planning) known as modules, that areintegrated to talk to each other and known as Oracle Applications or E-BusinessSuite. Oracle E-Business Suite Releases:10.7… Read More
To check the fragmentation of all indexes on the Database SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER… Read More
Fragmented_Tables_List in HTML FORMAT : col spoolname new_value spoolname select ‘Fragmented_tables’||to_char(sysdate, ‘YYYYMONDD’)||’.html’ spoolname from dual; spool ‘&spoolname’ set pagesize 200 set markup html on WITH STAT_SPACE AS( SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE FROM DBA_TABLES WHERE OWNER=’SCOTT’), SEG_SPACE AS ( SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE,… Read More
