ORA-01031 when trying to alter user XS$NULL

I was trying to alter all of the tablespaces for all users currently using the “USERS” tablespace. The following SQL worked nicely for all but one user:

select 'ALTER USER '||username||' default tablespace USERS_01 quota unlimited on USERS_01;' 
from dba_users where DEFAULT_TABLESPACE = 'USERS';

This SQL dumped me the command necessary to move all users using the USERS tablespace to start using the USERS_01 tablespace. When I ran these statements I received the following error on one user

ALTER USER XS$NULL default tablespace USERS_01 quota unlimited on USERS_01; *
ERROR at line 1:
ORA-01031: insufficient privileges

To fix this you simply need to modify the default tablespace of the database with this SQL:

alter database default tablespace USERS_01;

instance_groups and parallel_instance_group

To find your RAC install’s instance_groups and parallel_instance_group use the following SQL: Col Name Format A33 Col Value Format A33 Select Inst_Id, Name, Value From Gv$Parameter Where Upper(Name) Like ‘%INST%GROUP%’ Order By Value, Inst_Id;Continue Reading

Find all objects in a tablespace

Do you need to find all the objects in an Oracle tablespace? Run this simple script to find the objects. select OWNER, SEGMENT_NAME from dba_segments where TABLESPACE_NAME = ‘ACME_FAILED_BIG1′ group by OWNER, SEGMENT_NAME;Continue Reading

Move and partitioned index to a new tablespace

To dynamically move a partitioned index to a new tablespace run the following SQL select ‘Alter index ‘||INDEX_OWNER||’.’||INDEX_NAME||’ rebuild partition ‘||PARTITION_NAME||’ tablespace CDR_DETAIL_INDEX1;’ from dba_ind_partitions where TABLESPACE_NAME = ‘DW_CDR_DETAIL’;Continue Reading

Change a table’s default tablespace

Do you need to change a table’s default tablespace? It’s pretty simple: ALTER TABLE {OWNER}.{TABLE NAME} MODIFY DEFAULT ATTRIBUTES TABLESPACE {NEW TABLESPACE NAME}; This does not move the table it only changes the attribute for the default tablespace. This is helpful if you are adding partitions, but not specifying the tablespace.Continue Reading

Purge The DBA Recycle Bin

Need to dump the DBA recycle bin? It’s pretty easy. As a DBA user run: purge dba_recyclebin;Continue Reading

Remove non-ASCII characters from a column

Do you need to remove special (non-ASCII) characters from a VarChar2 column in Oracle? Create this function: CREATE OR REPLACE FUNCTION O1DW.RECTIFY_NON_ASCII(INPUT_STR IN VARCHAR2) RETURN VARCHAR2 IS str VARCHAR2(2000); act number :=0; cnt number :=0; askey number :=0; OUTPUT_STR VARCHAR2(2000); begin str:=’^’||TO_CHAR(INPUT_STR)||’^’; cnt:=length(str); for i in 1 .. cnt loop askey :=0; select ascii(substr(str,i,1)) intoContinue Reading

Dynamically Move Partitions To A New Tablespace

Do you want to move partitions to a new tablespace in Oracle? Here is some helpful SQL: Find a partitioned table’s default table space: SELECT def_tablespace_name FROM dba_part_tables where owner = ‘O1DW’ and table_name = ‘ACME_FAILED_DETAIL’; Change a partitioned table’s tablespace: ALTER TABLE O1DW.ACME_FAILED_DETAIL MODIFY DEFAULT ATTRIBUTES TABLESPACE ACME_FAILED_BIG1; Move existing partitions to a newContinue Reading

Dynamically Delete Old Partitions

Are you looking to delete older partitions in an Oracle database? Here is a basic SQL query that will give you what you need. select ‘ALTER TABLE ‘||table_owner||’.’||table_name ||’ DROP PARTITION ‘ ||partition_name||’ UPDATE INDEXES;’ from dba_tab_partitions where table_owner = ‘O1DW’ and table_name = ‘ACME_FAILED_DETAIL’ order by PARTITION_POSITION; Modify to your specific needs.Continue Reading

Finding Large Directories

Recently I was looking to clear some space on one of my Oracle servers. I looked for large files with this command: find . -type f -size +100000k -exec ls -lh {} \; | awk ‘{ print $9 “: ” $5 }’ This looks for files over 100MB, but other than datafiles there were veryContinue Reading