Oracle OBIEE passing middle-tier user to database

When querying using OBIEE to Oracle database the connection pool username is passed to the database. To also pass the middle-tier user name you need to set the user identifier on the session. TO do this in OBIEE, open the RPD, edit the connection pool settings and create a new connection script to run at connect time.

Add the following line to the connect script -

call dbms_session.set_identifier('VALUEOF(NQ_SESSION.USER)')

This will then be available as the CLIENT_ID attribute when querying session history and also be available in the audit trail on queries.
EG. Select sid, client_identifier from v$session

Using SSH to forward ports

Ports on remote machines can be forwarded over a SSH tunnel using the ssh command line tool. Using the ssh config file it is possible to set up many ports to be forwarded at the same time running one command. Putty can be used to achieve similar results on Windows.

To forward a port the syntax is the ssh command followed by the local port then the local host then remote port and host
ssh -L 8080:me.local.org:80 myuser@s1.remote.org

To forward many ports at once created a config file in ~/.ssh/config as follows

host localme
  HostName s1.remote.org
  User myuser
  LocalForward 8080 me.local.org 80
  LocalForward 8443 me.local.org 443

Then run ssh localme and give the password for myuser

Oracle ORA-02082 creating database link

Creating a database link in oracle gives the error -
create database link orcl connect to myuser identified by oracle using 'orcl';


SQL Error: ORA-02082: a loopback database link must have a connection qualifier
02082. 00000 -  "a loopback database link must have a connection qualifier"
*Cause:    attempt to create a database link with the same name as the
           current database.
*Action:   a loopback database link needs a trailing qualifier, e.g.
           MYDB.DEV.US.ORACLE.COM@INST1  - the '@INST1' is the qualifier

To fix use
create database link orcl@loopback connect to myuser identified by oracle using 'orcl';

Querying Oracle for Fine Grained Auditing settings

An earlier post about Fine Grained Auditing shows how to enable FGA in an Oracle 11gR2 database - http://notastrophe.blogspot.com/2013/01/oracle-11gr2-fine-grained-auditing-fga.html . But how do you tell what FGA policies are applied. Typically to query audit settings you query DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS, and these are shown in OEM's audit page and can be queried from there, but FGA requires a different query -

SELECT PNAME, PCOL,  FROM SYS.FGA$;

Gives the policy name and affected columns.

SELECT DBUID, CLIENTID, POLICYNAME FROM SYS.FGA_LOG$;

Gives audit trail of user, client id and audited policy. Can also get query details and object schema and name

Query against the regular audit log can be done against DBA_AUDIT_TRAIL and SYS.AUD$

Oracle Using Query Rewrite for Data Warehouses

Oracle can use computed aggregrates and joins from materialized views to improve performance on a database query by rewriting a query against the warehouse to make use of the view. To allow this to happen the view must support query rewrite and the warehouse itself must support query rewrite. Be aware that the materialized view must be fresh, or the related integrity session parameters relaxed to allow the rewrite to happen.

To enable query rewrite on the view do
CREATE MATERIALIZED VIEW my_view_mv
ENABLE QUERY REWRITE AS
SELECT .....

To enable query rewrite ensure QUERY_REWRITE_ENABLED = TRUE for the session or the system.

Basic query rewrite documentation for Oracle 11g R2
http://docs.oracle.com/cd/E11882_01/server.112/e25554/qrbasic.htm
Advanced query rewrite documentation for Oracle 11g R2
http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm

Make sure the warehouse supports the creation of the materialized view -
http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm

Oracle Audit Vault Database Firewall 12.1 Errors with network card on Oracle Virtual Machine

To get round a problem with Oracle Audit Vault Database Firewall appliance not finding the network interface after completing the initial configuration it is necessary to change the type of network of the NIC. The problem is seen when AVDF boots for the first time after the install, a screen is shown for selecting a management interface, even though this has been configured already, the NIC hardware address shows as 00:00:00:00:00:00 instead of the valid address. Dropping to a console and searching for the hardware device shows no Ethernet controller. If there are multiple NICs defined they will all be listed as 00:00:00:00:00:00.

For how to debug audit vault in the console look at http://notastrophe.blogspot.com/2013/06/oracle-audit-vault-database-firewall.html .
The command lspci | grep -i Ether xx shows no results, and ifconfig -a shows eth0 with HWADDR of 00:00:00:00:00:00

To get past this problem its necessary to use Fully Virtualized network configuration in OVM for the initial install. Then after the first reboot (just after the IP Config screen), shutdown the VM and change to Paravirtualized networking. Trying to do the install with Paravirtualized networking will fail because the install detects no interfaces. A Fully Virtualized configuration shows on OVM 2.0 as "ioemu" in the Network Interface column on the Network configuration page, Paravirtualized configuration shows as "netfront".

Oracle Audit Vault Database Firewall 12.1 run-level hot key

During Oracle Audit Vault Database Firewall 12.1 install you can change from the default interface to view the console (and any error messages) by using a hot-key and another key to get the interface back. From the console you can quit the running service to get to the management interface.
This helps a lot to debug Oracle AVDF install problems or errors.


To change from the interface to the console hit alt+F9
To revert hit alt+F1.
To skip over services that are hung hit ctrl+c from the console.

Once the management interface is up you can drop to a login using ctrl+alt+f2.
Login is root or support. Make sure you've set root and support passwords from the management interface first

Automatic Start and Stop of Oracle on LINUX

Here's a script to do automatic start and stop of an Oracle instance running on Linux. Note the change of startup and shutdown for 11gR2. I'm sticking with the dbstart and dbshut method until its removed

http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php

Guest Additions on Oracle VirtualBox

To get guest additions working in VirtualBox hosting Oracle Linux 5 update 8 its necessary to download the kernel-uek-devel and dependent packages. Make sure yum is installed and the repository is downloaded then run yum install kernel-uek-devel.

Kernel Panic - Fatal Exception

After installing Oracle Linux 5 Update 8 in VirtualBox everything worked fine until updating packages. After updating packages and rebooting I got the error message below. To fix the problem I had to change a setting in VirtualBox.

The Error:
Kernel Panic - Fatal Exception

The Fix:
In VirtualBox go to Machine -> Settings -> System.
Then Change chipset to ICH9 and check Enable IO APIC checkbox.

Simple HTTP server in Python

To start a very simple HTTP server on a LINUX machine use Python. Change to the directory that contains files to be served then run the command below. Change the port number as required.

python -m SimpleHTTPServer 8080

Specifying comments in SQL to support auditing in Oracle

Adding comments to an SQL statement in Oracle is a good way of providing context to auditing and sql forensic activities directed against an Oracle database audit log. The Oracle Audit trail, and related software like Oracle Audit Vault will capture any comments embedded within an sql statement as part of its normal activities. This, then, provides a way for applications built on Oracle to provide metadata that can assist in analytics performed on the audit record.

As an example, an online shopping cart may audit searches by keyword and may audit individual product pages. At the database level the sql run to generate such pages can be marked with a comment and a textual description of the functionality of the application that uses such a query. This permits application level auditing to be performed in the database, and not require that the auditor is educated in the full set of statements that may be performed by the application. This method of auditing can be done in addition to application level auditing functionality.

Comments are also useful in the enterprise manager to look up statements that are candidates for automated sql tuning.

To add comments to the statement use the following syntax -

SELECT /* [text] */ .....

When the statements require the use of hints ensure the comments do not get in the way of the correct hint definition. Ensure the comments are of the form


{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint [text]]... */

Finding Day, Month, Year from a date in Oracle SQL using EXTRACT

In Oracle, to get the year, month, day or time from a date its not necessary to parse the date string using the SUBSTR function. Simply use the EXTRACT function and pass in the relevant keyword to get the value as in the following example:

SELECT EXTRACT (year FROM sysdate) "Year" FROM DUAL;

Other keywords that can be passed in are YEAR, MONTH, DAY, HOUR., MINUTE, SECOND. There are also timezone keywords that can be used with timestamps.

The function is useful when used with GROUP BY. For Example:

GROUP BY EXTRACT(month FROM start_date)

Setting the value of a sequence in pl/sql in Oracle

In Oracle there is no direct way to set the value of a sequence once the sequence has been created. To set the sequence value to a specific value it is necessary to either drop and recreate the sequence, not forgetting all of the options specified in the create statement, or to increment the sequence by some positive or negative value to set the current value to the desired value.

To find out the current value of a sequence you can use the currval function on the sequence, but this will only return successfully if the sequence has already been initialized with a call to nextval. Another way to find the value for the sequence is to query the database as follows:

select sequence_name, last_number from user_sequences;

Below is sample pl/sql to set a sequence to the desired value, it defines two variables to store the current sequence and the desired sequence then alters the increment to the difference of those, increments the sequence then resets the sequence increment to the previous value. -


  procedure set_seq is
    l_max_row NUMBER(38,0) := 0;
    l_cur_seq NUMBER(38,0) := 0;
  begin

  --- Now change the sequence
   select max(id_col) into l_max_row from my_tab;
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by ' || (l_max_row-l_cur_seq) || ' nocache';
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by 1 nocache';
   select my_seq.nextval into l_cur_seq from dual;

Oracle PL/SQL Merge with sequence

In Oracle 11g it is not possible to use a sequence in the USING clause of a MERGE statement. To get around this limitation enter a dummy integer in the USING clause and specify the sequence in the VALUES clause.

Putting Sequence in the subquery following USING in a MERGE statement gives an error
-- Eg>

MERGE INTO mytab tab
USING (SELECT 1 as col1,  myseq.nextval seq from dual) q1
ON (tab.col1 = q1.col1)  -- The ON clause will take ANDs eg. ON (t1.c1 = t2.c1 AND t1.c2=t2.c2)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

Gives

Error report:
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Use a dummy value eg -999 instead

MERGE INTO mytab tab
USING (SELECT 1 as col1, -999 seq from dual) q1
ON (tab.col1 = q1.col1)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

Oracle 11G Shrinking Temp Tablespace

There are a few methods to shrink the temporary tablespace in Oracle 11G. In some cases you can directly resize or shrink the tablespace. If that is not possible then create a new temporary tablespace, reassign the default temp space and drop the old tablespace.


SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';


alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M;
alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M

-- Can give -
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- If that fails, on 11G do -
alter tablespace TEMP shrink space keep 256M;

-- If that fails do

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/datapath/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/datapath/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

From http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle

Disable Password expiry for Oracle Accounts

To disable password expiry for Oracle accounts find whihc profile is in use and then change the default timeout on the profile. If the users are already expired and locked the password must be reset and the account unlocked.


select * from dba_users; -- find the users and which profile they use

-- change the DEFAULT profile to not expire
ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME UNLIMITED;

-- could also add FAILED_LOGIN_ATTEMPTS UNLIMITED

Oracle PL/SQL Associative Arrays using %ROWTYPE

Creating an Associative Array in Oracle PL/SQL from a table using %ROWTYPE
This example creates an in memory table using the row type from another table then populates the tbale in batches of 1000. This allows operations to be done on the data before inserting it into a third table.

  procedure my_proc
  is
    TYPE tt_table IS TABLE OF mytable%ROWTYPE;
    l_id      NUMBER(38,0)  := -1;
    l_tab tt_table;
        cursor c_data is
      select * from mytable;
  begin
    open c_data;
    loop
      fetch c_data
      bulk collect into l_tab limit 1000;
      for i in 1 .. l_tab.count
      loop
        insert into other_tab (COL1)
        VALUES (l_tab(i).MYCOL1);
   -- Do other conditional processing here (otherwise we could just insert directly from a query)
      end loop;
      exit when l_tab.count = 0;
    end loop;
    close c_data;
  end;

Also see the example of using Associative Arrays at  - http://notastrophe.blogspot.com/2013/03/oracle-plsql-associative-arrays.html

Oracle PL/SQL Convert BOOLEAN to STRING

In Oracle pl/sql there is no direct method to convert a boolean type to a character type. It is necessary to test the boolean with a case statement as below:

l_varchar := case l_bool when TRUE then 'TRUE' else 'FALSE' end;

ORACLE PL/SQL Associative Arrays

An example of an Associative Array in Oracle 11g.
Associative arrays give you the ability to create in memory tables of a given datatype and iterate over them. This example shows the declaration of a table of character data which is populated from a select statement on an Oracle table.



-- Assign values to an empty associative array
procedure myproc
is
  TYPE tt_vals IS TABLE OF VARCHAR2(80);

    l_vals  tt_vals;
   l_count  NUMBER := 1;
begin
  l_vals := tt_vals(10);  -- l_vals.COUNT is 1 here and l_vals.EXISTS(1) is TRUE
  for mychar in (
    select CHAR_COL from mytab;
 )
LOOP
  l_vals.extend;
  l_vals(l_count) := mychar;
  l_count := l_count+1;
END LOOP;


----

l_vals(1) gives the value of the first element in the array, l_vals(2) the second....
l_vals.exists(N) evaluates to TRUE if the Nth value exists, FALSE otherwise
l_vals.count gives count of elements in the array

Other Collection Methods for the Associative Array are -
LIMIT,
FIRST and LAST,
PRIOR and NEXT for looping,
TRIM,
DELETE.

See Oracle PL/SQL Language Reference (pdf)

Also - http://notastrophe.blogspot.com/2013/03/oracle-plsql-associative-arrays-using.html

OBIEE Inconsistent Datatype Error

Using Bins in Oracle OBIEE I was getting ORA-00932: inconsistent datatypes: expected CHAR got NUMBER error.

The error occurred when the datatype of the column was Number, but I had a mix of String and Number in the Bin Name.

Eg.

CASE WHEN <TABLE.COLUMN> IS BETWEEN 30 and 39 THEN '30-39'
WHEN <TABLE.COLUMN> IS BETWEEN 40 and 49 THEN '40-49'
.....
ELSE
<TABLE.COLUMN>
END.

Becomes

CASE WHEN <TABLE.COLUMN> IS BETWEEN 30 and 39 THEN '30-39'
WHEN <TABLE.COLUMN> IS BETWEEN 40 and 49 THEN '40-49'
.....
ELSE
'Other'
END

Mapping Groups in Active Directory to Oracle OBIEE and Weblogic

Mapping Groups in Active Directory to Weblogic
http://www.rittmanmead.com/2012/03/obiee-11g-security-week-connecting-to-active-directory-and-obtaining-group-membership-from-database-tables/

Oracle OBIEE 11.1.1.6 Start/Stop

To start and stop the Oracle OBIEE server do the following:


Start the NodeManager -
<MW_HOME>/wlserver_10.3/server/bin/startNodeManager.sh
Stop the NodeManager
Use WLST Scripting Tool
MW_HOME/Oracle_BI1/common/bin/wlst.sh
wls:/nm/base_domain> stopNodeManager()
Stopped Node Manager Process successfully
wls:/offline>exit()
Or kill -9 <nodemanager pid>

Start the Administration Server -
MW_HOME/user_projects/domains/bifoundation_domain/bin/startWebLogic.sh
Stop the Administration Server -
MW_HOME/user_projects/domains/bifoundation_domain/bin/stopWebLogic.sh

Start the Managed Server -
MW_HOME/user_projects/domains/bifoundation_domain/bin/startManagedWebLogic.sh bi_server1 http://<admin_server>:7001
or via EM or via WLST Scripting tool
Stop the Managed Server -
MW_HOME/user_projects/domains/bifoundation_domain/bin/stopManagedWebLogic.sh bi_server1 http://<admin_server>:7001
or EM or WLST

Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition
http://docs.oracle.com/cd/E28271_01/bi.1111/e10541.pdf

Oracle 11gR2 Audit Trail Cleanup scripts

Initialise the Cleanup -
Do this once.
Watch out for tablespace problems in SYSAUX tablespace. Even if SYSAUX has auto extend ON

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
DEFAULT_CLEANUP_INTERVAL => 12 );
END;
/

Set a clean_up batch size

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
AUDIT_TRAIL_PROPERTY_VALUE => 100000);
END;
/

Run a manual audit trail cleanup
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Schedule a purge job

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

purge interval is in hours

Oracle 11gR2 Disabling Fast Recovery Area (FRA)

Disabling the Fast Recovery Area

If Flashback Database is enabled, then disable it before you disable the fast recovery area.
ALTER DATABASE FLASHBACK OFF;
If you are using fast recovery area for archive logs, then set the initialization parameter LOG_ARCHIVE_DEST_n to use a non-fast recovery area location.
eg.
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/ORACLE/DBS/';

Disable the fast recovery area initialization parameter.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';

(From Oracle Database Backup and Recovery User's Guide)

Oracle ORA-00904 "ORA_ROWSCN": invalid identifier

Oracle ORA-00904 "ORA_ROWSCN": invalid identifier occurs when querying a table with Fine Grained Auditing (FGA) Policy defined using sqldeveloper.

The fix is to go to preferences -> database -> Object Viewer and deselect the checkbox for use ROWSCN

Oracle 11gR2 Fine Grained Auditing (FGA) Policy

Example of adding a FGA policy to Oracle 11gR2 that will be collected by Audit Vault. This will create a policy on the SCHEMA.SCHEMA_TBL table called "QRY_TBL" for any select statement on COL1. The audited event will be written to the extended DB audit trail. This will provide the full sql command run, the column that triggered the event, various session data and will include any bind variable values used in the query.

Multiple columns and statement types can be provided in one statement. It is usual to have one policy statement to cover all columns that need to be audited on a table. The audit trail will show details of which columns caused the event to be audited.

BEGIN

  DBMS_FGA.ADD_POLICY (object_schema=>'"SCHEMA"',      object_name=>'"SCHEMA_TBL"',                policy_name=>'QRY_TBL’,

                audit_condition=>NULL,

                statement_types=>'SELECT', audit_column=>'COL1',

                audit_column_opts=>DBMS_FGA.ANY_COLUMNS,

                audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED   );

END;

Oracle Audit Vault AV Auditor Data Warehouse Tables

The following are the useful tables in Oracle Audit Vault Data Warehouse


avsys.audit_event_fact;

This is the centre of the star schema, containing the keys to other tables, and time and data information.

avsys.audit_option_tab;
avsys.authentication_method_tab;
avsys.client_host_dim;

This is the full list of client hosts

avsys.client_id_tab;

This olds all the client ids

avsys.client_tool_dim;
avsys.comment_text_tab;
avsys.context_dim;
avsys.event_dim;
avsys.event_status_tab;
avsys.fga_policyname_tab; -- contains fine grained auditing info
avsys.privileges_dim;
avsys.session_actions_tab;
avsys.source_dim;
avsys.target_dim;
avsys.time_dim;
avsys.transaction_name_t;
avsys.user_dim;

AUDIT CONNECT creates CREATE SESSION audited privilege

The query to find out if auditing of succesful or unsuccessful logins is enabled and to find out what login activity has occured on an Oracle database is below. It queries against the dba_priv_audit_opts table for use of the "CREATE SESSION" privilege.

To audit successful and failed logins run
AUDIT CONNECT BY ACCESS

To find if this is already enabled query
SELECT * FROM DBA_PRIV_AUDIT_OPTS WHERE PRIVILEGE = 'CREATE SESSION'

USER_NAME        PROXY_NAME        PRIVILEGE     SUCCESS        FAILURE
        |          | CREATE SESSION        |    BY ACCESS        | BY ACCESS

Oracle Audit Vault General Commands

Here are useful commands when using Oracle Audit Vault. Commands are listed for the collector to show the status and to start and stop the agent and there are commands for the server side to add and agent, start and stop the agent and also start and stop the collectors.

On a test system where collectors are created and removed often, sometimes it is necessary to redeploy using the commands listed.

On the Collector:
avctl show_agent_status
avctl start_agent
avctl stop_agent


On the Server:
avca add_agent -agentname avagent1 -agenthost <host>

avctl start_agent - starts the web server running on <host>:1158/av
avctl stop_agent - stops the web server running on <host>:1158/av
avctl show_av_status - shows if the web server at <host>:1158/av is running

avctl show_collector_status -collname DBAUD_Collector -srcname db1001
avctl start_collector -collname DBAUD_Collector -srcname db1001

avctl stop_collector -collname DBAUD_Collector -srcname db1001

If the avctl or avca commands return internal errors it may be necessary to redeploy the av using the deploy_av command.

Shrinking UNDO Tablespace in Oracle 11gR2

To shrink UNDO tablespace in Oracle first create the new undo tablespace, then alter the database to use the new undo tablespace. This will require enough available disk space to create the new tablspace. Then drop the old tablespace to free up the disk. Commands are provided below

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u02/datafile/undotbs02.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 50M;

Tablespace created.



ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;