How to find users in the database with default passwords

select d.username,account_status
from dba_users_with_defpwd d inner join dba_users u
on d.username = u.username
where u.account_status = 'OPEN';
exit

Output in my case:

USERNAME                                 ACCOUNT_STATUS
---------------------------------------- --------------------
CTXSYS                                   OPEN
MGDSYS                                   OPEN

These accounts should have their password changed for security reasons.

List temporary tablepaces and their tempfiles

select t.file#,ts#,status,t.name "temp file name",n.name "tablespace name", n.bigfile,t.bytes/1024/1024 "Size MB"
from v$tempfile t join v$tablespace n
using(ts#);

FILE#	TS#	STATUS	temp file name	tablespace name	BIGFILE	Size MB
1	3	ONLINE	/u02/oradata/proddb01/temp01.dbf	TEMP	YES	256
2	66	ONLINE	/u02/oradata/proddb01/user_temp.dbf	USER_TEMP	YES	256
3	70	ONLINE	/u02/oradata/proddb01/temp_d1u_01.dbf	BATCH_TEMP	YES	256

How to manually set the date and time on an AIX server

Old, incorrect date:

[root@myserver:/] date
Wed Jul 12 10:11:36 CEST 2017

Change it to the current date:

[root@myserver:/] date 010510152017
Thu Jan  5 10:15:31 CET 2017

Where

01=month
05=day
1015=time and minute
2017=year


Let's check it, immediately after we have set it:

[root@myserver:/] date
Thu Jan  5 10:16:27 CET 2017

The ntpd deamon has already adjusted the time slightly.
Note that ntpd will only adjust the server time if the local time of the server is within 1000 sec (16 minutes) of the current time. From the xntpd man pages:


Note: When operating in a client mode, the xntpd daemon will exit with an error if no configured servers are within 1000 seconds of local system time. Use the date or ntpdate command to set the time of a bad skewed system before starting xntpd.

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"