DBMS_UTILITY Oracle provides two procedures under the DBMS_UTILITY package related to statistics generation. (Oracle recommends use of DBMS_STATS package for generating statistics). DBMS_UTILITY.ANALYZE_SCHEMA This routine will generate statistics on an individual schema level. It is used for analyzing all tables, clusters and indexes. It takes the following parameters: schema – Name of the schema method…
10:17:18 SYS@WEBP:-> execute dbms_utility.analyze_schema(‘CS’, ‘COMPUTE’, NULL, NULL, NULL);
———————————————- Gathering statistics with DBMS_STATS ———————————————- DBMS_STATS.GATHER_TABLE_STATS DBMS_STATS.GATHER_TABLE_STATS gathers statistics for a table and its columns, and optionally the associated indexes. Call syntax dbms_stats.gather_table_stats(ownname, tabname, partname, estimate_percent, block_sample, method_opt, degree, granularity, cascade, stattab, statid, statown); The first two parameters are mandatory, the rest are defaulted to a value. ownname – owner tabname – table name…
create or replace package subs_cipher is function scramble ( obfs_in varchar2 ) return varchar2; — pragma not required as of 8.1.5 pragma restrict_references( scramble, wnds, wnps, wnps); function unscramble ( obfs_in varchar2 ) return varchar2; — pragma not required as of 8.1.5 pragma restrict_references( unscramble, wnds, rnds, wnps ); end; create or replace package body…
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database: CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING ‘local’;
POTENTIAL SOLUTION(S) for AWR Snapshot Error ============================================ (*) To take the snapshots we need to populate WRM$_SNAPSHOT table successfully and for that instance metadata should be present in WRM$_DATABASE_INSTANCE. (*) To regenerate the AWR objects run catnoawr.sql and catawr.sql scripts from $ORACLE_HOME/rdbms/admin (*) Then RESTART the database So, if we have regenerated the AWR objects…
Changing the Global Database Name: Example The following statement changes the global name of the database and includes both the database name and domain: ALTER DATABASE RENAME GLOBAL_NAME TO demo.world.oracle.com;
/************************ wait.sql *************************/ set pages 2000 set lines 132 column p1text format a18 column event format a35 column WT format 9999 column SW format 9999 select sid, event,p1text,p1raw,p1,p2,p3,seconds_in_Wait “SW”,Wait_time “WT” from v$session_wait where event not in( ‘KXFQ: Dequeue Range Keys – Slave’, ‘KXFQ: Dequeuing samples’, ‘KXFQ: kxfqcls – consumer closing TQ’, ‘KXFQ: kxfqdeq – dequeue…
The Mutating table error is a well-known problem encountered in development; most developers have come across this error. ORA-04091: table is mutating, trigger/function may not see it The basic reason for this error is the way that Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the…
Read More “Mutating Table Error while using database trigger” »
When you are using tablespace with segment space management AUTO then following command will not show FREELISTS. Alter table move tablespace my_data storage (initial 20M freelists 9) initrans 9; If tablespace is crreated with segment space management AUTO Then USER_TABLES.FREELISTS does not show FREELISTs as 9, but it displays blank in FREELISTS column.
