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
Below are different kind of problems where you have recovery issues in standby. Before you take any decision check with your colleagues. 1) Check on source : SQL> select dest_id,error from v$archive_dest_status; 2) To know the redo apply rate : set linesize 500col item format… Read More
Dear Readers, In this article we will see VD and OCR migration online. 1) Check existing setup by running below commands : +ASM1@cloud-server-1:/opt/oracle> crsctl query css votedisk 2) Check the OCR/VD backup : 3) Ask SA to take new backup using… Read More
