Force drop user in Oracle database

If you want to drop a oracle user with the DROP USER statement to remove a database user and optionally remove the user’s objects you can execute ‘drop user @USERNAME cascade’. But if you have a problem to drop the user and you get the error “ORA-01940: cannot DROP a user that is currently logged in”. The problem is that the user is not connected according to v$session. I suspect that it’s related to my replication, and the user is a propagator. How do I get around this ORA-01940 error?

When you drop a user, Oracle Database also purges all of that user’s schema objects from the recycle bin.


DECLARE
open_count integer;
BEGIN
-- prevent any further connections
EXECUTE IMMEDIATE 'alter user @USERNAME account lock';
--kill all sessions
FOR session IN (SELECT sid, serial#
FROM v$session
WHERE username = '@USERNAME')
LOOP
-- the most brutal way to kill a session
EXECUTE IMMEDIATE 'alter system kill session ''' || session.sid || ',' || session.serial# || ''' ';
END LOOP;
-- killing is done in the background, so we need to wait a bit
LOOP
SELECT COUNT(*)
INTO open_count
FROM v$session WHERE username = '@USERNAME';
EXIT WHEN open_count = 0;
dbms_lock.sleep(0.5);
END LOOP;
-- finally, it is safe to issue the drop statement
EXECUTE IMMEDIATE 'drop user @USERNAME cascade';
END;

Java – Show System Properties

You can use Properties to configure your java application. The Java platform itself uses System Properties to maintain its own configuration. With the java.lang.System class you can access the Properties object that describes the configuration of the java environment. In System Properties you can find information about the current user, the current version of the Java runtime, the CPU and the character used to separate components of a file path name. To list all System Properties you can use the following snippet.


Iterator iterator = System.getProperties().keySet().iterator();
while (iterator.hasNext())
{
String key = (String)iterator.next();
String value = System.getProperties().getProperty(key);
System.out.println (key + " " + value);
}

On my machine the result looks like this:

java.vm.specification.vendor Oracle Corporation
user.variant
os.name Windows 10
sun.jnu.encoding Cp1252
java.library.path C:\Tools\jre\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Users\jentsch\bin;C:\Program Files\Git\mingw64\bin;C:\Program Files\Git\usr\local\bin;C:\Program Files\Git\usr\bin;C:\Program Files\Git\usr\bin;C:\Program Files\Git\mingw64\bin;C:\Program Files\Git\usr\bin;C:\Users\jentsch\bin;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\app\oracle\product\11203\client64\bin;C:\app\oracle\product\11203\client32\bin;C:\ProgramData\Oracle\Java\javapath;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0;C:\Program Files\Intel\WiFi\bin;C:\Program Files\Common Files\Intel\WirelessCommon;C:\Program Files (x86)\CheckPoint\Endpoint Security\Endpoint Common\bin;C:\Program Files\PuTTY;C:\Android;C:\Windows\System32;C:\Program Files\TortoiseGit\bin;C:\Users\jentsch\.dnx\bin;C:\Program Files\Microsoft DNX\Dnvm;C:\Program Files\nodejs;C:\Program Files\TortoiseSVN\bin;C:\Users\jentsch\AppData\Local\Microsoft\WindowsApps;C:\Program Files\apache-maven-3.2.5\bin;C:\Program Files\Java\jdk1.8.0_141\bin;C:\Program Files\Microsoft VS Code\bin;C:\Toolsl\Gource\cmd;C:\Tools\ffmpeg-3.4.1-win64-static\bin;C:\Users\jentsch\AppData\Roaming\npm;C:\Program Files\Git\usr\bin\vendor_perl;C:\Program Files\Git\usr\bin\core_perl;.
java.specification.name Java Platform API Specification
java.class.version 52.0
sun.management.compiler HotSpot 64-Bit Tiered Compilers
os.version 10.0
user.home C:\Users\jentsch
user.timezone Europe/Berlin
java.awt.printerjob sun.awt.windows.WPrinterJob
file.encoding Cp1252
java.specification.version 1.8
java.class.path drivers\derby-10.14.2.0.jar;drivers\derbyclient-10.14.2.0.jar;drivers\h2-1.4.197.jar;drivers\hsqldb-2.4.0.jar;drivers\jtds-1.3.1.jar;drivers\mariadb-java-client-2.2.4.jar;drivers\mssql-jdbc-6.4.0.jre7.jar;drivers\postgresql-42.2.2.jre6.jar;drivers\sqlite-jdbc-3.20.1.jar
user.name jentsch
java.vm.specification.version 1.8
sun.java.command org.test.commandline.Main migrate
java.home C:\Tools\jre
sun.arch.data.model 64
user.language en
java.specification.vendor Oracle Corporation
user.language.format de
awt.toolkit sun.awt.windows.WToolkit
java.vm.info mixed mode
java.version 1.8.0_172
java.ext.dirs C:\Tools\jre\lib\ext;C:\Windows\Sun\Java\lib\ext
sun.boot.class.path C:\Tools\jre\lib\resources.jar;C:\Tools\jre\lib\rt.jar;C:\Tools\jre\lib\sunrsasign.jar;C:\Tools\jre\lib\jsse.jar;C:\Tools\jre\lib\jce.jar;C:\Tools\jre\lib\charsets.jar;C:\Tools\jre\lib\jfr.jar;C:\Tools\jre\classes
java.vendor Oracle Corporation
file.separator \
java.vendor.url.bug http://bugreport.sun.com/bugreport/
sun.io.unicode.encoding UnicodeLittle
sun.cpu.endian little
sun.desktop windows
sun.cpu.isalist amd64


This code snippet have been written for JDK 8. Examples and practices described in this page don’t take advantage of improvements introduced in later releases.

Rename git branch locally and remotely

In case you have created a branch with a wrong name and pushed your branch to the remote git repository you can follow these steps to change the name of your branch.

git branch -m old_branch new_branch # Rename branch locally
git push origin :old_branch # Delete the old branch
git push –set-upstream origin new_branch # Push the new branch, set local branch to track the new remote

Please note, if you use the name of the branch somewhere in your build scripts or somewhere else like SonarQube you have to change it there also.

Get rotation from its “natural” orientation on your android device

Up to API Level 7 you can use android.view.Display.getOrientation() to get the rotation of the screen from its default orientation. But this method was deprecated in API level 8.

But now you can use

getWindowManager().getDefaultDisplay().getRotation(‌​);

The returned value is:

  • Surface.ROTATION_0 (no rotation)
  • Surface.ROTATION_90
  • Surface.ROTATION_180
  • Surface.ROTATION_270
  • If your device has a naturally portrait screen, and the user has turned it on its side to go into a landscape orientation, the value returned here may be either Surface.ROTATION_90 or Surface.ROTATION_270 depending on the direction it was turned.

    With this simple line of code you can get the information about the screen orientation related to the default. This could be helpful, if you want to deal with your Sensor.

    Android Activity without UI

    Is it possible to create a Android activity without UI?

    Yes it is. Android provides a theme for this requirement. Add the following line to your AndroidManifest.xml and call finish() at the end of the Activity’s onCreate() method.


    android:theme="@android:style/Theme.NoDisplay"

    Theme_NoDisplay

    public static final class android.R.style – Added in API level 3
    int Theme_NoDisplay
    Default theme for activities that don’t actually display a UI; that is, they finish themselves before being resumed.

    Constant Value: 16973909 (0x01030055)

    How to know if the app goes to Background

    With API Level 14 – Android 4.0, there is a Method in Application which is called onTrimMemory. The onTrimMemory is executed by the system with different levels ( TRIM_MEMORY_COMPLETE, TRIM_MEMORY_MODERATE, TRIM_MEMORY_BACKGROUND, TRIM_MEMORY_UI_HIDDEN, TRIM_MEMORY_RUNNING_CRITICAL, TRIM_MEMORY_RUNNING_LOW, or TRIM_MEMORY_RUNNING_MODERATE). The interesting level is “TRIM_MEMORY_UI_HIDDEN”. This level can be used to know that the app is going to background.

    The following example is a demonstration of the Application.onTrimMemory Method:


    public class MyApplication extends Application {
    // ...
    @Override
    public void onTrimMemory(int level) {
    super.onTrimMemory(level);
    if (level == TRIM_MEMORY_UI_HIDDEN) {
    notifyAppInBackground();
    }
    }
    // ...
    }

    With this simple piece of code, you can check if your app is being sent to the background!

    How to Format cells in Excel for Android

    Not a snippet, but a needed Information when using Microsoft Android Calc

    1449826012329
    How to Format cells in Excel for Android. (Sceenshot made on a german device)

    Some more informations are available in the video at https://support.office.com/en-us/article/Video-Getting-started-with-Excel-for-Android-tablet-c5d5c135-0bc1-4aea-9b54-95a518dd0c11. Excel for Android is very similar to Microsoft Excel for Windows. But some differences can confuse sometimes. The short video shows you how to get up and running with Microsoft Calc for Android.

    Recompiling objects in oracle

    A pl/sql script to recompile all invalid objects in oracle. Simple but powerfull.

    DECLARE
    
       obj_name_    User_Objects.object_name%TYPE;
       obj_type_    User_Objects.object_type%TYPE;
       str_run_     VARCHAR2(200);
       cid_         INTEGER;
       ret_         INTEGER;
       CURSOR Invalid_Objects_ IS
          SELECT object_name, object_type
          FROM user_objects
          WHERE status = 'INVALID'
          ORDER BY object_type ASC;
    
    BEGIN 
       FOR Get_Rec_ IN Invalid_Objects_ LOOP
          BEGIN
             obj_name_ := Get_Rec_.object_name;
             obj_type_ := Get_Rec_.object_type;
             IF (obj_type_ = 'FUNCTION') THEN
                str_run_ := 'ALTER FUNCTION ' || obj_name_ || ' COMPILE';
             ELSIF (obj_type_ = 'JAVA SOURCE') THEN
                str_run_ := 'ALTER JAVA SOURCE "' || obj_name_ || '"
    COMPILE';
             ELSIF (obj_type_ = 'JAVA CLASS') THEN
                str_run_ := 'ALTER JAVA CLASS "' || obj_name_ || '"
    RESOLVE';
             ELSIF (obj_type_ = 'PACKAGE') THEN
                str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE';
             ELSIF (obj_type_ = 'PACKAGE BODY') THEN
                str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE
    BODY';
             ELSIF (obj_type_ = 'PROCEDURE') THEN
                str_run_ := 'ALTER PROCEDURE ' || obj_name_ || ' COMPILE';
             ELSIF (obj_type_ = 'TRIGGER') THEN
                str_run_ := 'ALTER TRIGGER ' || obj_name_ || ' COMPILE';
    
             ELSIF (obj_type_ = 'VIEW') THEN
                str_run_ := 'ALTER VIEW ' || obj_name_ || ' COMPILE';
             ELSIF (obj_type_ = 'MATERIALIZED VIEW') THEN
                str_run_ := 'ALTER MATERIALIZED VIEW ' || obj_name_ || '
    COMPILE';
             ELSIF (obj_type_ = 'DIMENSION') THEN
                str_run_ := 'ALTER DIMENSION ' || obj_name_ || ' COMPILE';
             ELSIF (obj_type_ = 'TYPE') THEN
                str_run_ := 'ALTER TYPE ' || obj_name_ || ' COMPILE';
             END IF;
             --
             cid_ := DBMS_SQL.OPEN_CURSOR;
             DBMS_SQL.PARSE(cid_, str_run_, DBMS_SQL.NATIVE);
             ret_ := DBMS_SQL.EXECUTE(cid_);
             DBMS_SQL.CLOSE_CURSOR(cid_);
          EXCEPTION
             WHEN OTHERS THEN
                BEGIN
                   IF (DBMS_SQL.IS_OPEN(cid_)) THEN
                      DBMS_SQL.CLOSE_CURSOR(cid_);
                   END IF;
                END;
          END;
    
       END LOOP; 
    END; 
    

    Source:
    http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/02/15/0382.htmy

    How To use landscape and reverseLandscape in AndroidManifest.xml

    If you develope a app or activity which is only available in landscape mode, you can enhance the app by adding the reverseLandscape mode (Landscape orientation in the opposite direction from normal landscape. Added in API level 9.) via editing the android:screenOrientation attribute of your activity in AndroidManifest.xml. Instead of using the “landscape” value, you can use “sensorLandscape” (Landscape orientation, but can be either normal or reverse landscape based on the device sensor. Added in API level 9.).

    
    
    

    This is a simple solution and your users will enjoy the possibility of using the device even if it is rotated by 180 degrees. The same solution is possible for portrait apps and activities. In this case you have to use sensorPortrait instead of sensorLandscape in AndroidManifest.xml.

    
    
    

    How to sort an ArrayList in Java

    If you want to sort any ArrayList in java, you need to make sure, your objects in the arrylist are comparable. This is done by using implements Comparable and implementing the abstract method compareTo (T cmp). For example if you want to sort some highscores you can do it like this.

    public class Highscore implements Comparable
    {
    
    	@Override
    	public int compareTo(Highscore cmp)
    	{
    		if (cmp.getAnzQuad() > getAnzQuad())
    		{
    			return -1;
    		} else {
    			return 1;
    		}
    	}
    
    	...
    
    public class Highscores
    {
    	....
    
    	public ArrayList getHighscores ()
    	{
    		ArrayList ret = new ArrayList();
    		...
    		// Fill the ret ArrayList with your highscores	
    
    		// Sort the highscores with Collections.sort
    		Collections.sort(ret);	
    		return ret;
    	}
    

    This is a simple, but powerfull solution to sort an ArrayList.