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. 🙂
Continue reading “[Oracle] ORA-01950 with REMAP_TABLESPACE – Solved!” →
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.
Continue reading “[JIRA] Lexorank integrity issues?” →
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.
Continue reading “[JIRA] Broken Permission Schemes? (better way)” →
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.
for i in (select * from tabs) loop
execute immediate ('drop table ' || i.table_name || ' cascade constraints');
‘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.
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.
However, with the following SQL it’s easy to exclude views and retrieve table columns only.
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! 🙂