Category: SQL | PLSQL

Database, SQL | PLSQL

Oracle Top Growing Segments

This is the second post of a serie. First one here.

Now that you already have an idea regarding the size of the database top segments (first post), you might want to check the top growing segments, considering a specified number of days back.

You probably used some AWR information in past or generated an AWR report, at least. But if this is still new to you, AWR stands for Automatic Workload Repository. AWR is a built-in repository, used to collect, process, and maintain performance statistics for problem detection and self-tuning purposes. This gathered data is stored both in memory and in the database, and is displayed in both reports and views.

For additional information, you can check this official doc here.

We are going to use some AWR views:
– dba_hist_seg_stat: historical information captured from V$SEGSTAT about segment-level statistics.
– dba_hist_seg_stat_obj: names of the segments captured in the workload repository.

More“Oracle Top Growing Segments”

Database, SQL | PLSQL

Disable/Enable Maintenance Jobs

Hi all!
A couple days ago a client asked me assistance to disable all the maintenance jobs on DB to run a critical process.

First considerations: We could just disable window changing, once the jobs are related to windows, and Resource Manager Plans use to be as well. However, to answer the question directly:

# To disable/enable all maintenance jobs in from/for all windows:

EXEC DBMS_AUTO_TASK_ADMIN.disable;
EXEC DBMS_AUTO_TASK_ADMIN.enable;

# And to disable/enable specific maintenance jobs from/for all windows:

exec DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO OPTIMIZER STATS COLLECTION',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE('SQL TUNING ADVISOR', NULL, NULL);

exec DBMS_AUTO_TASK_ADMIN.ENABLE('AUTO OPTIMIZER STATS COLLECTION',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.ENABLE('AUTO SPACE ADVISOR',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.ENABLE('SQL TUNING ADVISOR', NULL, NULL);

More information and details about it can be taken from here: https://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN11836

Hope it helps. Cheers!

SQL | PLSQL

PL/SQL Generalized Invocation

 , , ,

Did you know we have that since 11g? The generalized invocation allows a subtype to invoke a method of a parent type (supertype) using the following syntax:

(SELF AS supertype_name).method_name

Check the example below to understand it. First, creating original type:

CREATE OR REPLACE TYPE type_test AS OBJECT (MEMBER FUNCTION return_text RETURN VARCHAR2) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY type_test AS
MEMBER FUNCTION return_text RETURN VARCHAR2 IS
BEGIN RETURN 'This is the original text.'; END;
END;
/

And now creating a subtype of this object, which adds a new attribute and method as well as overriding the member’s function.

CREATE OR REPLACE TYPE subtype_test UNDER type_test (OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY subtype_test AS
OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2 IS
BEGIN RETURN (self AS type_test).return_text || ' This is an additional subtype text.'; END;
END;
/

And when calling:

SET SERVEROUTPUT ON
DECLARE
my_subtype subtype_test;
BEGIN
DBMS_OUTPUT.put_line(my_subtype.show_attributes);
END;
/
This is the original text. This is an additional subtype text.

A type can invoke the member functions of any parent type in this way, regardless of the depth of the inheritance.

Pretty nice, right?
Cheers!

Database, SQL | PLSQL

PLSQL: Working with NULLs and Function NHT_VALUE

 , ,

Did you know this function?

NTH_VALUE returns the result of expression on value of the nth row in the window defined by the analytic clause. The returned value has the data type of the expression. RESPECT NULLS or IGNORE clauses can be used to determine whether null values of expression are included in or eliminated from the calculation. The default is RESPECT NULLS. Also clauses FROM FIRST or FROM LAST determines whether the calculation begins at the first or last row of the window. The default is FROM FIRST.

The function returns NULL if the data source window has fewer than n rows. If n is null, then the function returns an error.

Example:

SELECT prod_id, channel_id, MIN(amount_sold),
    NTH_VALUE(MIN(amount_sold), 2) OVER (PARTITION BY prod_id ORDER BY channel_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
  FROM sales
  WHERE prod_id BETWEEN 13 and 16
  GROUP BY prod_id, channel_id;

   PROD_ID CHANNEL_ID MIN(AMOUNT_SOLD)         NV
---------- ---------- ---------------- ----------
        13          2           907.34      906.2
        13          3            906.2      906.2
        13          4           842.21      906.2
        14          2          1015.94    1036.72
        14          3          1036.72    1036.72
        14          4           935.79    1036.72
        15          2           871.19     871.19
        15          3           871.19     871.19
        15          4           871.19     871.19
        16          2           266.84     266.84
        16          3           266.84     266.84
        16          4           266.84     266.84
        16          9            11.99     266.84

 

For more information and examples, check: https://docs.oracle.com/cloud/latest/db112/SQLRF/functions114.htm#SQLRF30031

Cheers!

Database, SQL | PLSQL

Identifying the top segments

 , ,

Hello readers! My name is Bruno Kremer, this is my first post from a series, and I will be talking about how we can identify the top segments of the database.

Introduction

It’s well known that we can create automated tasks to collect and save the space used/allocated by the database objects, such as saving snapshots of the DBA_SEGMENTS view. But what if this is your first contact with a specific database and you need to identify the top segments, estimate their growth ratio, check the history of space allocated, or even to perform some kind of capacity planning? There are some alternatives to answer these questions, but on this post I will share the starting point. Please feel free to customize the scripts to your own need.

Checking the top sized segments


select
s.owner,
s.segment_name,
s.segment_type,
round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
fetch first &TOP rows only;

Input values: &TOP – limit the number of rows returned.

Filters you might want to use: and s.segment_type in ('&OBJECT_TYPE') – ‘TABLE’, ‘TABLE PARTITION’, ‘INDEX’…

Return example:

Note: the scripts used in this series were tested on 12.1.0.2 databases. Some of these use the “FETCH FIRST” clause to limit the number of rows returned, but if you are using older versions of Oracle Database, you can still use the old fashion like “ROWNUM”.

Example:


select * from (
select s.owner, s.segment_name, s.segment_type, round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
) where rownum <= &TOP;

Now that you already have an idea regarding the size of the largest database segments, you might want to check the top growing segments… On next publications we will talk about how we can use AWR data dictionary views and some DBMS_SPACE procedures to estimate space usage history and top growing segments.

Database, SQL | PLSQL

WHEN OTHERS -> NULL: Hidding your PLSQL Errors?

 , , ,

Are you using WHEN OTHERS -> NULL to hide your PLSQL errors?
Don’t be so sure…

WHEN OTHERS exception handlers that do nothing and don’t raise errors using RAISE or RAISE_APPLICATION_ERROR can often hide code failures that result in hard to identify bugs.

To avoid this, a new PL/SQL compiler warning was added in 11g to identify those kind of situations. Check example below:

SQL> ALTER SESSION SET plsql_warnings = 'enable:all';

Session altered.

SQL> CREATE OR REPLACE PROCEDURE warning_test AS
  2  BEGIN
  3    RAISE_APPLICATION_ERROR(-20000, 'This is an Exception!');
  4  EXCEPTION
  5    WHEN OTHERS THEN
  6      NULL;
  7  END;
  8  /

SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE OTHERS_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8      PLW-06009: procedure "WARNING_TEST" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR

Nice, right?!

There are also some other warnings improvements like:

  • New NO_DATA_NEEDED Predefined Exception: ORA-06548: For parallel access and pipelined table functions the caller of a pipelined function does not need more rows to be produced by the pipelined function.

Warnings:

  • Severe
    – 5018 – omitted optional AUTHID clause
    – 5018 – omitted optional AUTHID clause
    – 5019 – deprecated language element
    – 5020 – parameter name must be identified
  • Informative
    – 6016 – native code generation turned off (size/time)
    – 6017 – operation will raise an exception
    – 6018 – an infinity or NaN value computed or used

Cheers!

Database, Performance, SQL | PLSQL

Cross-Session PL/SQL Function Result Cache

 , , , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Cross-Session PL/SQL Function Result Cache

Since 11gR1 we have simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA:The cross-session PL/SQL function result cache.

The results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in a SQL query, or within a loop in PL/SQL.

Ok, but how to do this? It’s as simple as adding the RESULT_CACHE clause:

CREATE OR REPLACE FUNCTION procedure_example (p_in IN NUMBER)
  RETURN NUMBER
  RESULT_CACHE

The RELIES_ON may be set in 11gR1 but is unnecessary in 11.2 as it automatically tracks dependencies and invalidates the cached results when necessary.

Nice, right?
Cheers!

Database, SQL | PLSQL

Moving Audit Trails (AUD$ and FGA_LOG$) to a Different Tablespace

 , , , ,

Hi all!
There is one thing that is always true for any version  of the Oracle Database: the audit trail simply keeps growing and growing.

Some important facts in this regard:
– It’s based on activity, not the amount of data changed.
– The default location of the database audit trail is the AUD$ table, in the SYSTEM tablespace.
– As you know, If this tablespace gets filled up there could be serious consequences.
– The same concern is true for FGA_LOG$, which is located in the SYSAUX tablespace.

Ok, so if you could move these two tables  to specific tablespaces, the root problem would be solved, right?

From 11g R2 on you can move the some or all audit trails to other tablespaces you choose. How?

To move the AUD$:

begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDIT_TBS');
end;
/

To move the FGA_LOG$:

begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,
audit_trail_location_value => 'AUDIT_TBS');
end;
/

 

There is only one important thing to keep in mind:  This tablespace needs to be online when the database is open. Otherwise, if any auditable operation try to be executed, you’ll get an ORA-02002.

This is something recommendable if you are setting the Auditing for your database. This is also something included Oracle in scripts like the Exacheck.

Hope it helps!
Cheers!

Database, Errors and Bugs, SQL | PLSQL

ORA-02158: invalid CREATE INDEX option while using redef_table on Oracle 12cR1

Hey all,
I’m working on a table redefinition project to migrate the existing tables and indexes to new compressed tablespaces. As the customer asked to have near 0 downtime to its data we decided to go with DBMS_REDEFINITION.

Simple right? Well… I sure hoped so.
I’m preparing a serie of posts about it, but before that I would like to share some hands on issues and that the magic of the new redef_table is not that great yet, at least on 12cR1/12.1.0.2.
Prior 12c, when would need to redefine a table you would use the DBMS_REDEFINITION and its 6 steps:

0 – Manually create a interim table to receive the data with the same structure as the original table
1 – DBMS_REDEFINITION.can_redef_table
2 – DBMS_REDEFINITION.start_redef_table
3 – DBMS_REDEFINITION.sync_interim_table
4 – DBMS_REDEFINITION.copy_table_dependents
5 – DBMS_REDEFINITION.finish_redef_table

And sometimes you would need to user the DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT to help on some issues but if everything was good you would only need to do the steps above.

There are a few issues with the approach by that I mean BUGS 🙂 so you need to watch your back do an a good prep work.

In 12c you have a new procedure called DBMS_REDEFINITION.redef_table that would bundle all the 6 steps into one single procedure call. With its up and down side.
For me, the down side is that we can’t monitor the procedure, once this is no loger anything being recorded on dba_redefinition_errors.
By working as a transaction, everything works or its rolled back (Or it should but I will leave it for another post).

So the only way to know what is being done is to trace the session that is doing the redefinition. And that what I needed to do to see what was going on with a strange situation.

This is what was happening: I 1st tried the DBMS_REDEFINITION.redef_table and it raised ORA-02158: invalid CREATE INDEX option but when I used the 6 steps mentioned above (can_redef_table,start_redef_table,etc) it worked without issues:

That bugged me so I traced the session.

SQL> exec dbms_monitor.session_trace_enable(binds=>true,waits=>true);

SQL> BEGIN DBMS_REDEFINITION.REDEF_TABLE(uname => 'USER1',
tname => 'TEST1',
table_compression_type => 'COMPRESS FOR OLTP', 
table_part_tablespace => 'DATA_COMP', 
index_tablespace => 'DATA_COMP', 
index_key_compression_type   => 'COMPRESS ADVANCED LOW', 
lob_compression_type => 'COMPRESS HIGH', 
lob_tablespace => 'DATA_COMP', 
lob_store_as => 'SECUREFILE');
END;
/
BEGIN*
ERROR at line 1:ORA-02158: invalid CREATE INDEX option
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 2

And this was the create index statement that was in the trace:

250438 PARSE ERROR #140737488293344:len=421 dep=1 uid=0 oct=9 lid=0 tim=3977494319094 err=2158250439 CREATE INDEX "USER1"."TMP$$_INDEX0" ON "USER1"."REDEF$_16752430_0" ("VISIT_NO")250440   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED LOWNOLOGGING250441   STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645250442   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1250443   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)250444   TABLESPACE "DATA_COMP"

Can you see the issue?
Well neither could I, a colleague read the trace again and found a silly bug.

Here it is The create index had this:

COMPRESS ADVANCED LOWNOLOGGING

Instead of this, it had this:

COMPRESS ADVANCED LOW NOLOGGING

A silly space was missing and was causing the entire redefinition to fail!

I could not find any reference in MOS but that was it a space prevented to use the redef_table and caused me to lose some hours on it.

Hope this save you some time on your troubleshooting and I will be posting other strange situations that I found using the redef_table on Oracle 12cR1.

See you next post!

Elisson Almeida

Database, Security, SQL | PLSQL

Oracle Security: Users with Default Passwords

 , ,

Hi all!
Did you know since Oracle Database 11g we have a way to quickly identify users with default passwords?
It’s implemented in a really simple way, with a single data dictionary view: DBA_USERS_WITH_DEFPWD

You can identify these users with the query:

select * from dba_users_with_defpwd

Example of output:

USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS

You can see SCOTT listed above, because his password is TIGER, the default one. Change it with:

SQL> alter user scott identified by tiger1;

User altered.

Now if you check the view:

SQL> select * from dba_users_with_defpwd;
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS

You don’t see SCOTT on the list anymore. It’s that simple!

Hope it helps to make you database more secure.
Cheers!