Oracle Table and Database Size

To obtain the table size on an Oracle database, the following script can be used:


SELECT lower(table_name) AS tableName
    ,tablespaceName
    ,totalRows
    ,blocks*8/1024 AS Mbytes
FROM all_tables
WHERE owner = USER
ORDER BY 1,2;

Performing the sum of the sizes of all tables allow us to get an estimate of the database size:


SELECT sum(blocks*8/1024) AS TotalMbytes
FROM all_tables
WHERE owner = USER;

The previous script may not be accurate because it actually gets the sum of the size of the tables, which can be simply viewed as the data size, since a database is more than just data on tables.

Install Oracle Database in Linux

This is a guide that will show how to install Oracle Database (version 12c at this time) in Linux (Oracle Linux 7 is used).
The following is assumed:

  • Oracle Linux (or any Oracle Database approved Linux distribution) is installed with a minimum working set, i. e. working from the command line, as it is accessible via SSH.
  • A desktop running a Linux distribution with a window manager is used and the connections are performed to the server via SSH.
To simplify things, let’s say Oracle Linux is installed using the default “Minimal Install” option.

Setting up Linux

First thing to do is to update the system using
sudo yum update -y
Then add necessary software
sudo yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh compat-libstdc++-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64 zip unzip openssh-server

Getting GUI on Server

The Oracle Database installer is graphical a Java desktop application, and thus a window manager is required. If your server already has a graphical window manager, you can skip this
yum grouplist
Will show the available options. The easiest is just
sudo yum groupinstall "Server with GUI"
Though you may only require
sudo yum groupinstall "X Window System"
Or even a more lightweight option
sudo yum install -y xorg-x11-xauth xorg-x11-apps
Additional packages installation may be required depending.

Setting up Remote Display

The next step is to prepare to server to run the graphical applications in the desktop.

Server

In the server perform the following:

  • If you use the C shell, type:
    setenv DISPLAY desktop-ip:n.n
    
  • If you use the Bourne shell, type:
    DISPLAY=desktop-ip:n.n
    export DISPLAY
    
  • If you use the Korn shell, type:
    export DISPLAY=desktop-ip:n.n
    
Replace desktop-ip with the desktop IP address and n.n with the desktop display number.screen number (usually 0:0)

If the server and the desktop are all on the same network, usually setting DISPLAY to localhost:10.0 is good enough.

Desktop

In the desktop perform the following:

xhost +
ssh -X user@server
Where server is the server IP, user is your server username and login using your credentials.

If you get a warning or error message about xauth, don’t worry about it. If you logout and login again the same way, it should have disappeared since thew first login should have fixed it.

To check if everything is working properly, after authenticated in the server from the desktop with using ssh -X, do

xclock
and the clock application should appear in the desktop screen.

Running Oracle Database Installer

The Oracle Database installer comes in a zip file, e.g. linuxx64_12201_database.zip, confirm the version and architecture is the correct one. Assuming the Oracle Database installer is ready for install, i.e. the zip file is already on the server (sftp may help on transferring the file from the desktop to the server), uncompress the file

unzip linuxx64_12201_database.zip
and then run the installer
cd database
./runInstaller 

A precondition checkup is performed by the installer and you may need to do some additional things, such as install extra software packages. It is highly recommended that you do not ignore the dependencies (maybe except the swap) as it may have prevent the database to execute correctly.
During the installation, you will be required to run some scripts manually with administration privileges.

Finishing It

After the installation you may think that opening up a browser and pointing it to “https://server-ip:5500/” may be enough, but that may not be the case.

Setting up Database Administrator User

To add a databse administrator, from the server command line, run

sqlplus /as sysdba
create user admin identified by pass;
grant dba to admin; 
conn admin/pass;

Where “admin” is the username for the administration and “pass” is the correspondent password.

Firewall

Oracle database works on specific ports, thus it is mandatory that the firewall allows them to carry traffic:

firewall-cmd --zone=public --add-port=1521/tcp --add-port=5500/tcp --add-port=5520/tcp --add-port=3938/tcp --permanent
firewall-cmd --reload

Environment Settings

Edit your .bash_profile and set the following

TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/<INSTALL_DIR>/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=<ORA_SID>; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
Where <INSTALL_DIR> is the Oracle installation directory (you can check the installation log “oracle/oraInventory/logs“) and <ORA_SID> is the Oracle SID (usually “oracle”).

Ready

Now try https://server-ip:5500/em/ where server-ip is the server IP address. It is necessary to accept the certificate and the to have Flash installed, since Enterprise Manager is a Flash application that is served under HTTPS.
If the application does not execute properly, try using Internet Explorer from Microsoft Windows to see if it is working.

Print the result of a cursor in Oracle PL/SQL

While developing Oracle PL/SQL code, sometimes one needs to debug via the old way, using DBMS_OUTPUT.PUT_LINE or inserting data into a table for a later inspection.
But when working with cursors, this is not an easy task, specially if one does not know the real output of the cursor when using dynamic SQL or simply because the cost of writing a specific output for each cursor one needs to evaluate is too high.

In order to aid this task, here’s a procedure that will print out the result of any cursor. By default, it will print the first 10 rows, but to print the entire result, just call the PRINT_CURSOR procedure with the v_maxRows = 0.

create or replace
PROCEDURE PRINT_CURSOR (
    p_refcursor IN OUT SYS_REFCURSOR, v_maxRows IN NUMBER default 10 )
AS
  v_desc DBMS_SQL.DESC_TAB;
  v_cols BINARY_INTEGER;
  v_cursor BINARY_INTEGER;
  v_varchar2 VARCHAR2(4000);
  v_number   NUMBER;
  v_date     DATE;
  v_data     VARCHAR2(32767);
  v_curRow   NUMBER;
BEGIN
  /* Convert refcursor "parameter" to DBMS_SQL cursor... */
  v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(p_refcursor);
  /* Describe the cursor... */
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
  /* Define columns to be fetched. We're only using V2, NUM, DATE for example... */
  FOR i IN 1 .. v_cols
  LOOP
    IF v_desc(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_number);
    ELSIF v_desc(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_date);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_varchar2, 4000);
    END IF;
  END LOOP;
  /* Now output the data, starting with header... */
  DBMS_OUTPUT.NEW_LINE;
  FOR i IN 1 .. v_cols
  LOOP
    v_data := v_data ||
    CASE v_desc(i).col_type
    WHEN 2 THEN
      LPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
    WHEN 12 THEN
      RPAD(v_desc(i).col_name, 22)
    ELSE
      RPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
    END || ' ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_data);
  v_data := NULL;
  FOR i IN 1 .. v_cols
  LOOP
    v_data := v_data ||
    CASE v_desc(i).col_type
    WHEN 2 THEN
      LPAD('-', v_desc(i).col_max_len+1, '-')
    WHEN 12 THEN
      RPAD('-', 22, '-')
    ELSE
      RPAD('-', v_desc(i).col_max_len+1, '-')
    END || ' ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_data);
  /* Fetch all data... */
  v_curRow := v_maxRows;
  WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
  LOOP
    v_data := NULL;
    FOR i IN 1 .. v_cols
    LOOP
      IF v_desc(i).col_type = 2 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_number);
        v_data                := v_data || LPAD(v_number, v_desc(i).col_max_len+1) || ' ';
      ELSIF v_desc(i).col_type = 12 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_date);
        v_data := v_data || RPAD(v_date, 22) || ' ';
      ELSE
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_varchar2);
        v_data := v_data || RPAD(v_varchar2, v_desc(i).col_max_len+1) || ' ';
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_data);
    if  v_maxRows <> 0 then
       v_curRow :=  v_curRow - 1;
       exit when 0 = v_curRow;
    end if;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;

Enable and Disable constraints and triggers in Oracle

During data migrations it’s common to have constraints disabled, usually for loading performance or testing purposes.
To aid in such task, we’re sharing two simple scripts, one for disabling and another for enabling.

To disable all enabled constraints and triggers, just execute:

BEGIN
  -- Constraints
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    -- DISABLE
    execute immediate 'alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name;
  END LOOP;
 
  -- Triggers
  FOR ut IN
    (select ut.table_owner, ut.trigger_name
    from user_triggers ut
    where ut.status = 'ENABLED')
  LOOP
    -- DISABLE
    execute immediate 'ALTER TRIGGER "' || ut.table_owner || '"."' || ut.trigger_name || '" DISABLE;';
  END LOOP;
END;
/

To enable all disabled constraints and triggers, just execute:

BEGIN
  -- Constraints
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLE'
   ORDER BY c.constraint_type DESC)
  LOOP
    -- ENABLE
    execute immediate 'alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name;
  END LOOP;
 
  -- Triggers
  FOR ut IN
    (select ut.table_owner, ut.trigger_name
    from user_triggers ut
    where ut.status = 'DISABLE')
  LOOP
    -- ENABLE
    execute immediate 'ALTER TRIGGER "' || ut.table_owner || '"."' || ut.trigger_name || '" ENABLE;';
  END LOOP;
END;
/

For XML Path Equivalent in Oracle For String Aggregation

During a migration from SQL Server to Oracle 11g, I’ve came across with a specific functionality of T-SQL FOR XML PATH.
In the scope of the migration, the result of the select clause where this was applied was the concatenation of the values of a column per key.

Here’s an example of what I’ve found on T-SQL during the migration:

Declare @tbl1 table (
  id int,
  Col1 varchar(50),
  Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A001','Y'

INSERT INTO @tbl1
SELECT 2,'A002','N'

INSERT INTO @tbl1
SELECT 3,'A003','N'

INSERT INTO @tbl1
SELECT 4,'A004','Y'

INSERT INTO @tbl1
SELECT 5,'A005','N'

INSERT INTO @tbl1
SELECT 6,'A006','N'

INSERT INTO @tbl1
SELECT 7,'A007','N'

INSERT INTO @tbl1
SELECT 8,'A008','Y'

-- Show current values
select * from @tbl1

-- Get all values when col2 has value 'N'
SELECT c.col1 AS [text()]
FROM @tbl1 c
WHERE c.col2 = 'N'
FOR XML PATH(''),type

-- Aggregate all values from col1 using col2 as key
select c1.col2 [Yes/No],
  (SELECT c.col1 AS [text()]
   FROM @tbl1 c
   WHERE c.col2 = c1.col2
   FOR XML PATH(''),type) as Value
FROM @tbl1 c1
group by c1.col2
order by c1.col2

The execution of this T-SQL script shows how it is possible transform the result of a query into a new result where the values are aggregated by col1, and all col2 values are concatenated.

In order to achieve this kind of aggregate transformation in Oracle, one has to make use of an associative array, as Tom explains in the “String Concatenation” how one can do this. Using the examples in the AskTom web site, first we create the “STRING_AGG_TYPE” type:

CREATE OR REPLACE TYPE string_agg_type AS object
(
  total VARCHAR2(4000),

  static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type) RETURN NUMBER,

  member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2) RETURN NUMBER,

  member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,

  member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) RETURN NUMBER
);
/

And the correspondent body:

CREATE OR REPLACE TYPE BODY string_agg_type
IS

static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
RETURN NUMBER
IS
BEGIN
  sctx := string_agg_type( NULL );
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
  -- NOTE: Insert a separator in the empty string if you wish
  self.total := self.total || '' || VALUE;
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
  -- NOTE: ltrim(self.total, '') if you've specified a separator in ODCIAggregateIterate
  returnValue := LTRIM(self.total);
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
RETURN NUMBER
IS
BEGIN
  self.total := self.total || ctx2.total;
  RETURN ODCIConst.Success;
END;

END;
/

Then we create the aggregated function “stragg” of the type we’ve just created:

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

We are now able to make a similar aggregation in Oracle:

CREATE TABLE tbl1 (
  id NUMBER(10),
  Col1 VARCHAR(50),
  Col2 VARCHAR(50)
);
/

INSERT INTO tbl1 (id, col1, col2)
VALUES (1,'A001','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (2,'A002','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (3,'A003','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (4,'A004','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (5,'A005','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (6,'A006','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (7,'A007','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (8,'A008','Y');
/
COMMIT;
/

-- Show current values
SELECT * FROM tbl1;
/

-- Get all values when col2 has value 'N'
SELECT c.col2, STRAGG(c.col1)
FROM tbl1 c
WHERE c.col2 = 'N'
GROUP BY c.col2;
/

-- Aggregate all values from col1 using col2 as key
SELECT c1.col2 "Yes/No",
  (SELECT STRAGG(c.col1)
   FROM tbl1 c
   WHERE c.col2 = c1.col2
  ) AS val
FROM tbl1 c1
GROUP BY c1.col2
ORDER BY c1.col2;
/

-- drop table tbl1;

And that’s it. This will work in 9i and beyond, for 8i, check the 8i workaround in the same AskTom thread.