[Oracle] ORA-01950 with REMAP_TABLESPACE – Solved!

Today I’ve been importing an Oracle database to a test server to copy a JIRA instance. I’m doing this after many years, because, at work what I would have normally done is submitting a ticket to DBAs. However, in this case, this is a personal database server I maintain for testing purposes, so I’m on my own. 🙂

[JIRA] Lexorank integrity issues?

So, this is loosely another part of the unofficial series ‘fixing a corrupted JIRA db‘.

However, this is a different case. This JIRA instance (7.1.x) was created by importing a huge XML backup (> 1 million issues). Due to an unknown reason, ActiveObjects didn’t import. That means, JIRA Software (aka JIRA Agile) no longer functioned normal.

[JIRA] Broken Permission Schemes? (better way)

Yesterday I blogged about how to fix JIRA Permission Schemes of a corrupted JIRA database. The observation was from atlassian-jira.log, but in my case I soon found out the problem was much greater in my case.

Oracle:: Drop all tables in a single shot

If you are into software development, you may have wanted to drop all tables of an Oracle database schema in a single shot. How do you do it? Here’s how I do it with a simple PL/SQL procedure.

begin
for i in (select * from tabs) loop
execute immediate ('drop table ' || i.table_name || ' cascade constraints');
end loop;
end;
/

‘tabs’ is actually an in-built view in Oracle. As explained in the follwing forum post, this PL/SQL block affects only the logged in user’s (schema) tables only.
https://community.oracle.com/message/10359255#10359255

Oracle :: How to query all table columns except views

As per the documentation provided in the following page, once you query ALL_TAB_COLUMNS, database views and view columns are also included. There’s nowhere specified whether the table is actually a table or a view.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

However, with the following SQL it’s easy to exclude views and retrieve table columns only.

select 
  table_name,
  column_name,
  data_type,
  data_length,
  column_id
from
  all_tab_columns
where
  owner = 'SCHEMA_NAME' and
  table_name not in (select view_name from all_views where owner = 'SCHEMA_NAME')
order by 1, 2, 3;

It saved my day! 🙂