Feed aggregator

data guard drift

Tom Kyte - 10 hours 33 min ago
I have setup DR with maximum performance mode, at any time of instance, how to find the duration of time of data difference between DC and DR.
Categories: DBA Blogs

DATABASE STORAGE USAGE IN ASM

Tom Kyte - 10 hours 33 min ago
Hey Guys, I am working in a RAC environment and have been struggling to get a perfect query to view ASM Storage usage based on specific and each databases on the ASM. <b>Simply, i want a result that shows a database and how much size that databas...
Categories: DBA Blogs

Materialized View Add Columns

Tom Kyte - 10 hours 33 min ago
Hi Tom, I have added a new column in my master table and i want this column to be reflected in my Materialized view is this possible without Re creating it i have to fetch all columns from my master table in my view.please suggest.
Categories: DBA Blogs

Event SQL*Net break/reset to client in refresh of materialized view

Tom Kyte - 10 hours 33 min ago
Dear, I have had event of "SQL*Net break/reset to client" in session (job) with refresh of materialized view Same after kill it the session, kill the running job, the new session again remains with this event. The solution always is re-create th...
Categories: DBA Blogs

Automating service creation in Oracle APEX

Joel Kallman - Sun, 2018-08-19 11:47
Oracle Academy is a division in Oracle whose mission is to advance "computer science education globally to drive knowledge, innovation, skills development, and diversity in technology fields."  The programs that Oracle Academy offers are free to accredited secondary schools, technical/vocational schools, and two and four-year colleges and universities.

Oracle Application Express (APEX) has been hosted by Oracle Academy and used for many years to facilitate the delivery of curriculum for database design and programming, SQL, PL/SQL and even APEX.  To facilitate their business requirements, they had custom extensions written (in part, by our team) into the core APEX product.  But this type of solution becomes difficult to maintain, because every release of APEX requires migration and rewrite of these custom extensions.

A number of months ago, I met with the Oracle Academy team to try and encourage them to move the service creation of APEX workspaces into their own custom interfaces.  I told them that everything is provided to easily and programmatically create APEX workspaces, create database users (schemas), create administrators and developers within each workspace, and even pre-load APEX applications and custom database objects in each workspace, at the time of service creation.

Naturally, they asked for an example script to accomplish all of these tasks, which I authored and shared with them.  Because this type of logic is very relevant for many other purposes, e.g., testing, continuous integration, DevOps, I wish to share these same annotated scripts here.  If you are someone who has been using APEX for 10 years, you won't learn anything new here.  But for those just getting started with APEX, I hope you find it fruitful.

Starting with an empty Oracle database, the requirements are to develop a single script which will:

  1. Create a tablespace (storage) for each workspace
  2. Create and associate multiple database users (schemas) with each workspace
  3. Create an APEX workspace
  4. Create an administrator and developer account in each workspace, restricting the access of each developer to only one of the schemas mapped to the workspace.
  5. Create custom database objects in each schema
And here we go...
set define '^' verify on
set concat on
set concat .

Rem
Rem Title: Demo_of_Provisioning.sql
Rem
Rem Description: This script will demonstrate use of all of the APIs and SQL statements necessary
Rem to create tablespaces, data files, database users, and APEX workspaces. From this
Rem example, it is assumed that the Academy team will be able to develop their own custom
Rem provisioning process and no longer rely upon custom extensions to the
Rem Oracle Application Express software.
Rem
Rem Notes: It is assumed that this script is run as user SYSTEM.
Rem
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jkallman 08/19/2018 - Created


column foo1 new_val LOG1
select 'Demo_of_Provisioning_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1

timing start "Create demonstration tablespaces, schemas and workspaces"


--
-- Predefine the path used for tablespace datafile creation. If you're using Oracle Managed Files
-- or grid infrastructure, then this isn't necessary and you'll remove the 'datafile' portion of
-- the CREATE TABLESPACE statements
--
define DATAFILE_PATH='/u01/app/oracle/oradata/'


--
-- Step 1: Create the Tablespaces
--
-- Since Oracle Academy will want to group multiple database users/workspaces into a single tablespace, we'll need to
-- first create the tablespaces, and then the database users in step 2
--
create tablespace OACADEMY_DATA1 datafile '^DATAFILE_PATH.iacademy_01.dbf'
size 50M autoextend on next 50M maxsize 2G extent management local
autoallocate segment space management auto;

create tablespace OACADEMY_DATA2 datafile '^DATAFILE_PATH.iacademy_02.dbf'
size 50M autoextend on next 50M maxsize 2G extent management local
autoallocate segment space management auto;



--
-- Step 2: Create the Database Users (Schemas)
--
-- It's up to you how you wish to group database users/schemas into different tablespaces.
-- You can do it by total number or free space or ratio of users to tablespaces. It's all up to you.
--
begin
-- DB users in DATA1 tablespace
execute immediate 'create user OACADEMY_DB10 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';

execute immediate 'create user OACADEMY_DB11 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';

-- DB users in DATA2 tablespace
execute immediate 'create user OACADEMY_DB20 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';

execute immediate 'create user OACADEMY_DB21 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';

end;
/



--
-- Step 3: Create the APEX Workspaces
--
-- Create the APEX workspaces and associate a default schema with each
--
begin
apex_instance_admin.add_workspace(
p_workspace_id => null,
p_workspace => 'OACADEMY1',
p_primary_schema => 'OACADEMY_DB10');
end;
/

begin
apex_instance_admin.add_workspace(
p_workspace_id => null,
p_workspace => 'OACADEMY2',
p_primary_schema => 'OACADEMY_DB20');
end;
/


--
-- Step 4: Add additional schemas to the existing workspaces
--
--
begin
apex_instance_admin.add_schema(
p_workspace => 'OACADEMY1',
p_schema => 'OACADEMY_DB11');
end;
/

begin
apex_instance_admin.add_schema(
p_workspace => 'OACADEMY2',
p_schema => 'OACADEMY_DB21');
end;
/


--
-- Show a quick summary of the workspaces and schemas
--
column workspace_name format a50
column schema format a40
select workspace_name, schema from apex_workspace_schemas;



--
-- Step 5: Create an administrator account and a developer account in each worskpace
--
--
begin
-- We must set the APEX workspace security group ID in our session before we can call create_user
apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY1'));

apex_util.create_user(
p_user_name => 'BOB',
p_email_address => 'bob@bob.com',
p_default_schema => 'OACADEMY_DB10',
p_allow_access_to_schemas => 'OACADEMY_DB10',
p_web_password => 'change_me',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- workspace administrator

apex_util.create_user(
p_user_name => 'JUNE',
p_email_address => 'june@june.com',
p_default_schema => 'OACADEMY_DB11',
p_allow_access_to_schemas => 'OACADEMY_DB11',
p_web_password => 'change_me',
p_developer_privs => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- developer

commit;
end;
/

begin
-- We must set the APEX workspace security group ID in our session before we can call create_user
apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY2'));

apex_util.create_user(
p_user_name => 'ALICE',
p_email_address => 'alice@alice.com',
p_default_schema => 'OACADEMY_DB20',
p_allow_access_to_schemas => 'OACADEMY_DB20',
p_web_password => 'change_me',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- workspace administrator

apex_util.create_user(
p_user_name => 'AUGUST',
p_email_address => 'august@august.com',
p_default_schema => 'OACADEMY_DB21',
p_allow_access_to_schemas => 'OACADEMY_DB21',
p_web_password => 'change_me',
p_developer_privs => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- developer

commit;
end;
/

--
-- Show a quick summary of the APEX users
--
column workspace_name format a30
column user_name format a20
column email format a25
column is_admin format a10
column is_application_developer format a10
select workspace_name, user_name, email, is_admin, is_application_developer from apex_workspace_apex_users order by 1,2;


--
-- Install custom database objects in each schema by simply running one more SQL scripts. Note that the
-- script below is something that you author and maintain. It will do all DML and DDL you have in it,
-- and we simply iterate through the schemas and run the script each time.
--
alter session set current_schema = OACADEMY_DB10;
@custom.sql

alter session set current_schema = OACADEMY_DB11;
@custom.sql

alter session set current_schema = OACADEMY_DB20;
@custom.sql

alter session set current_schema = OACADEMY_DB21;
@custom.sql

timing stop
spool off


And to complete the lifecycle, I also authored a simple SQL script which will cleanup everything created above - removing the APEX workspaces, database users and tablespaces.

set define '^' verify on
set concat on
set concat .

Rem
Rem Title: Demo_of_Cleanup.sql
Rem
Rem Description: This script will cleanup all objects created by script Demo_of_Provisioning.sql
Rem
Rem Notes: It is assumed that this script is run as user SYSTEM.
Rem
Rem **** THIS SCRIPT IS DESTRUCTIVE **** - It will drop tablespaces, data files, workspaces, schemas, etc.
Rem
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jkallman 08/19/2018 - Created


column foo1 new_val LOG1
select 'Demo_of_Cleanup_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1


timing start "Remove all workspaces, schemas and tablespaces"

--
-- Step 1: Remove the APEX Workspaces
--
--
begin
apex_instance_admin.remove_workspace(
p_workspace => 'OACADEMY1' );
end;
/

begin
apex_instance_admin.remove_workspace(
p_workspace => 'OACADEMY2' );
end;
/



--
-- Step 2: Drop the database users
--
--
drop user OACADEMY_DB10 cascade;
drop user OACADEMY_DB11 cascade;
drop user OACADEMY_DB20 cascade;
drop user OACADEMY_DB21 cascade;



--
-- Step 3: Drop the tablespaces
--
--
drop tablespace oacademy_data1 including contents and datafiles;
drop tablespace oacademy_data2 including contents and datafiles;

timing stop
spool off

oracle utl_file encoding from utf8; missing character

Tom Kyte - Sat, 2018-08-18 14:46
I like to export large amount of text data from db to file. The characterset in db is UTF8. The excepted result in the file is ISO8859P2 or MSWIN1250. My db settings: <code> SELECT * FROM v$nls_parameters; 1 NLS_LANGUAGE HUNGARIAN 0 ...
Categories: DBA Blogs

How do I determine how much storage will be required for NUMBER(p, s)?

Tom Kyte - Sat, 2018-08-18 14:46
<i>Oracle Database SQL Language Reference</i> states, "Each NUMBER value requires from 1 to 22 bytes." I was curious how much space a NUMBER(10, 0) would take, so I did: <code>SQL> create table t (a number(10, 0)) 2 / Table created. SQL>...
Categories: DBA Blogs

Exception when executing Bulk Collect

Tom Kyte - Sat, 2018-08-18 14:46
I am using bulk collect with save exceptions, it runs fine sometimes and sometimes it errors out. The error is as follows - <code>BEGIN * ERROR at line 1: ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-...
Categories: DBA Blogs

Performance issue after database upgrade from 11.2.0.4 to 12.1.0.2

Tom Kyte - Sat, 2018-08-18 14:46
Hi Team, We have recently upgraded a SAP database from 11.2.0.4 to 12.1.0.2, After encountered performance issues that did not exist on the previous version(11.2.0.4). Proper keeping sga_target/sga_maxsize did not help Proper keeping db_file_m...
Categories: DBA Blogs

DBMS_SQLTUNE_UTIL0

Tom Kyte - Sat, 2018-08-18 14:46
Hi Tom I have a question regarding DBMS_SQLTUNE_UTIL0 package. This seems to be helper package. Is it okay to use this package directly in code or use of such internal packages should be avoided? I did not find any documentation on this packa...
Categories: DBA Blogs

Space Fragmentation in a table which has only inserts

Tom Kyte - Sat, 2018-08-18 14:46
Hi Tom, This is Praveen. 1)I have table x which has only inserts in it and updates rarely,so my doubt is till now i have heard and read that there will be space fragmentation for delete operation done on a table and i couldn't find out how the ...
Categories: DBA Blogs

definition of v$lock table

Tom Kyte - Sat, 2018-08-18 14:46
please find below query output.. select OWNER,OBJECT_NAME,OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME in ('V$LOCK'); ---PUBLIC V$LOCK SYNONYM as per above query output V$LOCK is SYNONYM. so i checked in DBA_SYNONYMS for further informa...
Categories: DBA Blogs

Rownum = 1 no longer returning results after upgrade to 12.1

Tom Kyte - Sat, 2018-08-18 14:46
Hi, I have a view that I created in 11g, after we upgraded the database to 12.1 it is no longer returning the dates for the view. I pulled the SQL for the view and confirmed that it no longer works. It does return the comment. I need to use the r...
Categories: DBA Blogs

Oracle SOA Suite 12c : Audit Levels (Off, Production, Development)

Dietrich Schroff - Sat, 2018-08-18 08:02
Inside the Enterprise Manager (URL: http://yourhost:7001/em) you can configure SOA Suite Audit Levels:

Choose "Common Properties"

Click on "Change Profile"
The oracle documentation shows the following:



  • Off:
    No business flow instance tracking and payload tracking information is collected. Drilling down into a flow shows the components and their status. Using this setting limits database growth, and also helps reduce latency and improve throughput.
  • Production:
    Flow and audit event information is collected and stored in the database. Drilling down into a flow provides the flow trace. The flow trace shows the exact sequence of component interaction, and also shows component statuses. Drilling down into a BPEL instance shows the execution of BPEL activities, and their statuses.
    As the flow-trace audit trail may not be required for all composites, you may want to set the audit level to Off at the SOA Infrastructure level, and set the audit level to Production for the required composites
  • Development:
    This option collects payload details in addition to flow and audit events. This setting is not recommended for a production environment, as it will impact performance and result in rapid database growth. This level is useful largely for testing and debugging purposes only.

Partner Webcast – Monitoring your cloud-based Infrastructure with Oracle Management Cloud

Today’s Modern IT supports today’s requirements by making IT infrastructure more accessible and available based on demand.  IT infrastructure can be made available both in your...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partitioning -- 3b : More Indexes on Partitioned Table

Hemant K Chitale - Sat, 2018-08-18 03:23
In the previous blog post, I had demonstrated a Global (Non-Partitioned) Index and a Local (Partitioned) Index.  A Global Index itself may be partitioned.  A Local Index is actually Equi-Partitioned with the Tablee.

Let me demonstrate a Global Partitioned Index on the same table now.

Given the SALES_DATA table which is partitioned by SALE_DATE, I might want to support queries by CUSTOMER_ID.  What if I have millions of customers?  My CUSTOMERS table might be partitioned by CUSTOMER_ID.  Can I have an Index on SALES_DATA that is also partitioned by CUSTOMER_ID ?

SQL> l
1 create index sales_data_glbl_part_ndx_2
2 on sales_data (customer_id)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /

Index created.

SQL>


This is a Global Partitioned Index which is prefixed on a left prefix (CUSTOMER_ID) of the Index Column(s). Oracle does not support Global Partitioned Indexes that are non-prefixed.  Let me demonstrated the difference between the two.

SQL> drop index sales_data_glbl_part_ndx_2;

Index dropped.

SQL>
SQL> l
1 create index sales_data_glbl_part_ndx_2
2 on sales_data (customer_id, sale_date)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /

Index created.

SQL>
SQL>
SQL> l
1 create index sales_data_glbl_part_ndx_3
2 on sales_data (sale_date)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /
partition by range (customer_id)
*
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause: User attempted to create a GLOBAL non-prefixed partitioned index
// which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
// index, it must be created as LOCAL; otherwise, correct the list
// of key and/or partitioning columns to ensure that the index is
// prefixed

SQL>


My first attempt above is valid because the Index is partitioned on CUSTOMER_ID although it is defined on (CUSTOMER_ID, SALE_DATE).  Thus, it is partitioned on a left prefix. The second index attempt fails because the Partition Key (CUSTOMER_ID) is not a left prefix (part of) the Index key (SALE_DATE alone).

Remember : The Index I've created is  partitioned on CUSTOMER_ID while the Table itself is partitioned on SALE_DATE.
This Index is a Global Index so any Partition Maintenance operation (TRUNCATE, DROP, SPLIT, MERGE) may result in the *entire* Index becoming UNUSABLE unless the UPDATE INDEXES clause is used in the Partition Maintenance DDL statement.  This was demonstrated with the Primary Key index being a Global Index in my earlier blog post.


What about Bitmap Indexes ?  Can a Bitmap Index on a partitioned Table be either Global or Local ?  Oracle does not support Global Bitmap Indexes.  A Bitmap Index on a Partitioned Table has to be Local.

SQL> create bitmap index sales_data_glbl_ndx_4
2 on sales_data(product_id)
3 /
on sales_data(product_id)
*
ERROR at line 2:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables


SQL> !oerr ora 25122
25122, 00000, "Only LOCAL bitmap indexes are permitted on partitioned tables"
// *Cause: An attempt was made to create a global bitmap index on a partioned
// table.
// *Action: create a local bitmap index instead.

SQL>
SQL> create bitmap index sales_data_lcl_ndx_2
2 on sales_data(product_id) local
3 /

Index created.

SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

SQL> select partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name = 'SALES_DATA_LCL_NDX_2'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------
P_2018 TBS_YEAR_2018 USABLE
P_2019 TBS_YEAR_2019 USABLE
P_2020 TBS_YEAR_2020 USABLE
P_MAXVALUE USERS USABLE

SQL>


As demonstrated earlier, a Local Index automatically inherits the Partition Name and Tablespace Name from the Table Partitions.

To summarise, these are the Indexes on my SALES_DATA table :

SQL> l        
1 select index_name, index_type, partitioned, tablespace_name, status
2 from user_indexes
3* where table_name = 'SALES_DATA'
SQL> /

INDEX_NAME INDEX_TYPE PAR
------------------------------ --------------------------- ---
TABLESPACE_NAME STATUS
------------------------------ --------
SYS_C0017514 NORMAL NO
HEMANT VALID

SALES_DATA_LCL_NDX_1 NORMAL YES
N/A

SALES_DATA_GLBL_PART_NDX_2 NORMAL YES
N/A

SALES_DATA_LCL_NDX_2 BITMAP YES
N/A


SQL>


Remember that for a Partitioned Index (either Global or Local), the Tablespace Name and Status are attributes at the Partition definition level not at the Index definition.


Categories: DBA Blogs

Learn Why and When Website prototyping is important

Nilesh Jethwa - Fri, 2018-08-17 23:07

A Simple Explanation to Website Prototyping A website prototype is an initial design of a website. Developers, testers, and users can interact with it. Developers often create these prototypes to gather feedback from stakeholders or clients. They create one during … Continue reading ?

Source: MockupTiger Wireframes

Court Upholds Permanent Injunction Against Rimini Street

Chris Warticki - Fri, 2018-08-17 16:18

On August 15, a Federal Court in Nevada once again granted Oracle's motion for a permanent injunction against Rimini Street and required payment to Oracle of $28 million in attorney’s fees due to Rimini’s egregious litigation misconduct. 

In upholding the injunction, the Court made clear that Rimini’s business “was built entirely on its infringement of Oracle’s copyrighted software.” In addition, the court noted that Rimini’s improper conduct not only enabled the company to grow quickly, but also unfairly “eroded the bonds and trust that Oracle has with its customers.”

In a statement, Dorian Daley, Oracle's Executive Vice President and General Counsel, reinforced the Court’s findings. “As the Court's Order today makes clear, Rimini Street's business has been built entirely on unlawful conduct, and Rimini's executives have repeatedly lied to cover up their company's illegal acts.”

To learn more about the Federal Court ruling:

1. Read full press release

2. "Oracle Punches Rimini Street Hard in Court" - Enterprise Times

The size of Oracle Home: from 9GB to 600MB – What about PostgreSQL?

Yann Neuhaus - Fri, 2018-08-17 13:20

A recent blog post from Franck and a tweet around that topic is the inspiration for this blog post, thanks Jan for requesting :). In short it is about how small you can get the binaries. Is that important? At least when it comes to Docker images it might get important as you usually try make the image as small as possible. Well, comparing PostgreSQL and Oracle in that field is unfair as Oracle comes with many stuff by default which PostgreSQL is just not shipping (e.g. Apex, SQL Developer, …), so please treat this more a as fun post, please.

The way we usually compile PostgreSQL is this (not in /var/tmp in real life):

postgres@pgbox:/home/postgres/ [pg103] cd /var/tmp/
postgres@pgbox:/var/tmp/ [pg103] wget https://ftp.postgresql.org/pub/source/v10.5/postgresql-10.5.tar.bz2
postgres@pgbox:/var/tmp/ [pg103] tar -axf postgresql-10.5.tar.bz2
postgres@pgbox:/var/tmp/ [pg103] cd postgresql-10.5/
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] PGHOME=/var/tmp/pg105
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] SEGSIZE=2
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] BLOCKSIZE=8
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] WALSEGSIZE=16
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] ./configure --prefix=${PGHOME} \
                                                             --exec-prefix=${PGHOME} \
                                                             --bindir=${PGHOME}/bin \
                                                             --libdir=${PGHOME}/lib \
                                                             --sysconfdir=${PGHOME}/etc \
                                                             --includedir=${PGHOME}/include \
                                                             --datarootdir=${PGHOME}/share \
                                                             --datadir=${PGHOME}/share \
                                                             --with-pgport=5432 \
                                                             --with-perl \
                                                             --with-python \
                                                             --with-openssl \
                                                             --with-pam \
                                                             --with-ldap \
                                                             --with-libxml \
                                                             --with-libxslt \
                                                             --with-segsize=${SEGSIZE} \
                                                             --with-blocksize=${BLOCKSIZE} \
                                                             --with-wal-segsize=${WALSEGSIZE}  \
                                                             --with-systemd
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make -j 4 all
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make install
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] cd contrib
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make -j 4 install

When we do this against the PostgreSQL 10.5 source code the result is this (without the documentation, of course, but containing all the extensions ):

postgres@pgbox:/var/tmp/postgresql-10.5/contrib/ [pg103] du -sh /var/tmp/pg105/
28M	/var/tmp/pg105/

Can we get that even smaller? Let’s try to skip the extensions:

postgres@pgbox:/var/tmp/postgresql-10.5/contrib/ [pg103] cd ..
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make clean
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] ./configure --prefix=${PGHOME} \
                                                             --exec-prefix=${PGHOME} \
                                                             --bindir=${PGHOME}/bin \
                                                             --libdir=${PGHOME}/lib \
                                                             --sysconfdir=${PGHOME}/etc \
                                                             --includedir=${PGHOME}/include \
                                                             --datarootdir=${PGHOME}/share \
                                                             --datadir=${PGHOME}/share \
                                                             --with-pgport=5432 \
                                                             --with-perl \
                                                             --with-python \
                                                             --with-openssl \
                                                             --with-pam \
                                                             --with-ldap \
                                                             --with-libxml \
                                                             --with-libxslt \
                                                             --with-segsize=${SEGSIZE} \
                                                             --with-blocksize=${BLOCKSIZE} \
                                                             --with-wal-segsize=${WALSEGSIZE}  \
                                                             --with-systemd
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make -j 4 all
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] rm -rf /var/tmp/pg105/
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make install

What do we have now?

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] du -sh /var/tmp/pg105/
25M	/var/tmp/pg105/

We saved another 3MB. Can we do more? Let’s try to skip all the “–with” flags that enable perl and so on for the configure command:

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make clean
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] ./configure --prefix=${PGHOME} \
                                                             --exec-prefix=${PGHOME} \
                                                             --bindir=${PGHOME}/bin \
                                                             --libdir=${PGHOME}/lib \
                                                             --sysconfdir=${PGHOME}/etc \
                                                             --includedir=${PGHOME}/include \
                                                             --datarootdir=${PGHOME}/share \
                                                             --datadir=${PGHOME}/share \
                                                             --with-pgport=5432 \
                                                             --with-segsize=${SEGSIZE} \
                                                             --with-blocksize=${BLOCKSIZE} \
                                                             --with-wal-segsize=${WALSEGSIZE}  \
                                                             --with-systemd
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make -j 4 all
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] rm -rf /var/tmp/pg105/
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] make install

Do we see a change?

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] du -sh /var/tmp/pg105/
25M	/var/tmp/pg105/

No, that does not change anything. Franck stripped the Oracle binaries and libraries, so lets try to do the same (although I am not sure right now if that is supported):

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] du -sh /var/tmp/pg105/
25M	/var/tmp/pg105/
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] strip /var/tmp/pg105/bin/*
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] strip /var/tmp/pg105/lib/*
strip: Warning: '/var/tmp/pg105/lib/pkgconfig' is not an ordinary file
strip: Warning: '/var/tmp/pg105/lib/postgresql' is not an ordinary file
postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] du -sh /var/tmp/pg105/
24M	/var/tmp/pg105/

So, another 1MB less. Can we still initialize and start PostgreSQL?

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] /var/tmp/pg105/bin/initdb -D /var/tmp/testpg
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.utf8
  CTYPE:    en_US.utf8
  MESSAGES: en_US.utf8
  MONETARY: de_CH.UTF-8
  NUMERIC:  de_CH.UTF-8
  TIME:     en_US.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/tmp/testpg ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /var/tmp/pg105/bin/pg_ctl -D /var/tmp/testpg -l logfile start

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] /var/tmp/pg105/bin/pg_ctl -D /var/tmp/testpg/ start
waiting for server to start....2018-08-17 18:57:50.329 CEST [8528] LOG:  listening on IPv6 address "::1", port 5432
2018-08-17 18:57:50.329 CEST [8528] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-08-17 18:57:50.334 CEST [8528] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-08-17 18:57:50.354 CEST [8529] LOG:  database system was shut down at 2018-08-17 18:57:31 CEST
2018-08-17 18:57:50.358 CEST [8528] LOG:  database system is ready to accept connections
 done
server started

Looks good and we are able to connect:

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] /var/tmp/pg105/bin/psql -c "select version()" postgres
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

Time: 1.428 ms

What else can we do? When you do not need the utilities on the server you could just remove them (as said, this is a fun post, don’t do this):

postgres@pgbox:/var/tmp/postgresql-10.5/ [pg103] cd /var/tmp/pg105/bin
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] rm clusterdb createdb createuser dropdb dropuser pg_archivecleanup pg_basebackup pg_dump pg_dumpall pg_isready pg_receivewal pg_recvlogical pg_resetwal pg_restore pg_rewind pg_test_fsync pg_test_timing pg_upgrade pg_waldump reindexdb vacuumdb

We could probably even remove pgbench and psql but these two I will need to show that the server is still working. What do we have now?

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] du -sh /var/tmp/pg105/
21M	/var/tmp/pg105/

Another 3MB less. Can we still restart and connect?

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] /var/tmp/pg105/bin/pg_ctl -D /var/tmp/testpg/ stop
waiting for server to shut down....2018-08-17 19:08:49.588 CEST [9144] LOG:  received fast shutdown request
2018-08-17 19:08:49.593 CEST [9144] LOG:  aborting any active transactions
2018-08-17 19:08:49.597 CEST [9144] LOG:  worker process: logical replication launcher (PID 9151) exited with exit code 1
2018-08-17 19:08:49.598 CEST [9146] LOG:  shutting down
2018-08-17 19:08:49.625 CEST [9144] LOG:  database system is shut down
 done
server stopped
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] /var/tmp/pg105/bin/pg_ctl -D /var/tmp/testpg/ start
waiting for server to start....2018-08-17 19:08:51.949 CEST [9368] LOG:  listening on IPv6 address "::1", port 9999
2018-08-17 19:08:51.949 CEST [9368] LOG:  listening on IPv4 address "127.0.0.1", port 9999
2018-08-17 19:08:51.953 CEST [9368] LOG:  listening on Unix socket "/tmp/.s.PGSQL.9999"
2018-08-17 19:08:51.966 CEST [9369] LOG:  database system was shut down at 2018-08-17 19:08:49 CEST
2018-08-17 19:08:51.969 CEST [9368] LOG:  database system is ready to accept connections
 done
server started
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] /var/tmp/pg105/bin/psql -c "select version()" postgres
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

Time: 2.043 ms

Looks good. Now lets do the final step and remove the rest which is not required for the server, but before that we do an initdb as we can not do that afterwards:

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] /var/tmp/pg105/bin/pg_ctl -D /var/tmp/testpg/ stop
waiting for server to shut down....2018-08-17 19:10:31.693 CEST [9368] LOG:  received fast shutdown request
2018-08-17 19:10:31.696 CEST [9368] LOG:  aborting any active transactions
2018-08-17 19:10:31.696 CEST [9368] LOG:  worker process: logical replication launcher (PID 9375) exited with exit code 1
2018-08-17 19:10:31.697 CEST [9370] LOG:  shutting down
2018-08-17 19:10:31.712 CEST [9368] LOG:  database system is shut down
 done
server stopped
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] rm -rf /var/tmp/testpg/
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] /var/tmp/pg105/bin/initdb -D /var/tmp/testpg
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.utf8
  CTYPE:    en_US.utf8
  MESSAGES: en_US.utf8
  MONETARY: de_CH.UTF-8
  NUMERIC:  de_CH.UTF-8
  TIME:     en_US.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/tmp/testpg ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /var/tmp/pg105/bin/pg_ctl -D /var/tmp/testpg -l logfile start

So, remove the rest:

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] rm pg_config pg_controldata psql pgbench initdb ecpg pgbench pg_ctl
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] du -sh /var/tmp/pg105/
20M	/var/tmp/pg105/

We are down to 20MB but we can still start the instance:

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] /var/tmp/pg105/bin/postgres -D /var/tmp/testpg/ &
[1] 9486
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] 2018-08-17 19:13:54.917 CEST [9486] LOG:  listening on IPv6 address "::1", port 9999
2018-08-17 19:13:54.917 CEST [9486] LOG:  listening on IPv4 address "127.0.0.1", port 9999
2018-08-17 19:13:54.924 CEST [9486] LOG:  listening on Unix socket "/tmp/.s.PGSQL.9999"
2018-08-17 19:13:54.955 CEST [9487] LOG:  database system was shut down at 2018-08-17 19:10:56 CEST
2018-08-17 19:13:54.960 CEST [9486] LOG:  database system is ready to accept connections

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] ps -ef | grep postgres
root      1061   941  0 18:26 ?        00:00:00 sshd: postgres [priv]
postgres  1064  1061  0 18:26 ?        00:00:02 sshd: postgres@pts/0
postgres  1065  1064  0 18:26 pts/0    00:00:01 -bash
postgres  9486  1065  0 19:13 pts/0    00:00:00 /var/tmp/pg105/bin/postgres -D /var/tmp/testpg/
postgres  9488  9486  0 19:13 ?        00:00:00 postgres: checkpointer process   
postgres  9489  9486  0 19:13 ?        00:00:00 postgres: writer process   
postgres  9490  9486  0 19:13 ?        00:00:00 postgres: wal writer process   
postgres  9491  9486  0 19:13 ?        00:00:00 postgres: autovacuum launcher process   
postgres  9492  9486  0 19:13 ?        00:00:00 postgres: stats collector process   
postgres  9493  9486  0 19:13 ?        00:00:00 postgres: bgworker: logical replication launcher  
postgres  9496  1065  0 19:14 pts/0    00:00:00 ps -ef
postgres  9497  1065  0 19:14 pts/0    00:00:00 grep --color=auto postgres

Using another psql on that box we can confirm that we can connect:

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] /u01/app/postgres/product/10/db_4/bin/psql -c "select version()" postgres
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

Still too much? What else can we? What is consuming space:

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] du -sh /var/tmp/pg105/*
6.6M	/var/tmp/pg105/bin
5.9M	/var/tmp/pg105/include
4.1M	/var/tmp/pg105/lib
2.9M	/var/tmp/pg105/share

We can not do more in the “bin” directory, nothing left to delete:

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] ls -l /var/tmp/pg105/bin
total 6660
-rwxr-xr-x. 1 postgres postgres 6817480 Aug 17 18:56 postgres
lrwxrwxrwx. 1 postgres postgres       8 Aug 17 18:54 postmaster -> postgres

Everything else will probably safe us a few bytes such as the sample files:

postgres@pgbox:/var/tmp/pg105/ [pg103] find . -name *sample*
./share/postgresql/tsearch_data/synonym_sample.syn
./share/postgresql/tsearch_data/thesaurus_sample.ths
./share/postgresql/tsearch_data/hunspell_sample.affix
./share/postgresql/tsearch_data/ispell_sample.affix
./share/postgresql/tsearch_data/ispell_sample.dict
./share/postgresql/tsearch_data/hunspell_sample_long.affix
./share/postgresql/tsearch_data/hunspell_sample_long.dict
./share/postgresql/tsearch_data/hunspell_sample_num.affix
./share/postgresql/tsearch_data/hunspell_sample_num.dict
./share/postgresql/pg_hba.conf.sample
./share/postgresql/pg_ident.conf.sample
./share/postgresql/postgresql.conf.sample
./share/postgresql/recovery.conf.sample
./share/postgresql/pg_service.conf.sample
./share/postgresql/psqlrc.sample

So how much space do we consume for the PostgreSQL installation and the files which make up the instance?

postgres@pgbox:/var/tmp/pg105/bin/ [pg103] du -sh /var/tmp/pg105/
20M	/var/tmp/pg105/
postgres@pgbox:/var/tmp/pg105/bin/ [pg103] du -sh /var/tmp/testpg/
41M	/var/tmp/testpg/

… 61MB. When we add the wal file Jan mentioned in his tweet we come the 77MB. Not much.

The final question is if PostgreSQL is still working. Let’s use pgbench from another installation on the same server against this:

postgres@pgbox:/var/tmp/pg105/ [pg103] /u01/app/postgres/product/10/db_3/bin/pgbench -i -s 10 postgres
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.08 s, remaining 0.75 s)
200000 of 1000000 tuples (20%) done (elapsed 0.24 s, remaining 0.95 s)
300000 of 1000000 tuples (30%) done (elapsed 0.42 s, remaining 0.98 s)
400000 of 1000000 tuples (40%) done (elapsed 0.49 s, remaining 0.74 s)
500000 of 1000000 tuples (50%) done (elapsed 0.70 s, remaining 0.70 s)
600000 of 1000000 tuples (60%) done (elapsed 0.88 s, remaining 0.58 s)
700000 of 1000000 tuples (70%) done (elapsed 0.95 s, remaining 0.41 s)
800000 of 1000000 tuples (80%) done (elapsed 1.14 s, remaining 0.29 s)
900000 of 1000000 tuples (90%) done (elapsed 1.32 s, remaining 0.15 s)
1000000 of 1000000 tuples (100%) done (elapsed 1.41 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
postgres@pgbox:/var/tmp/pg105/ [pg103] /u01/app/postgres/product/10/db_3/bin/pgbench -s 10 postgres
scale option ignored, using count from pgbench_branches table (10)
starting vacuum...end.
transaction type: 
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 4.436 ms
tps = 225.435296 (including connections establishing)
tps = 285.860401 (excluding connections establishing)

Looks good. So you can come down to 20MB for the PostgreSQL installation and another 41Mb for the files you need to start the instance. You could even drop the postgres database to save another 7MB. But remember: Please don’t do that, you are still fine with around 30MB :)

 

Cet article The size of Oracle Home: from 9GB to 600MB – What about PostgreSQL? est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator