How to check status and start/stop daemons on the AIX platform

For example, let's check the status of the ntpd service:

[root@myserver:/] lssrc -a|grep ntpd
 xntpd            tcpip                         inoperative

It is inoperative, meaning not running.

Start it:

[root@myserver:/] startsrc -s xntpd
0513-059 The xntpd Subsystem has been started. Subsystem PID is 11141174.

If you want to stop it, use

[root@myserver:/] stopsrc -s xntpd

How to expand the /tmp file system on a Linux server

Problem: /tmp needs more space.
It is placed in the root directory of the server:

[root@myserver ~]# df -h /
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_system-Root
               5.8G  4.0G  1.5G  73% /

Use vgdisplay to check how much free space exist in the volume group by looking at Free PE / Size:

[root@myserver]# vgdisplay
  --- Volume group ---
  VG Name               vg_system
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  11
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                5
  Open LV               5
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               24.00 GiB <-- total size of VG
  PE Size               4.00 MiB
  Total PE              6143
  Alloc PE / Size       5631 / 22.00 GiB
  Free  PE / Size       512 / 4.00 GiB <-- 4 GB free
  VG UUID               d0OgTw-stMx-GfyT-N1zA-cEv8-1OGY-2qZ8D4

Increase size of logical volume by 2GB:

lvextend -L +2G /dev/vg_system/Root 

 Size of logical volume vg_system/Root changed from 8.00 GiB (2047 extents) to 10.00 GiB (2559 extents).
  Logical volume Root successfully resized.

Finally increase size of file system:

resize2fs /dev/mapper/vg_system-Root 

resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/mapper/vg_system-Root is mounted on /; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 1
Performing an on-line resize of /dev/mapper/vg_system-Root to 2620416 (4k) blocks.
The filesystem on /dev/mapper/vg_system-Root is now 2620416 blocks long.

In this example:

vg_system = volume group name

/dev/vg_system/Root = Logical volume Path

/dev/mapper/vg_system-Root = file system name

The file system name you will find in /etc/fstab:

/dev/mapper/vg_system-Root      /       ext4    defaults        1       1

How to analyze a specific partition only using dbms_stats

To analyze only specific partitions, including subpartitions, use the dbms_stats.gather_table_stats while specifying the directives PART_NAME and GRANULARITY.

The following query reveals that several of the recently added subpartitions in the COMPLETED_ORDERS partition have not yet been analyzed:

SELECT P.PARTITION_NAME "partition name",P.NUM_ROWS "partition rows",P.LAST_ANALYZED "last analyzed", SP.SUBPARTITION_NAME "subpart name", SP.NUM_ROWS "subpartition rows",SP.LAST_ANALYZED "subpart last analyzed"
FROM DBA_TAB_PARTITIONS P INNER JOIN DBA_TAB_SUBPARTITIONS SP
ON (P.TABLE_NAME = SP.TABLE_NAME)
WHERE P.TABLE_NAME='ORDERS'
AND P.PARTITION_NAME = SP.PARTITION_NAME
AND P.PARTITION_NAME = 'COMPLETED_ORDERS'
AND SP.NUM_ROWS IS NULL
ORDER BY P.PARTITION_NAME DESC,SP.SUBPARTITION_NAME;

Output:

partition name	partition rows	last analyzed	subpart name	subpartition rows	subpart last analyzed
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2017
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2018
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2019
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2020

To analyze them, use dbms_stats with the PART_NAME directive, and the GRANULARITY directive set to "SUBPARTITION"

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName        => 'SCOTT'
    ,TabName        => 'ORDERS'
    ,partname       => 'COMPLETED_ORDERS'
    ,Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
    ,method_opt        => 'FOR ALL COLUMNS SIZE AUTO'
    ,Degree            => DBMS_STATS.AUTO_DEGREE
    ,Cascade           => DBMS_STATS.AUTO_CASCADE
    ,granularity       => 'SUBPARTITION'
    ,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE);
END;
/

After statistics gathering, execute the query again, but without the "AND SP.NUM_ROWS IS NULL" predicative:

partition name	partition rows	last analyzed	subpart name	subpartition rows	subpart last analyzed
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2017	0	27/12/2016 09:10:04
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2018	0	27/12/2016 09:10:05
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2019	0	27/12/2016 09:10:07
COMPLETED_ORDERS	2527257	19/10/2015 07:17:44	CO_SP_2020	0	27/12/2016 09:10:08

The 12.2 version of dbms_stats.gather_table_stats is documented here

How to create a partitioned table using a subpartition template

Using templates for your subpartitions is a handy shortcut to avoid specifying attributes for every subpartition in the table.

Oracle calls this concept "vertical striping", since it allows for each subpartition matching the template to end up in the same tablespace and thus allows you to "stripe" your partitions across multiple tablespaces, even though they logically belong to different partitions.

You only describe subpartitions once in the template; Oracle will then apply the template to every partition you create in the table.

The original table's layout is shown below. It is an imaginative tables that will store received documents in a governmental office.
In this example, the documents stored in the CLOB column will be saved in the same tablespace based on year, rather than the type of document it represents.

It will create

* A heap-organized, LIST-LIST partitioned table
* A column of type CLOB (character large object). We will call the CLOB object "REC_DOCS_XML_CLOB"
* 2 pre-defined LIST partitions
* 4 pre-defined LIST sub-partitions
* A subpartition template to simplify future additions of partitions.

CREATE TABLE RECEIVED_DOCUMENTS
(
  UUID                 VARCHAR2(160 BYTE),
  FISCAL_YEAR          NUMBER(4),
  DOCUMENTTYPE         VARCHAR2(100 CHAR),
  DOCUMENTNAME         VARCHAR2(1000 CHAR),
  DOCUMENTSTATE        VARCHAR2(30 CHAR),
  VALID                CHAR(1 BYTE),
  CREATED_TIMESTAMP    NUMBER(20),
  VERSION              NUMBER(20),
  DATA_XML             CLOB,
  FORMAT               VARCHAR2(1000 CHAR),
  PERIOD               VARCHAR2(1000 CHAR)
 )
LOB (DATA_XML) STORE AS SECUREFILE REC_DOCS_XML_CLOB(
 TABLESPACE RECEIVED_DOCUMENTS_LOB_DATA
 COMPRESS HIGH
)
-- The table definition is stored in the tablespace DOCS_DATA
TABLESPACE DOCS_DATA
PARTITION BY LIST (DOCUMENTTYPE)
SUBPARTITION BY LIST (PERIOD)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DOCS_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DOCS_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DOCS_2016,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DOCS_DATA
   )
(
  PARTITION SETTELM
    VALUES ('SETTLEM_ACCEPTED', 'SETTLEM_REJECTED'),
  PARTITION APPLICATIONS
    VALUES ('SINGLE_PARENT_SUPP','UNEMPLOYMENT_SUPP','CHILD_SUPP','HOUSING_SUPP')    
)
;

If you later would like to add a partition, it is as simple as

ALTER TABLE RECEIVED_DOCUMENTS
 ADD PARTITION APPLICANT VALUES ('FAMILY','SINGLE_PARENT','ASYLUM_SEEKER')
;

Similarly, dropping a partition with its subpartition would be done with:

ALTER TABLE RECEIVED_DOCUMENTS
DROP PARTITION APPLICATION;

Notice that if you have a default partition to handle incoming data that doesn't fit in any particular partition, you will get an error when attempting to add a partition.

Sources:

"Specifying Subpartition Templates to Describe Composite Partitioned Tables"

"Specifying a Subpartition Template for a *-List Partitioned Table"

How to remove the APEX option from the database

cd $ORACLE_HOME/apex
sqlplus / as sysdba
@apxremov.sql
drop public synonym htmldb_system;
drop PACKAGE HTMLDB_SYSTEM;

If you have older APEX installations, they may be left in the database, but not registred in the dba_registry.

I had an old APEX schema called APEX_030200, with several invalid objects:

OWNER	OBJECT_TYPE	COUNT(*)
PUBLIC	SYNONYM	3
APEX_030200	PACKAGE	2
APEX_030200	PACKAGE BODY	114
APEX_030200	PROCEDURE	3

This means that the script above won't work. You will get this output when attempting to remove the installation:

sqlplus / as sysdba @apxremov.sql
...
Error:
You can only use this script to remove Application Express

I found some useful information about these situations at this blog

Basically, you can simply drop the old schema directly.

Check first:

SELECT username, 'drop user ' || username || ' cascade;' AS remove_statement
  FROM dba_users
 WHERE     (username LIKE 'FLOWS_%' OR username LIKE 'APEX_%')
       AND username NOT IN ('FLOWS_FILES',
                            'APEX_PUBLIC_USER',
                            'APEX_LISTENER',
                            'APEX_REST_PUBLIC_USER',
                            'APEX_INSTANCE_ADMIN_USER')
       AND username NOT IN (SELECT schema s
                              FROM dba_registry
                             WHERE comp_id = 'APEX');

So cleaning up can be done as easily as this:

drop user APEX_030200 cascade;

Will interrupting a "split partition"-command cause any harm to your database?

Will interrupting a "alter table split partition"-command cause any harm to your database?

No, it will not. It is safe to kill the session. Oracle will be able to recover and the table would be left in its original state, prior to time when the "alter table split partition" command was executed.

I was recently in contact with Oracle support regarding such a case. My goal was to split a sub-partition, in order to accommodate rows for years 2017 to 2020, but there was way too much concurrent activities in the database to complete the "alter table split partition"-command.
The database was almost completely non-responsive and there was pressure to finish within a certain time frame.

The supporting engineer confirmed that there would be some rollback activities in the wake of this command, how much depended on the activity level in the database during the time when the DDL was executing.

He added the following explanation to how Oracle handles the split internally:

As for kill the split partition, the way it works internally is to create temporary segments for the 2 new partitions it is splitting into, and when the operation is complete, these new temporary segments are attached to the original table and become the new partitions. So the result of this is that it's perfectly safe to terminate the split partition, and it simply stops its work, drops the temporary segments, and you are left with the original table in-tact, so there will be no ill-effects of this.

How to create a common and a local user in a 12c multitenant database

In a multitenant container database, there are two types of users:

* Common users, who are known in both the root containers and in all the pluggable database containers
* Local users, who are only known in a single pluggable database container

Common users can, if granted the necessary privileges, perform administrative tasks across all the PDBs in multitenant database.
They can also perform tasks specific to the container database, also called the ROOT container.

To create a common user, make sure you are connected to the ROOT container

On my client, my tnsnames.ora entry looks as follows:

# container database
CDB =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.no)(PORT = 1531))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = "container#db01")
     )
   )

Since my client is a windows pc, I start sqlplus from the start menu, and connect as system@cdb and enter the password.
My default container will be CDB$ROOT, which is what you need to create a common user:

show con_name

CON_NAME
------------------------------
CDB$ROOT

Create the common user:

create user C##DBAMASTER
identified by ****
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
container=all;

SQL> user C##DBAMASTER created.

Note the new rules for creating common users in a CDB database:
In Oracle Database 12c Release 1 (12.1.0.1), the name of a common user must begin with C## or c##

Verify that the common user was created:

select USERNAME,CREATED,LAST_LOGIN,COMMON
from dba_USERS
where trunc(created) = trunc(sysdate)

USERNAME             CREATED   LAST_LOGIN                               COM
-------------------- --------- ---------------------------------------- ---
C##DBAMASTER         18-DEC-16 18-DEC-16 03.16.16.000000000 PM +01:00   YES

Give the common user the right to create a session, to switch between all containers in the CDB and the right to create and drop users in all the containers in the CDB:

grant connect to C##DBAMASTER;
grant set container to C##DBAMASTER container=all;
grant create user to C##DBAMASTER container=all;
grant drop user to C##DBAMASTER container=all;

SQL> Grant succeeded.

Connect to ROOT container as the new common user:

connect C##DBAMASTER/***@cdb
Connected.
show user
USER is "C##DBAMASTER"

Switch container:

alter session set container=vpdb01;

SQL> Session altered.

Create a new local user in the current container:

create user "1Z0061"
identified by ****
default tablespace users
quota unlimited on users
temporary tablespace TEMP
container=current;

SQL> User created.

Note that without the "container=all" privilege, the new common user C##DBAMASTER cannot connect directly to the vpdb01 pluggable database

Here is what happened:

SQL> connect C##DBAMASTER/****@vpdb01
ERROR:
ORA-01045: user C##DBAMASTER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect system/*****@vpdb01
Connected.
SQL> show con_name

CON_NAME
------------------------------
VPDB01
SQL> grant create session to C##DBAMASTER container=current;

Grant succeeded.

SQL> connect C##DBAMASTER/****@vpdb01
Connected.
SQL> show con_name

CON_NAME
------------------------------
VPDB01

This means that these two privileges are distinctly different:

grant create session to C##DBAADMIN container=all;

vs

grant set container to C#DBAADMIN container=all;