ORA-01031 when trying to alter user XS$NULL

Posted on December 10, 2012 by Matt • 0 Comments

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;
Filed Under: Oracle How-To

instance_groups and parallel_instance_group

Posted on June 18, 2012 by Matt • 0 Comments

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

Filed Under: Oracle Tip

Find all objects in a tablespace

Posted on June 11, 2012 by Matt • 0 Comments

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

Filed Under: Oracle How-To, Oracle Tip

Move and partitioned index to a new tablespace

Posted on July 18, 2011 by Matt • 0 Comments

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

Filed Under: Oracle How-To

Change a table’s default tablespace

Posted on July 18, 2011 by Matt • 0 Comments

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

Filed Under: Oracle How-To

Purge The DBA Recycle Bin

Posted on June 20, 2011 by Matt • 0 Comments

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

Filed Under: Oracle How-To

Remove non-ASCII characters from a column

Posted on June 14, 2011 by Matt • 0 Comments

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)) into… Continue Reading

Filed Under: Oracle How-To, Oracle Tip

Dynamically Move Partitions To A New Tablespace

Posted on June 8, 2011 by Matt • 0 Comments

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 new… Continue Reading

Filed Under: Oracle How-To

Dynamically Delete Old Partitions

Posted on June 8, 2011 by Matt • 0 Comments

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

Filed Under: Oracle How-To

Finding Large Directories

Posted on May 30, 2011 by Matt • 0 Comments

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 very… Continue Reading

Filed Under: DBA Thoughts, Oracle How-To, Oracle Tip