user ddl

set head off
REM set long 150
set linesize 150
set longchunksize 150
set pages 0
set long 99999999
set feedback off
set echo off
spool /home/oracle/user_MIGRATE.sql
SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) || ';' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists (select 'x' from dba_role_privs drp where drp.grantee=upper('USERNAME'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists (select 'x' from dba_sys_privs drp where drp.grantee=upper('USERNAME'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists(select 'x' from dba_tab_privs drp where drp.grantee=upper('USERNAME'))
/
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';'from DBA_ROLE_PRIVS
/
spool off


ddl username

createUser.sql

set pages 800 lines 300  long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual 

creation of partition table

SQL> CREATE TABLE “SH”.”PART_TABLE”
2     (       “PARAMETER” VARCHAR2(32) NOT NULL ENABLE,
3     “TIMESTAMP” NUMBER NOT NULL ENABLE,
4     “VALUESUM” NUMBER NOT NULL ENABLE,
5     “VALUECOUNT” NUMBER DEFAULT 1 NOT NULL ENABLE,
6      CONSTRAINT “PK_INDEX01? PRIMARY KEY (“PARAMETER”, “TIMESTAMP”) ENABLE,
7      CONSTRAINT “VALUECOUNT_POS” CHECK ( valuecount > 0 ) ENABLE
8     ) ORGANIZATION INDEX COMPRESS 1 PCTFREE 10 INITRANS 2 MAXTRANS 255  LOGGING
9    TABLESPACE “TS1?
10    STORAGE(
11    BUFFER_POOL DEFAULT)
12   PCTTHRESHOLD 50
13    PARTITION BY RANGE (“TIMESTAMP”)
14   (PARTITION “PART_1?  VALUES LESS THAN (1277596800001)
15    PCTFREE 10 INITRANS 2 MAXTRANS 255
16    STORAGE(INITIAL 50331648 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18    TABLESPACE “TS1? );

Table created.

SQL> select segment_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select partition_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select table_name,tablespace_name from dba_tables where table_name=’PART_TABLE’;

TABLE_NAME                     TABLESPACE_NAME
—————————— ——————————
PART_TABLE

SQL> select partition_name,tablespace_name from dba_tab_partitions where partition_name=’PART_1';

PARTITION_NAME                 TABLESPACE_NAME
—————————— ——————————
PART_1

SQL> select index_name,partition_name,subpartition_count,status from dba_ind_partitions where tablespace_name=’TS1';

INDEX_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT STATUS
—————————— —————————— —————— ——–
PK_INDEX01                     PART_1                               0 USABLE

SQL> select owner,table_name,index_name,status from dba_indexes where index_name=’PK_INDEX01';

OWNER      TABLE_NAME                     INDEX_NAME              STATUS
———- —————————— ——————————
SH         PART_TABLE                     PK_INDEX01              N/A

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1' group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             1

SQL> ALTER TABLE SH.PART_TABLE ADD
2    PARTITION “PART_2?  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1? ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1' group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             2

Insert some  data

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971

$exp SH/SH@RACDB file=part3.dmp log=part3_exp.log tables=SH.PART_TABLE:PART_2 feedback=10000 statistics=none constraints=n

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: constraints on tables will not be exported

About to export sSHcified tables via Conventional Path …
. . exporting table PART_TABLE
. . exporting partition              PART_2
…………………………………………………………………
…..
809310 rows exported
Export terminated successfully without warnings.

SQL> alter table SH.PART_TABLE drop partition PART_2;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

==================================================================================================================================

sql>select Period,count(1) from arch_fct.sc_products_sum where period between '01-JAN-2001' and '01-DEC-2005' GROUP by period;

==================================================================================================================================

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

SQL>    alter table SH.PART_TABLE add
2    PARTITION “PART_2?  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1? ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

$ imp SH/SH@RACDB file=part3.dmp log=part3_imp.log fromuser=SH buffer=200000 recordlength=6400 tables=PART_TABLE:PART_2 feedback=10000 ignore=y constraints=n statistics=none

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SH, not by you

import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SH’s objects into SH
. . importing partition “PART_TABLE”:”PART_2?
…………………………………………………………………
…..
809310 rows imported
Import terminated successfully without warnings.

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971






command to move table, index, table partition, index partition of owner = MSC
o/p like
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_0 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_1023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_2023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_SUPPLIES move partition ALLOC_SUPPLIES_0 tablespace APPS_TS_TX_DATA;


SQL> create table test1 tablespace test as select *from dict ;

Table created.

SQL> create table test2 tablespace test as select *from cat ;

Table created.

SQL> create table test3 tablespace test as select *from cat ;




select partition_name from dba_tab_partitions where tablespace_name='COST_REV_FACT_DATA' and table_name='PA_UNBLLD_UNRND_FCT';

select partition_name,owner,segment_name from dba_segments where tablespace_name='COST_REV_FACT_DATA';







select partition_name, high_value
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;


select partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name='TEST_TBL'
order by partition_name, subpartition_position;




http://gavinsoorma.com/2015/03/oracle-12c-partitioning-new-features/


SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS_PART';


We can see that for the partitions where indexing is not enabled, the index has been created as UNUSABLE.

SQL> SELECT PARTITION_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME='SALES_12C_IND';




Find if you need to add new partitions
                                                 Last update (2010-07-09 21:11:28)
                                                                                                                   Date added (2008-02-29 08:44:19)
Summary
If you have partitions in your database it is very common in the future to need a method knowing in which partition you are currently inserting data and if this is the last available partition. If yes, then you must add very soon new partitions to the table.

In this example partitions have been setup for tables in AX, AR, GL schemas of an E-Business Suite ERP. The algorith is very simple. It checks if rows exist in the last partition of the table, if yes then it creates the ddl sql to add the new partition.

Connect as sys from sqlplus and set
sql>set serveroutput on

DECLARE
CURSOR c1 IS
SELECT 'select count(*) from '|| a.table_owner || '.' || a.table_name ||' partition('||a.partition_name||')' s1,a.*
FROM dba_tab_partitions a,dba_part_tables b
WHERE a.table_owner IN ('AX','AR','GL')
AND a.table_name NOT LIKE 'HZ%'
AND a.table_name NOT LIKE 'AX_DOC%'
AND a.partition_position = b.partition_count
AND b.owner = a.table_owner
AND b.table_name = a.table_name;

CURSOR c2(v_owner VARCHAR2,v_tname VARCHAR2) IS
SELECT high_value,partition_name
   FROM (SELECT * FROM dba_tab_partitions
             WHERE table_owner = v_owner 
             AND   table_name  = v_tname
         ORDER BY partition_position DESC) WHERE ROWNUM <3;


CURRENT_MAX_VALUE_NUMBER NUMBER;
CURRENT_MAX_VALUE_NUMBER2 NUMBER;
v_statement   VARCHAR2(200);
v_rec        dba_tab_partitions%ROWTYPE;
v_oldtname   dba_tab_partitions.table_name%TYPE := NULL;
v_maxhvalue  dba_tab_partitions.high_value%TYPE;
v_minhvalue  dba_tab_partitions.high_value%TYPE;
v_newhvalue  dba_tab_partitions.high_value%TYPE;
v_newpname   dba_tab_partitions.partition_name%TYPE;
BEGIN
FOR i IN c1 LOOP
v_statement           := i.s1;
    EXECUTE IMMEDIATE v_statement INTO CURRENT_MAX_VALUE_NUMBER;
    IF CURRENT_MAX_VALUE_NUMBER > 0 THEN
   FOR j IN c2(i.table_owner,i.table_name) LOOP
       IF i.table_name = v_oldtname THEN
      v_minhvalue := j.high_value;
   ELSE
      v_maxhvalue := j.high_value; 
   END IF;
   v_oldtname := i.table_name;
   END LOOP;
   v_newhvalue := TO_NUMBER(v_maxhvalue) + (TO_NUMBER(v_maxhvalue)-TO_NUMBER(v_minhvalue));
       v_newpname  := SUBSTR(i.partition_name,1,LENGTH(i.partition_name)-3)||
       LTRIM(TO_CHAR(SUBSTR(i.partition_name,LENGTH(i.partition_name)-2)+1,'099'));
       DBMS_OUTPUT.PUT_LINE('The table '|| i.table_owner || '.'|| i.table_name||' needs partition');
   DBMS_OUTPUT.NEW_LINE();
       DBMS_OUTPUT.PUT_LINE('Alter table '||i.table_name||' add partition '||v_newpname ||
       ' VALUES LESS THAN ('||v_newhvalue||')');
   DBMS_OUTPUT.PUT_LINE(' LOGGING NOCOMPRESS TABLESPACE '||i.tablespace_name);
   DBMS_OUTPUT.PUT_LINE(' PCTFREE '||i.pct_free);
   DBMS_OUTPUT.PUT_LINE(' INITRANS '||i.ini_trans||' MAXTRANS '||i.max_trans);
   DBMS_OUTPUT.PUT_LINE(' STORAGE    (INITIAL '||i.initial_extent);
   DBMS_OUTPUT.PUT_LINE(' NEXT '||i.next_extent);
   DBMS_OUTPUT.PUT_LINE(' MINEXTENTS '||i.min_extent);
   DBMS_OUTPUT.PUT_LINE(' MAXEXTENTS '||i.max_extent);
   DBMS_OUTPUT.PUT_LINE(' PCTINCREASE '||i.pct_increase);
   DBMS_OUTPUT.PUT_LINE(' BUFFER_POOL      DEFAULT)');
   DBMS_OUTPUT.NEW_LINE();
    END IF;
END LOOP;
END;
Tip:It works for partitions based on NUMBER columns, not DATE

To check the parition value

select a.table_owner, a.table_name, a.partition_name, a.high_value from dba_tab_partitions a
where a.table_name in ('DAILY_INVENTORY_FACTS')
and partition_name in (select max(partition_name) from dba_tab_partitions b
    where b.table_owner = a.table_owner  and b.table_name = a.table_name )
order by a.table_name;

___________________________________________________________________

Create on temp machine.


CREATE TABLE DBMON.TEST_PARTITION_TEMP AS
(
SELECT 1 AS PARTITION_POSITION, TABLE_OWNER, TABLE_NAME, SUBSTR(PARTITION_NAME, 1, (LENGTH(PARTITION_NAME)-3))    AS PARTITION
_NAME,
TO_NUMBER(SUBSTR(PARTITION_NAME, (LENGTH(PARTITION_NAME)-2),3)) AS PARTITION_NUMBER, PARTITION_NAME AS VALIDATION,
TO_LOB(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_TAB_PARTITIONS
WHERE
( TABLE_NAME,PARTITION_POSITION ) IN
(
SELECT    TABLE_NAME, MAX(PARTITION_POSITION)
FROM DBA_TAB_PARTITIONS
WHERE
TABLE_OWNER = 'EDIADMIN'
GROUP BY TABLE_NAME
)
);



CREATE TABLE DBMON.TEST_PARTITION AS
SELECT PARTITION_POSITION, TABLE_OWNER, TABLE_NAME, PARTITION_NAME, PARTITION_NUMBER, VALIDATION,
TO_DATE(SUBSTR(HIGH_VALUE,10,11), 'YYYY-MM-DD') AS HIGH_VALUE
FROM DBMON.TEST_PARTITION_TEMP;



SELECT TEMP1.TABLE_OWNER, TEMP1.TABLE_NAME, TEMP1.PARTITION_NAME, TEMP1.HIGH_VALUE, TEMP2.HIGH_VALUE
FROM  DBMON.TEST_PARTITION TEMP1, DBMON.TEST_PARTITION_TEMP TEMP2
WHERE
TEMP1.TABLE_NAME = TEMP2.TABLE_NAME
ORDER BY 2;

_________________________________________________________________________
prepare_daily_inventory_facts.sh

script to partitions.


#!/bin/ksh

if [[ $# -ne 1 ]]
then
print "Usage: prepare_daily_inventory_facts.sh  <days>"
print "Example: prepare_daily_inventory_facts.sh 1 \n"
exit -1
fi

typeset -i WEEK=$1
typeset -i x=0
SCRIPT_FILE=daily_inventory_facts.sql

cat header.txt > ${SCRIPT_FILE}

while [[ $x -ne $WEEK ]];
do

##################################
# Create the partitions
#
sqlplus /nolog @prepare_daily_inventory_facts.sql > prepare_daily_inventory_facts.sql.out

cat prepare_daily_inventory_facts.sql.out | grep  "ALTER"  | grep -v TABLE_OWNER >> ${SCRIPT_FILE}
print "Partitions for day: ${x} as added."

x=$x+1


done

cat tail.txt >> daily_inventory_facts.sql


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

prepare_daily_inventory_facts.sql

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;

SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME ||
       ' ADD PARTITION ' || PARTITION_NAME || TO_CHAR(PARTITION_NUMBER+1)  ||
       ' VALUES LESS THAN (TO_DATE(' || '''' || ' '|| to_char(HIGH_VALUE+1, 'YYYY-MM-DD HH24:MI:SS') || ''
'' ||
       ', ' || '''' || 'SYYYY-MM-DD HH24:MI:SS' || '''' || ', ' || '''' || 'NLS_CALENDAR=GREGORIAN' || '''
' || '));'
FROM DBMON.TEST_PARTITION
where TABLE_NAME = 'DAILY_INVENTORY_FACTS';

UPDATE DBMON.TEST_PARTITION SET PARTITION_NUMBER = PARTITION_NUMBER+1, HIGH_VALUE=HIGH_VALUE+1
WHERE TABLE_NAME = 'DAILY_INVENTORY_FACTS';

COMMIT;

quit

______________________________________________________________________

header .txt

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;

_________________________________________________

tail.txt

quit;

_________________________-

daily_inventory_facts.sql  

ALTER TABLE DSSADMIN.DAILY_INVENTORY_FACTS ADD PARTITION DIF_PART05859 VALUES LESS THAN (TO_DATE(' 2019-07-23 00:00:0

0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

ALTER TABLE DSSADMIN.DAILY_INVENTORY_FACTS ADD PARTITION DIF_PART05860 VALUES LESS THAN (TO_DATE(' 2019-07-24 00:00:0

0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));