Dicas ORACLE DBA

Using Virtual Columns on 11g: Indexes defined against virtual columns are equivalent to function-based indexes. Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML. Tables containing virtual columns can still be eligible for result caching. Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled: Constraint on the virtual column must be disabled and re-enabled. Indexes on the virtual column must be rebuilt. Materialized views that access the virtual column must be fully refreshed. The result cache must be flushed if cached queries have accessed the virtual column. Table statistics must be regathered. Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables. The expression use

CONN DENILSON/DENILSON@ORCL SQL> CREATE TABLE teste(x number); Table created SQL> CREATE UNIQUE INDEX ix_teste ON teste(x); Index created SQL> begin 2 for i in 1..20 3 loop 4 insert into teste(x) values(i); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed SQL> COMMIT; Commit complete CONN DENILSON/DENILSON@ORCL2 SQL> CREATE TABLE teste(x number); Table created SQL> CREATE UNIQUE INDEX ix_teste ON teste(x); Index created SQL> begin 2 for i in 1..20 3 loop 4 insert into teste(x) values(i); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed SQL> COMMIT; Commit complete SQL> DELETE FROM teste WHERE x = 10; 1 row deleted SQL> COMMIT; Commit complete CONN DENILSON/DENILSON@ORCL SQL> ALTER SESSION SET max_dump_file_size=unlimited; Session altered. SQL> ALTER SESSION SET timed_statistics = true; Session altered. SQL> ALTER SESSION SET statistics_level = ALL ; Session altered. SQL>

To identify segment, then to the specified size: SELECT DISTINCT owner, segment_name, segment_type, tablespace_name, file_id FROM dba_extents WHERE ((block_id+1)*(SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) > (3000*1024*1024) AND tablespace_name= 'SYSAUX' ORDER BY file_id, owner, segment_name, segment_type;

The DRIVING_SITE hint instructs the CBO to execute the query at a different site than that selected by the database. This hint is useful if you are using distributed query optimization. For example: SELECT /*+ DRIVING_SITE(tab2) */ * FROM tab1, tab2@remote WHERE tab.id = tab2.id; If this query is executed without the hint, then rows from tab1 are sent to the local site, and the join is executed there. With the hint, the rows from tab2 are sent to the remote site, and the query is executed there and the result set is returned to the local site.

Using Extended Statistics to Optimize Multi-Column Relationships and Function-Based Statistics

Configure an Application Express Application as a Partner Application in Oracle AS Single Sign-On