Tips for TortoiseSVN

Many discussion on internet, think it is a SVN client. In fact, it is also a SVN server.

As I practice, I found SNV over WebDV has view only capability, even I tried websvn which can't help on check-in and check-out. I decided to use SVN protocol only, i.e. use TortoiseSVN serves as both server (deploy it on server) and client GUI (install it too in various PCs).

To clear authentication info, go to settings/saved data/clear authentication data, as shown below.

The key config files of SVN server are under the repository directory by default.e.g.

authorization file:  authz
password file: passwd
main config file:  svnserve.conf


First funny problem is when I enable the password file and hit authentication failed.

This is due to a space before the userid.   Remove it, do so for all other type of configuration just in case.

The 2nd Funny problem with authorization failed, whenever I enable authz-db in the server config file, I even can't read the repository , which was functioning.Finally I got the solution from someone else on the web.

I use the "/" for repository instead of particular repository name. e.g. change it from "/dbasvn" to "/" in authz-db file. e.g.

Changed it from 
[/dbasvn]
liqy = rw
* = r

TO

[/]
liqy = rw
* = r

Now , everything works perfectly.


Run svnserve as a Microsoft Windows service, so that we can use SVN protocol.  below is my sample command.

sc create svnserver binpath= "\"C:\Program Files\Subversion\bin\svnserve.exe\" --service -r D:\svnrepos" displayname= "Subversion" depend= Tcpip start= auto

Note if we want to delete a window service, can use "sc delete " , followed by reboot. 

Above is based TortoiseSVN v1.9.2 

SQL server 2000 profiler

One system is getting slower and slower, so I decided to use profiler for investigation.

Googled and read out the following useful articles:


http://serverfault.com/questions/162245/how-do-i-identify-slow-queries-in-sql-server
http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

to minimize changes to production, I select using tracing file instead of a table.

confirmed with below two articles for finding out indexes columns.

https://bytes.com/topic/sql-server/answers/562317-how-do-i-find-indexes-columns-sql-server-2000-a

http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db



Finally, I got a huge return -- the system has ZERO indexes created.

Play with MERGE partition command

The partition name and high value

The error message has self-explanation.

ALTER TABLE TEST_UNBILLED  

MERGE PARTITIONS P2014_02_01, P2014_02_07 INTO PARTITION P2014_02_07

UPDATE INDEXES;

Table altered.

ALTER TABLE TEST_UNBILLED  

MERGE PARTITIONS P2014_02_07, P2014_02_11 INTO PARTITION P2014

UPDATE INDEXES;

Table altered.

SYS> ALTER TABLE TEST_UNBILLED

  2  MERGE PARTITIONS P2014_02_14, P2014 INTO PARTITION P2014

  3  UPDATE INDEXES;

MERGE PARTITIONS P2014_02_14, P2014 INTO PARTITION P2014

                 *

ERROR at line 2:

ORA-14273: lower-bound partition must be specified first

Elapsed: 00:00:00.01

SYS> ALTER TABLE TEST_UNBILLED

  2  MERGE PARTITIONS P2014, P2014_02_14 INTO PARTITION P2014

  3  UPDATE INDEXES;

MERGE PARTITIONS P2014, P2014_02_14 INTO PARTITION P2014

                 *

ERROR at line 2:

ORA-14275: cannot reuse lower-bound partition as resulting partition

Elapsed: 00:00:00.01

when oracle listener listens on all netowrk interfaces?

Learned below from https://community.oracle.com/thread/2126662?start=0&tstart=0

When you use HOSTNAME in listener definition and the HOSTNAME matches the result of `hostname` command, the listener will listen on all interfaces.

If you use an IP in listener definition, the listener will listen on THAT interface only.


So here is my test  to confirm this is correct.


--the test server info


orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> hostname
orarac2poc
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> ping orarac2poc
PING orarac2poc (10.139.90.42) 56(84) bytes of data.
64 bytes from orarac2poc (10.139.90.42): icmp_seq=1 ttl=64 time=0.021 ms
64 bytes from orarac2poc (10.139.90.42): icmp_seq=2 ttl=64 time=0.027 ms

--- orarac2poc ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.021/0.024/0.027/0.003 ms



--ifconfig output


[root@orarac2poc ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C2
          inet addr:10.139.90.42  Bcast:10.139.255.255  Mask:255.255.0.0
          inet6 addr: fe80::250:56ff:fe9c:3dc2/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:29146147 errors:0 dropped:0 overruns:0 frame:0
          TX packets:20491711 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:33283609829 (30.9 GiB)  TX bytes:2225739822 (2.0 GiB)

eth0:1    Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C2
          inet addr:10.139.92.42  Bcast:10.139.255.255  Mask:255.255.0.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth0:2    Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C2
          inet addr:10.139.90.43  Bcast:10.139.255.255  Mask:255.255.0.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1      Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C1
          inet addr:10.139.91.42  Bcast:10.139.255.255  Mask:255.255.0.0
          inet6 addr: fe80::250:56ff:fe9c:3dc1/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:4225633 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3336 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:477256264 (455.1 MiB)  TX bytes:761551 (743.7 KiB)

eth2      Link encap:Ethernet  HWaddr 00:50:56:9C:1D:68
          inet addr:10.149.0.12  Bcast:10.149.255.255  Mask:255.255.0.0
          inet6 addr: fe80::250:56ff:fe9c:1d68/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:203522763 errors:0 dropped:0 overruns:0 frame:0
          TX packets:192410252 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:86546647557 (80.6 GiB)  TX bytes:143237765923 (133.4 GiB)

eth2:1    Link encap:Ethernet  HWaddr 00:50:56:9C:1D:68
          inet addr:169.254.231.131  Bcast:169.254.255.255  Mask:255.255.0.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:459889795 errors:0 dropped:0 overruns:0 frame:0
          TX packets:459889795 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:145470946165 (135.4 GiB)  TX bytes:145470946165 (135.4 GiB)


--use the IP of `hostname`


-- use physical IP

orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:34:41

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orarac2poc)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orarac2poc)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:34:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orarac2poc)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 :::1522                     :::*                        LISTEN
tcp        0      0 ::ffff:10.139.90.42:1522    ::ffff:10.139.90.42:48332   TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:35:42

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orarac2poc)(PORT=1522)))
The command completed successfully



We can see the LISTENER1 listeners on all IPs'  1522 port.

-- now use other ip


--Use virtual IP

orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:36:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.92.42)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.92.42)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:36:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.92.42)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 10.139.92.42:1522           0.0.0.0:*                   LISTEN
tcp        0      0 10.139.92.42:31765          10.139.92.42:1522           TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/adm


orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:37:44

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.92.42)(PORT=1522)))
The command completed successfully

We can see the LISTENER1 only listens the exact IP we specified. 

--more testing

orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:38:40

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.91.42)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.91.42)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:38:40
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.91.42)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 10.139.91.42:1522           0.0.0.0:*                   LISTEN
tcp        0      0 10.139.91.42:51369          10.139.91.42:1522           TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:39:50

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.91.42)(PORT=1522)))
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> vi listener.ora
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:40:23

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.149.0.12)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.149.0.12)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:40:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.149.0.12)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 10.149.0.12:1522            0.0.0.0:*                   LISTEN
tcp        0      0 10.139.91.42:58897          10.139.91.42:1522           TIME_WAIT
tcp        0      0 10.149.0.12:36482           10.149.0.12:1522            TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 16:47:15

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.149.0.12)(PORT=1522)))
The command completed successfully

Conclusion:

The finding is correct. Thanks.

Monitor metadata for a interval partition with a rolling window

We may hit below error when dropping a partition using interval range.

SQL> alter table orders2 drop partition p_second;
alter table orders2 drop partition p_second
                                   *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


As mentioned in "How to Use Interval Partitioning with a Rolling Partition Window and Avoid ORA-14758 (Doc ID 1590833.1)",

When you create an interval partitioned table, you must create at least one partition and specify the interval. This specified partition is created as a range partition; all future partitions that are automatically created by the database are interval
partitions. A known limitation is that you cannot drop the last range partition of an interval partitioned table. That means you cannot drop the first (last) partition that you specified when you created the table.
1. Enhancement Bug 17571086 : ENH: ALLOW DROP OF OLDEST PARTITION IN ROLLING WINDOW WHEN INTERVAL PARTITIONED is implemented in 12.2 and resolves this issue. This is a 12.2 feature and cannot be backported to lower
RDBMS versions.
2. Until 12.2 is released and your database is upgraded to 12.2, here is the description of and workaround to the issue:



Below experiment list down metadata changing over the maintenance.

1.  create the test table

create table orders2
(
invoice_no number not null,
invoice_date date not null,
comments varchar2(100)
)
partition by range (invoice_date)
interval (numtoyminterval(1,'month'))
(partition p_first values less than (to_date('01-Jan-2012','DD-MON-YYYY')) ,
 partition p_second values less than (to_date('01-Feb-2012','DD-MON-YYYY')) );

2. insert two test rows BUT not to commit

 Insert into orders2 values (150,'03-JAN-2012','test');

-- this follows into partition p_second

 Insert into orders2 values (150,'03-Feb-2012','test');
-- this created a new parttion

3. query the partitions

SQL>   select partition_name, partition_position, high_value from dba_tab_partitions where table_name='ORDERS2' order by partition_position;

PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
P_FIRST                                         1
TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

P_SECOND                                        2
TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P194                                        3
TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P194 is the last partition.

3. the current metadata
  CREATE TABLE "LIQY"."ORDERS2"
   ( "INVOICE_NO" NUMBER NOT NULL ENABLE,
"INVOICE_DATE" DATE NOT NULL ENABLE,
"COMMENTS" VARCHAR2(100 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("INVOICE_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
 (PARTITION "P_FIRST"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "P_SECOND"  VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;


However, in the metadata, the P_SECOND is still shown as last partition, while SYS_P194 not updated to the metadata.    P_SECOND is so called statically partition

4.   try to drop the STATICALLY last partition P_SECOND


alter table orders2 drop partition p_second;

SQL> alter table orders2 drop partition p_second;
alter table orders2 drop partition p_second
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


--commit the INSERT session now and try again



SQL> alter table orders2 drop partition p_second;
alter table orders2 drop partition p_second
                                   *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


5. use the workaround for DB prior to 12.2

5.1   reset it to normal range partition table and drop the partition

SQL> alter table orders2 set interval ();

Table altered.

SQL>  alter table orders2 drop partition p_second;

Table altered.


5.2 monitor the metadata again. Notice the metadata gets updated.


 CREATE TABLE "LIQY"."ORDERS2"
   ( "INVOICE_NO" NUMBER NOT NULL ENABLE,
"INVOICE_DATE" DATE NOT NULL ENABLE,
"COMMENTS" VARCHAR2(100 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("INVOICE_DATE") 
 (PARTITION "P_FIRST"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "SYS_P194"  VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;



SQL>   select partition_name, partition_position, high_value from dba_tab_partitions where table_name='ORDERS2' order by partition_position;

PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
P_FIRST                                         1
TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P194                                        2
TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA



6.1 change the partition back to interval
SQL>  alter table orders2 set interval (numtoyminterval(1,'month'));

Table altered.


6.2 monitor the metadata again

 CREATE TABLE "LIQY"."ORDERS2"
   ( "INVOICE_NO" NUMBER NOT NULL ENABLE,
"INVOICE_DATE" DATE NOT NULL ENABLE,
"COMMENTS" VARCHAR2(100 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("INVOICE_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
 (PARTITION "P_FIRST"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "SYS_P194"  VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;

install oracle sample data

Famous scott/tiger is here.

ORCL2:/u01/app/oracle/product/11.2.0.4/rdbms/admin> ls utlsampl.sql
utlsampl.sql


SQL> conn scott/tiger
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE


Install the sample schema installer files.
demo schemas are under $ORACLE_HOME/demo/schema directory.


DG:/u01/app/oracle/product/11.2.0.4/demo/schema/order_entry> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 19 15:11:28 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@DG> @oe_main

specify password for OE as parameter 1:
Enter value for 1: oe

specify default tablespeace for OE as parameter 2:
Enter value for 2: USERS

specify temporary tablespace for OE as parameter 3:
Enter value for 3: temp

specify password for HR as parameter 4:
Enter value for 4: hr

specify password for SYS as parameter 5:
Enter value for 5: ****

specify directory path for the data files as parameter 6:
Enter value for 6: /u01/app/oracle/product/11.2.0.4/demo/schema/order_entry

writeable directory path for the log files as parameter 7:
Enter value for 7: /u01/app/oracle/product/11.2.0.4/demo/schema/order_entry

specify version as parameter 8:
Enter value for 8: v3


User dropped.

old   1: CREATE USER oe IDENTIFIED BY &pass
new   1: CREATE USER oe IDENTIFIED BY oe

User created.

old   1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER oe DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS

User altered.

old   1: ALTER USER oe TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER oe TEMPORARY TABLESPACE temp

User altered.