Monitoring Memory Resizing Operations with Memory_target Parameter.

To enable memory target

SQL> alter system set memory_max_target=5G scope=spfile;

SQL> alter system set memory_target=4G scope=spfile;

Turn off the SGA_TARGET and the PGA_AGGREGATE_TARGET
parameters by issuing the following ALTER SYSTEM commands:

SQL> alter system set sga_target = 0;

SQL> alter system set pga_aggregate_target = 0;

SQL>shutdown immediate;

SQL>startup


Under an automatic memory management mode, you can view the current allocations of
memory inany instance by querying the V$MEMORY_DYNAMIC_COMPONENTS
view. Querying this view provides vitalinformation to help you tune the MEMORY_TARGET parameter. Here’s how you execute a query against thisview:

SQL> select * from v$memory_target_advice order by memory_size;

Your current memory allocation is shown by the row with the MEMORY_SIZE_FACTOR value of 1 . The MEMORY_SIZE_FACTOR column shows alternate sizes of theMEMORY_TARGET
parameteras a multiple of the current MEMORY_TARGET parameter value. The
ESTD_DB_TIME column shows the timeOracle estimates it will need to complete the current workload with a specific MEMORY_TARGET value. Thus, the query results show you how much faster the database can process its work by varying the value of the
MEMORY_TARGET parameter.

ORA-04030: out of process memory while trying to allocate "N" Bytes

Cause:Oracle cannot perform requested operation with this free memory.

Action:

1)Tune pga_aggregate_target value depends on your server memory.
2)Server is windows,Increase your page file size with the help of system admin.
If you are working with UNIX platform check kernal parameters of shared memory .
3)Move to multi-threaded server.



Cheers!!!

Reclaiming Unused Index Space

We can see Reclaimable space in Schema by using below query.

SELECT'Task Name : ' || f.task_name || CHR(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || CHR(10) ||'Segment Type : ' || o.type || CHR(10) ||'Partition Name : ' || o.attr3 || CHR(10) ||'Message : ' || f.message || CHR(10) ||'More Info : ' || f.more_info || CHR(10) ||
'------------------------------------------------------' Advice FROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name;

Solution

There are a couple of effective methods for freeing up unused space associated with an index:




•Rebuilding the index
•Shrinking the index


Before you perform either of these operations, first check
USER_SEGMENTS to verify that the amount of space used corresponds with the Segment Advisor’s advice. In this example, the segment name is F_REGS_IDX1

SQL> select bytes from user_segments where segment_name = 'F_REGS_IDX1';
BYTES----------
166723584

This example uses the
ALTER INDEX...REBUILD

statement to re-organize and compact the space usedby an index:

SQL> alter index f_regs_idx1 rebuild;
Alternatively, use the
ALTER INDEX...SHRINK SPACE statement to free up unused space in an index—for example:

SQL> alter index f_regs_idx1 shrink space;

Now query

USER_SEGMENTS

again to verify that the space has been de-allocated. Here is the output forthis example:

BYTES----------
524288

The space consumed by the index has considerably decreased.

I prefer export and import is the another useful method to reclaim free space.

IMP-00038: Could not convert to environment character set's handle

Check character set of Database which export dump file has been already taken.

You check the character set with below query.

select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');

Also check the character set of DB which needs to be imported.

Check whether the characterset conversion is possible or not.

Sometimes a corrupted dumpfile cause this error.Suppose if you are trying to import using IMP from a EXPDP(Data pump Export)dumpfile Cause this error .

Cheers.

EXEC DBMS_WM.REMOVEWORKSPACE('WORKSPACE_NAME');

ora-04030 out of process memory while trying to allocate 'N' bytes.


Delete all the save points from workspace.We can see the save points from WMSYS.WM$WORKSPACE_SAVEPOINTS_TABLE.Use below query to remove save points from worskspace .


DBMS_WM.DeleteSavepoint(
workspace IN VARCHAR2,
savepoint_name IN VARCHAR2,
compress_view_wo_overwrite IN BOOLEAN DEFAULT FALSE,
auto_commit IN BOOLEAN DEFAULT TRUE,
commit_in_batches IN BOOLEAN DEFAULT FALSE,
batch_size IN VARCHAR2 DEFAULT 'PRIMARY_KEY_RANGE');


After the successful removal of save points,You can remove workspace simply by

EXEC DBMS_WM.REMOVEWORKSPACE('WORKSPACE_NAME');


Cheers!!!

Forcefull Disable Version on Versioned Table.

You can forcefully Disable Version tables that have already modified in non Live Workspace in Oracle.But all the changes made by the non Live workspace will be neglected.

SQL> EXEC DBMS_WM.DISABLEVERSIONING('TABLE_NAME',TRUE);

ORA-16179: incremental changes

ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE


You can simply avoid this error by cross checking your command once again.

Use your command like this

SQL>ALTER SYSTEM SET log_archive_dest_2 = 'LOCATION=/OPT/ARC' SCOPE=SPFILE;



Cheers!!!