среда, 12 июля 2017 г.

Косяки Java

1) switch реализован совершенно непонятно. От использования его без break и размещения default не в конце лучше воздержаться.

switch (1) {
    case 2: System.out.print("y");
    case 3: System.out.print("икраткое");
    default: System.out.print("x");
} //вполне ожидаемый х

switch (1) {
    default: System.out.print("x");
    case 2: System.out.print("y");
    case 3: System.out.print("икраткое");
} //внезапное xyикраткое

switch (1) {
    case 2: System.out.print("y");
    default: System.out.print("x");
    case 3: System.out.print("икраткое");
} // печальное xикраткое

Написать case с переменными невозможно – все варианты должны быть разрешены в момент компиляции.

2) Куча запутанных преобразований типов. Но всех переплюнуло уменьшение разрядности – неявное не работает, а явное наоборот, вместо того, что бы выкинуть ошибку сделает большое отрицательное число

int i = Short.MAX_VALUE - 10;
short s = i; //Ошибка компилятора: incompatible types: possible lossy conversion from int to short
short s = (short) (Short.MAX_VALUE + 1);
System.out.print("s=" + s + "; MAX_VALUE+1=" + (Short.MAX_VALUE+1));//s=-32768; MAX_VALUE+1=32768

3) Еще преобразование типов. Приведенный ниже пример конечно не компилируется

short s1 = 0;
short s2 = 1;
short s3 = s1 + s2;
//Error:(6, 15) java: incompatible types: possible lossy conversion from int to short

потому что внезапно

System.out.println(((Object)(s1+s2)).getClass().getName());
//возвращает java.lang.Integer

4) Присваивание это еще и функция, которая возвращает правую часть. Не ошибись в if, не пропусти второе =

boolean b = false;
if (b = true) System.out.print("На самом деле я уже true, посмотри сам " + b);//На самом деле я уже true, посмотри сам true

5) Сравнение строк и StringBuilder – отдельная боль
вот, например, строки одинаковые, а конструкторы вызваны были разные

String s1 = "Я томат";
String s2 = new String("Я томат");
if (s1 == s2)
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' равны");
else
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' НЕ равны");

Рано печалилсь, для StringBuilder это не работает уже из коробки :)

StringBuilder s1 = new StringBuilder("Я томат");
StringBuilder s2 = new StringBuilder("Я томат");

if (s1 == s2)
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' равны");
else
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' НЕ равны");

А вот ударили монтировкой по голове – шнурки развязались

StringBuilder s1 = new StringBuilder("Я томат");
StringBuilder s2 = s1;
s2.append(" очень рад");

if (s1 == s2)
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' равны");
else
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' НЕ равны");
// Мы случайно поменяли и первую строку: По мнению джавы строки 'Я томат очень рад' и 'Я томат очень рад' равны. 

В общем сравнение со строками не совместимо, надо использовать equals

String s1 = "Я томат";
String s2 = " Я томат".trim();
if (s1 == s2)
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' равны");
else
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' НЕ равны");
//С томатами явная проблема: По мнению джавы строки 'Я томат' и 'Я томат' НЕ равны

if (s1.equals(s2))
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' эквальны");
else
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' НЕ эквальны");
//Хоть с эквальностью проблем нету)): По мнению джавы строки 'Я томат' и 'Я томат' эквальны

Но wtf, если вместо строк случайно оказался StringBuilder

StringBuilder s1 = new StringBuilder("Я томат");
StringBuilder s2 = new StringBuilder("Я томат");

if (s1.equals(s2))
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' эквальны");
else
    System.out.print("По мнению джавы строки '" + s1 + "' и '" + s2 + "' НЕ эквальны");
//По мнению джавы строки 'Я томат' и 'Я томат' НЕ эквальны

оказывается разрабочтики метод не реализовали (facepalm)

6) Унарный инкремент и декремент давно пора бы убрать. Польза только в синтаксисе for-цикла, а непонятности вносит много. Вот, например что выведет?

int i = 0;
while (i <= 3) {
    i = i++;
    System.out.println(i);
}

7) Реализация substring по-моему чудовищная, хотя с этих можно смириться.

8) Инициализация переменных. Она зависит от контекста

public class Main {
float gf;
int gi;
public static void main(String[] xxx) {
    int li;
    float lf;
    Main c = new Main();
    System.out.println("Global float default: " + c.gf + "; Global integer default " + c.gi);
    // Global float default: 0.0; Global integer default 0
    System.out.println("Local float default: " + lf + "; Local integer default " + li);
    // не компилируется, т.к. переменные не инициализированы
}
}

9) Игра Угадай-ка

public class Simple {
    public Simple() {/*Угадай, кто из нас конструктор*/}
    public void Simple() {/*Угадай, кто из нас конструктор*/}
    public static void main(String[] args) {}
}

10) Дурацкие типы для литералов

long x = 123;//Компилируется
System.out.println(Integer.MAX_VALUE);//Выводит 2147483647
long x = 2147483999; //не компилируется Error:(6, 10) java: integer number too large: 2147483999
long x = 2147483999L;//Должно быть так

все потому что литерал по-умолчанию имеет тип int

11) “Чудесно” работающая процедура поиска по несортированным массивам

int numbers[] = new int[] {3,2,1};
System.out.println(Arrays.binarySearch(numbers, 2)); //правильное 1
System.out.println(Arrays.binarySearch(numbers, 3)); //странное -4

12) Protected access – вообще отдельная песня. Кажется, что понять скомпилируется код или нет можно уже только постфактум. Примера не привожу ибо длинно и непонятно.

13) Использование пустых ссылок не всегда приводят к ошибкам:

public class Main {
    static int x;
public static void main(String[] xxx) {
    Main m = new Main();
    System.out.println("Static var = " + m.x);//0, инициализирована которым по-умолчанию
    m = null;
    System.out.println("Static var via null link = " + m.x);//и тут 0
}

14) By reference or by value? Или все таки By reference? Вроде как заявляется, что параметры передаются By Value, т.е. изменение параметра внутри функции не влияет на переменную, которая была между скобками в вызывающем коде. Никаких спец конструкций, вроде NOCOPY или by reference в Java нет.
Вот небольшой примерчик:

static void byValueOrByReference(StringBuilder s1, String s2, String s3, String[] many_s) {// а попробуй угадай, что тут по ссылке, а что по значению :)?
    s1.append("но");
    s2 = s1 + "но";
    s3 = s2 + "но";
    for(String s: many_s) {
        s = s + "нейшество";
    }
}

public static void main(String[] xxx) {
    StringBuilder s1 = new StringBuilder("Гов");
    String s2 = "гов";
    String s3 = new String("New гов");
    String[] sarr = {"гов", "нище"};

    byValueOrByReference(s1, s2, s3, sarr);

    System.out.println(s1);//Говно
    System.out.println(s2);//гов
    System.out.println(s3);//New гов -- не смотря на то, что они обещали обычный объект
    System.out.println(sarr[0] + "-" + sarr[1]);//гов-нище
}

Допустим объяснение, что примитивы должны вести себя как-будто их передают по значению, а объекты по ссылке. Но что же со строками, которые объявлены с new и просто? Они по разному вели себя в сравнении, а тут опять выдают себя за примитивы? Непоследовательно как-то…

15) Цепочки периодов работаю немного по разному

public static void main(String[] xxx) {
    LocalDate d = LocalDate.of(2017, 1, 1);

    Period p1 = Period.of(0,0,0);
    p1 = p1.plusYears(1).plusMonths(1).plusDays(1);

    Period p2 = Period.ofYears(1).ofMonths(1).ofDays(1);

    System.out.println(d.plus(p1));//2018-02-02 - как и просили добавился день, месяц и год
    System.out.println(d.plus(p2));//2017-01-02 полнейшая печаль
}

В первом случае создаем пусто период и пользуемся функциями plus* - все работает как часы. А вот во втором, более коротком варианте добавляется только последний вызов. Лучше бы исключение кидали.

16) C StringBuilder.substring нельзя делать цепочки вызовов

StringBuilder s = new StringBuilder("опаньки");
System.out.println(s.insert(0,"ж").substring(0,2).append("пища"));// не компилируется
System.out.println(s.insert(0,"ж").append("пища"));// компилируется

17) Волшебная перезагрузка статических методов, вызов которых работает совсем не так, как в перегружегнных обычных.
В примере ниже: из дочернего объекта вызываем процедуру печати. В первом случае перегруженную, во втором родную.
Процедура печати почти одинаковая – дергает статический и нестатический метод из ?своего? класса. Смотрим на результаты

class Parent {
    static String stat() {return "СТАТИЧЕСКИЙ Parent";}
    String nonStat(){return "Parent";}
    void printInherited(){
        System.out.println("Вызов статической процедуры из Parent вызвал: " + stat());
        System.out.println("Вызов НЕстатической процедуры из Parent вызвал: " + nonStat());
        }
        }
public class Main extends Parent{
    static String stat() {return "СТАТИЧЕСКИЙ Child";}
    String nonStat(){return "Child";}
    void printLocal(){
        System.out.println("Вызов статической процедуры из Child вызвал: " + stat());
        System.out.println("Вызов нестатической процедуры из Child вызвал: " + nonStat());
    }
    public static void main(String... args) {
        Main m = new Main();
        System.out.println("Вызываем процедуру печати из родительского класса. Она наследовалась и не перегружалась");
        m.printInherited();
        System.out.println("Вызываем процедуру печати из дочернего класса");
        m.printLocal();
    }
}
Вызываем процедуру печати из родительского класса. Она наследовалась и не перегружалась
Вызов статической процедуры из Parent вызвал: СТАТИЧЕСКИЙ Parent
Вызов нестатической процедуры из Parent вызвал: Child

Вызываем процедуру печати из дочернего класса
Вызов статической процедуры из Child вызвал: СТАТИЧЕСКИЙ Child
Вызов нестатической процедуры из Child вызвал: Child

Нижняя часть с вызовом для дочернего класса прошла без неожиданностей – обе процедуры дернулись из самого дочернего класса. В верхней части волшебство – поведение то разное. Статический метод вызвался из одного места, а обычный из другого…

To be continued…

dbms_stats.import_table_stats

Если процедура импорта статистики dbms_stats.import_table_stats долго думала и в итоге ничего не сделала, возможно следует проапдейтить колонки C1 (название объекта) и C5 (имя схемы) таблицы статистики
Исследования тут

понедельник, 10 июля 2017 г.

Философия performance tuning

Из Expert Oracle SQL: Optimization and Deployment
1. Давать стабильное, предсказуемое, гарантируемое время выполнения повторяющихся запросов. Все изменения в оптимизаторе делают его всё более непредсказуемыми. Тут автор не учитывает генерируемые различными тулами запросы, которые так же должны выполняться быстро
2. Парадокс Dave Ensor: Единственный момент когда безопасно собирать статистику – когда это ничего не изменит. У сбора статистики может быть 2 исхода: ничего не поменяется и мы этим ничего не сломаем или что-то поменяется, но мы не знаем в лучшую или худшую сторону. Это unpredictable, см. пункт первый.
3. Причина, по которой мы собираем статистику в PROD – предотвратить изменение планов запросов, а не убедиться, что они поменяются. Пример – time-based таблицы, в которых без сбора статистики предикаты рано или поздно начнут превышать HIGH_VALUE и Oracle будет ожидать возврат 1 строки. Сбор статистики (а также удаление HIGH_VALUE или, всвязи с указанным автором изменением поведения в 12с, установка HIGH_VALUE в очень большое значение) предотвращает изменение планов.
4. Правильная версия парадокса David Ensor: правильное время для сбора статистики, когда это предотвратит изменение планов
5. Подход Wolfgang Breitling: для того, что бы получить оптимальный план, необходимо и достаточно дать CBO правильные cardinality. CBO будет ошибаться, только если кардинальность различается в несколько порядков
6. Bind variables and histograms should be considered mutually exclusive
7. Средства для стабилизации планов: Outline, SQL Profile (почему-то автор считает, что ошибочно), Baseline
8. Две стороны одной монеты: stability и adaptability. Например в baseline stability это сами baseline, adaptability - возможность планов меняться.
9. TSTATS – запретить планам меняться (stability) без использования каких бы то ни было репозиториев хранения, а корректировкой статистики на таблицах. В основе лежит идея Adrian Billington удаления time-based данных из статистики. Остальные идеи
- сбор статистики на специально сэмулированной тестовой среде с последующим переносом на прод. Такие среды следует использовать для тестирования планов запросов
- Полная генерация статистики для темповых таблиц
- залочить статистику на всех системах для всех объектов
10. Управлять статистикой так же, как и исходным кодом: хранить в SVN, разворачивать вместе с приложением
11. Разные техники хорошо применимы для разных случаев: нужно поправить 1 запрос или несколько запросов. К техникам можно отнести: хинтование, переписывание запроса, физические изменения в базе (параметры, индексы и т.д.) корректировка статистики

вторник, 4 июля 2017 г.

Когда CBO берет информацию из data dictionary

Еще интересный факт всё из той же книги:

Normally, the inputs to the CBO’s estimating process are the object
statistics, system statistics, and initialization parameters. One
exception to this rule is the degree of parallelism specified in the
data dictionary for a table or index. The only other case that I know
of where the CBO uses data dictionary information, other than
statistics, is when it costs full table scans for partitions.

CBO считает стоимость FULL скана к партиции как число блоков / число партиций

понедельник, 3 июля 2017 г.

Generate create user statement

BEGIN
dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
END;
/

WITH usr AS ( 
  SELECT ',USER1,USER2,' usr FROM dual
  ),
statements(txt, username, ord) AS (  
  SELECT dbms_metadata.get_ddl('USER', username), username, 1 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%'
  UNION ALL
  SELECT dbms_metadata.GET_GRANTED_DDL('ROLE_GRANT', username), username, 2 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%' 
    AND EXISTS (SELECT NULL FROM dba_role_privs WHERE grantee = username)
  UNION ALL
  SELECT dbms_metadata.GET_GRANTED_DDL('SYSTEM_GRANT', username), username, 3 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%'
   AND EXISTS (SELECT NULL FROM dba_sys_privs WHERE grantee = username)
  UNION ALL
  SELECT dbms_metadata.GET_GRANTED_DDL('OBJECT_GRANT', username), username, 4 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%'
   AND EXISTS (SELECT NULL FROM dba_tab_privs WHERE grantee = username)
  )
SELECT txt
FROM statements
ORDER BY username, ord
;

среда, 14 июня 2017 г.

Hint philosophy

Again from Expert Oracle SQL: Optimization, Deployment, and Statistics

You should be asking yourself these sorts of questions:
• Do I know the reason why I need to hint this code? Why has the CBO picked a different plan to
the one I think is best?
• Do I know that hinting the code will actually make a positive difference, or am I making a
potentially incorrect assumption?
• Are there alternative approaches? Are these alternative approaches better or worse than the
use of hints?
• Do I understand what the hints that I plan to use do?
• Are the hints an appropriate and complete set?
• Will my colleagues understand what I have done and why?

Analytic sorts

If you take a 10032 trace you will see that the ROW_NUMBER analytic function uses what is known as a version 1 sort, which is a memory-intensive, insertion-based sort, whereas the FIRST_VALUE function uses a version 2 sort, which is apparently a fancy combination of a radix sort and a quick sort1 that requires much less memory and fewer comparisons.

From Expert Oracle SQL: Optimization, Deployment, and Statistics

среда, 31 мая 2017 г.

Prepare POI for Sygic

Sygic doesn’t support POI and routes in standard KML format.
To load POI to Sygic from google mymaps you have to
1. Export layers to KML format (or KMZ format – its just archive with KML plus icons for places). KML is just XML format
2. Process KML file with XSLT transfomation

<!-- longitude | latitude | name | address | phone | fax | web | email | short description | long description -->
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:df="http://www.opengis.net/kml/2.2">
  <xsl:output method="text" encoding="utf-8" />

  <xsl:param name="delim" select="'|'" />
  <xsl:param name="quote" select="'&quot;'" />
  <xsl:param name="break" select="'&#xA;'" />

  <xsl:template match="/">
    <xsl:apply-templates select="df:kml/df:Document/df:Folder/df:Placemark" />
  </xsl:template>

  <xsl:template match="df:Placemark">
    <xsl:value-of select="substring-before(substring-after(normalize-space(df:Point/df:coordinates), ','), ',')"/>|<xsl:value-of select="substring-before(normalize-space(df:Point/df:coordinates), ',')"/>|<xsl:value-of select="normalize-space(df:name)"/>|||||||<xsl:value-of select="normalize-space(df:description)"/>
    <xsl:if test="following-sibling::*">
      <xsl:value-of select="$break" />
    </xsl:if>
  </xsl:template>

</xsl:stylesheet>

I did it with Notepad++ text editor with XML Tools plugin

Next steps was taken from Sygic site
3. Download CSV to RUPI converter
4. Process file from step 2 with converter
5. Copy created upi/rupi files to Sygic/maps/import folder (create import folder if you don’t have such)

Unfortunately Sygic support told me, that they have no plans to support KML/KMZ format like free MapsMe do.

понедельник, 29 мая 2017 г.

How to read FILTER operation

Из книги Expert Oracle SQL: Optimization, Deployment, and Statistics

if you see a FILTER operation with more than one operand then the
second and subsequent operands (the subquery or subqueries) are
evaluated for each row returned by the first operand (the main query)

Merge and unnest

Опять из книги Expert Oracle SQL: Optimization, Deployment, and Statistics разница между MERGE и UNNEST

• View merging applies to inline views, factored subqueries, and data
dictionary views that appear as row sources in the FROM clause of an
enclosing query block. View merging is controlled by the MERGE and
NO_MERGE hints.

• Subquery unnesting relates to subqueries in the
SELECT list, WHERE clause, or anywhere else that Oracle may in the
future support. Subquery unnesting is controlled by the UNNEST and
NO_UNNEST hints

UNNEST – это про WHERE, SELECT и управляется при помощи UNNEST – NO_UNNEST hints

VIEW MERGING – это про FROM, управляется при помощи MERGE – NO_MERGE hints
Бывает 2 видов:
SIMPLE VIEW MERGING – heuristic transformation, т.е. применяется безусловно, но может быть отменена хинтами.
COMPLEX VIEW MERGING – cost based transformation, применяется, если subquery содержит DISTINCT или GROUP BY.

четверг, 18 мая 2017 г.

Nested loops в планах

NB:

In the case of NESTED LOOPS the estimated row count is per iteration
of the loop whereas the actual row count is for all iterations of the
loop.

Restoring objects statistics

From book Expert Oracle SQL: Optimization, Deployment, and Statistics I found new thing: you don’t need to export-import automatically gathered statistics. Instead of you can use DBMS_STATS.RESTORE* procedures.
By default oracle stores data for 31 days.
Quote from Chapter 9 of the book:

• There are several procedures for restoring statistics including DBMS_STATS.RESTORE_SCHEMA_STATS.
• User statistics set with DBMS_STATS.SET_xxx_STATS procedures are not restored. So, for example, any hand-crafted histogram would have to be reapplied after statistics are restored.
• Although this is normally the default behavior, it is good practice to explicitly invalidate any bad plans in the shared pool by using the NO_INVALIDATE => FALSE parameter setting
• The view DBA_OPTSTAT_OPERATIONS provides a history of gather and restore operations.
• By default superseded statistics are retained for 31 days. This can be managed by the function DBMS_STATS.GET_STATS_HISTORY_RETENTION and the procedure DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.

пятница, 7 апреля 2017 г.

Join elimination rules

comment from extremedb here

I found 7 Rules for Join Elimination in last year

1.Primary Key-Foreign Key – normal join, Starting in 10gR2
2.Primary Key-Foreign Key – ANSI join, Starting in 11gR1
3.Primary Key-Foreign Key – (semi/anti) join, Starting in 11gR1
4.Unique Index – outer join, Starting in 11gR1

Every guru knows above 4 things

5.Primary Key-Primary Key – simple self join, Starting in 11gR2 –> you ‘ve aleady mentioned

There are two more things and one simular thing
6.Primary Key-Primary Key – self join filter subsumption, Starting in 11gR2
7.Join Back Elimination – Using Bit Map Join Index, Starting in 9iR1

воскресенье, 2 апреля 2017 г.

Полезное по подсчетам Logical reads

Прочитал у Рендольфа

So here is an important point: If you want to understand the work Oracle has performed in terms of buffer visits you need to consider both, the number of logical I/Os as well as the number of buffers visited without involving logical I/O - this is represented by the “buffer is pinned count” statistics.
Quite often this fact is overlooked and people only focus on the logical I/Os - which is not unreasonable - but misses the point about pinned buffers re-visited without doing logical I/O.
Note that buffer pinning is not possible across fetch calls - if the control is returned to the client the buffers will no longer be kept pinned. This is the explanation why a the “fetchsize” or “arraysize” for bulk fetches can influence the number of logical I/Os required to process a result set.

пятница, 17 марта 2017 г.

List all privileges for user in oracle

-- You can filter results in last lines of query
WITH username(username) AS (
-- fill user name here
    SELECT UPPER('&USERNAME') FROM dual
  ),
  all_user_roles AS (
     SELECT (SELECT username FROM username) || sys_connect_by_path(granted_role, '->') PATH, granted_role, admin_option
     FROM dba_role_privs p
     START WITH grantee IN (SELECT username FROM username)
     CONNECT BY PRIOR granted_role = grantee
  ),
  grantee AS (
    SELECT granted_role NAME, PATH FROM all_user_roles
    UNION
    SELECT username, NULL AS PATH FROM username
  ),
  priv_list AS (
    SELECT 'ROLE' priv_type, granted_role priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option grantable, PATH
    FROM all_user_roles
    UNION
    SELECT 'SYSTEM' priv_type, privilege priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option, PATH
    FROM dba_sys_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'TABLE' priv_type, PRIVILEGE, owner, table_name, NULL AS column_name, grantable, PATH
    FROM dba_tab_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'COLUMN' priv_type, PRIVILEGE, owner, table_name, column_name, grantable, PATH
    FROM dba_col_privs, grantee
    WHERE grantee = grantee.name)
SELECT * 
FROM priv_list
-- optional filter
--WHERE table_name = 'MY_TABLE_NAME'
--AND priv = 'DELETE';  

четверг, 16 февраля 2017 г.

Long to clob

Use function sys.dbms_metadata_util.long2clob
For example

SELECT sys.dbms_metadata_util.long2clob(v.textlength,
                                        'SYS.VIEW$',
                                        'TEXT',
                                        v.rowid) 
FROM sys.view$ v;

LISTAGG - remove duplicates

My colleague Victor help me to find solution for task:
Aggregate string from query result without duplicates.

Almost all solutions, that I find in internet was like

SELECT LISTAGG(str, ',') WITHIN GROUP (ORDER BY 1)
FROM (SELECT DISTINCT str FROM tab);

But if you have scalar subquery with filter condition this solution doesn’t work because of 2-levels of nesting.
Below there are 2 solutions with regexps and xslt -transformations

SELECT (
   SELECT regexp_replace(LISTAGG(object_type, ',') WITHIN GROUP (ORDER BY object_type), '([^,]+)(,\1)+', '\1') 
   FROM user_objects
   ) solution1,
   (
   SELECT rtrim(xmltype('<r><n>' || LISTAGG(object_type, ',</n><n>') WITHIN GROUP (ORDER BY object_type) || ',</n></r>').extract('//n[not(preceding::n = .)]/text()').getstringval(), ',')
   FROM user_objects
   ) solution2
FROM dual;

четверг, 9 февраля 2017 г.

Sql profile content

Extract sql profiles info and hints:

SELECT CREATED, PROFILE_NAME, SQL_TEXT, 
  XMLtransform(XMLTYPE(h.comp_data), '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="*">
<xsl:for-each select="/outline_data/hint">
<xsl:value-of select="."/>
<xsl:text>&#xa;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>').getStringVal()
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p
WHERE p.name = h.profile_name;

This query puts all hints in one field. If multiply lines is good for you you can choose simplier implementation

SELECT CREATED, PROFILE_NAME, SQL_TEXT, extractvalue(VALUE(hint), '.') AS hint
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p, TABLE(xmlsequence(extract(xmltype(h.comp_data), '/outline_data/hint'))) hint
WHERE p.name = h.profile_name;

Also found solution on JL site, but this query valid for 10g only

select
       sp.sp_name, sa.attr#, sa.attr_val
from
       sqlprof$      sp,
       sqlprof$attr  sa
where
       sp.signature = sa.signature
and     sp.category  = sp.category
order by
       sp.sp_name,
       sa.attr#
;

The further investigations give me the link to Christian’s Antognini site where he recommends the following query for 11g:

SELECT so.name, extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name = 'opt_estimate'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

Test shows me, that this query is incorrect. Correct query is

SELECT so.name,extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name IN (SELECT name FROM DBA_SQL_PROFILES)
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

But it should be rewritten to show sql-query and creation time

среда, 8 февраля 2017 г.

Why put sys.aud$ to sysaux?

It’s not a secret, that if you leave sys.aud$ in system tablespace, you can catch high buffer busy waits because of freelist management of system tablespace.
But which tablespace choose for moving? SYSAUX or user tablespace.
One more point for SYSAUX from Data Pump:

When transporting a database over the network using full transportable
export, auditing cannot be enabled for tables stored in an
administrative tablespace (such as SYSTEM and SYSAUX) if the audit
trail information itself is stored in a user-defined tablespace

вторник, 7 февраля 2017 г.

среда, 1 февраля 2017 г.

Looking for object usage

WITH looking_for(l_owner, l_name) AS (
  SELECT UPPER('&owner'), UPPER('&object_name') FROM dual
  ),
  dep AS (
    SELECT 'DBA_DEPENDENCIES' info_from, d.owner, d.name, d.type, CAST(d.dependency_type AS VARCHAR2(4000)) note 
    FROM dba_dependencies d, looking_for 
    WHERE d.referenced_name = l_name AND d.owner = l_owner
    ),
  dba_source_with_owner AS (
    SELECT 'DBA_SOURCE with owner', d.owner, d.name, d.type, NULL
    FROM dba_source d, looking_for 
    WHERE regexp_like(text, '(^|[^A-Z0-9#$_]+)' || l_owner || '.' || l_name , 'i')
      AND NOT (d.name = l_name AND d.owner <> l_owner)
    ),
  dba_source_wo_owner AS (
    SELECT 'DBA_SOURCE without owner', d.owner, d.name, d.type, NULL
    FROM dba_source d, looking_for 
    WHERE regexp_like(text, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
      AND d.owner = l_owner
      AND d.name <> l_name
    ),
  jobs AS (
    SELECT 'DBMS_JOB', d.priv_user, to_char(d.job), NULL, d.what
    FROM dba_jobs d, looking_for 
    WHERE regexp_like(what, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  schedules AS (
    SELECT 'DBA_SCHEDULER_JOBS.JOB_ACTION', d.owner, d.job_name, job_type, d.job_action
    FROM dba_scheduler_jobs d, looking_for 
    WHERE regexp_like(job_action, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  schedules_programs AS (
    SELECT 'DBA_SCHEDULER_PROGRAMS.PROGRAM_ACTION', d.owner, d.program_name, program_type, program_action
    FROM dba_scheduler_programs d, looking_for 
    WHERE regexp_like(program_action, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  privs AS (
    SELECT 'DBA_TAB_PRIVS' info_from, NULL, grantee, 'ROLE', PRIVILEGE note 
    FROM dba_tab_privs d, looking_for 
    WHERE d.table_name = l_name AND d.owner = l_owner
    ),
  policy AS (
    SELECT 'DBA_POLICIES' info_from, NULL, d.policy_name, 'POLICY FOR ' || d.object_owner || '.' || d.object_name, pf_owner || '.' || d.package || '.' || d.function note 
    FROM dba_policies d, looking_for 
    WHERE d.pf_owner = l_owner 
      AND (d.package = l_name OR d.function = l_name)
    ),
  sql_plan AS (
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'HIST_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_text, 1, 4000))
    FROM looking_for, dba_hist_sql_plan p, dba_hist_sqltext t
    WHERE p.object_owner = l_owner
      AND p.object_name = l_name
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'INDEX_HIST_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_text, 1, 4000))
    FROM dba_hist_sql_plan p, dba_hist_sqltext t
    WHERE (object_owner, object_name) IN (SELECT owner, index_name FROM dba_indexes, looking_for WHERE table_name = l_name AND owner = l_owner)
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_fulltext, 1, 4000))
    FROM looking_for, v$sql_plan p, v$sql t
    WHERE p.object_owner = l_owner
      AND p.object_name = l_name
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'INDEX_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_fulltext, 1, 4000))
    FROM looking_for, v$sql_plan p, v$sql t
    WHERE (object_owner, object_name) IN (SELECT owner, index_name FROM dba_indexes, looking_for WHERE table_name = l_name AND owner = l_owner)
      AND p.sql_id = t.sql_id(+)
  ),
  tab_modifications AS (
    SELECT 'DBA_TAB_MODIFICATIONS' info_from, NULL AS owner, NULL AS NAME, 'Was modified on: ' || TO_CHAR(TIMESTAMP, 'DD.MM.YYYY HH24:MI:SS'), 
        'Inserts: ' || inserts || '; Updates: ' || updates || '; Deletes: ' || deletes || '; Truncated ' || truncated   note 
    FROM dba_tab_modifications d, looking_for 
    WHERE d.table_owner = l_owner 
      AND d.table_name = l_name
    )
SELECT /*+ PARALLEL(4)*/* FROM dep
UNION ALL
SELECT * FROM dba_source_with_owner
UNION ALL
SELECT * FROM dba_source_wo_owner t WHERE NOT EXISTS (SELECT NULL FROM dba_source_with_owner i WHERE i.owner = t.owner AND i.name = t.name)
UNION ALL
SELECT * FROM jobs
UNION ALL
SELECT * FROM schedules
UNION ALL
SELECT * FROM schedules_programs
UNION ALL
SELECT * FROM privs
UNION ALL
SELECT * FROM policy
UNION ALL
SELECT * FROM sql_plan
UNION ALL
SELECT * FROM tab_modifications
;

суббота, 28 января 2017 г.

ORA-01031 on CREATE/ALTER USER under sysdba account

If you have the following error with user operations (CREATE USER, ALTER USER etc)

SQL> connect / as sysdba
Connected.
SQL> create user c##common identified by c##common;
create user c##common identified by c##common
                                    *
ERROR at line 1:
ORA-01031: insufficient privileges

check if Database Vault is enabled.

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

After you enable Oracle Database Vault, you no longer can use the
administrative accounts (such as SYS and SYSTEM) to create or enable
user accounts.
Disabling database Vault is version specific. For example in 12c if you forgot password for accounts with DV_ACCTMGR role the only way is to recreate database.

понедельник, 16 января 2017 г.

Powershell: Split file to parts by placeholders

I have file all.sql with following structure

--------------start of file1.sql--------------
...
...content of file1.sql...
...
--------------end of file1.sql--------------


--------------start of file2.sql--------------
...
...content of file2.sql...
...
--------------end of file2.sql--------------

and I need to split file all.sql to separate files file1.sql, file2.sql, etc
You can use following Powershell script to do it

$workingDir="c:\[path_to_dir_with_files]\"
$allFileName="all.sql"

$pattern=[regex]'(?sm)--------------start of (.*?)--------------(.*?)--------------end of (.*?)--------------'

$file = Get-content $workingDir$allFileName -Raw 
foreach($match in $pattern.Matches($file)) {
  $outputFileName = $workingDir+$match.Groups[1].value
  Write-Output $outputFileName
  Set-Content -Path $outputFileName -Value $match.Groups[2].value
}

change pattern expression to hit your placeholders.

Get objects with dblinks

Script to get objects with dblinks in source code. It checks views, materialized views and objects with source code (I don’t check through dba_dependencies because it returns objects that use dblinks via synonyms).
It can be easily extended for dba_jobs for example.

Version with all_ views

WITH db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM all_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM all_views
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM all_mviews
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM all_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;
Version with dba_ views

WITH owner_list(owner) AS (
  SELECT USER FROM dual
 ),
 db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM dba_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM dba_views
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM dba_mviews
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM dba_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND s.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;

четверг, 12 января 2017 г.

Notepad++ lines not starting with word regexp

To remove lines not starting with word you can use following regular expression (put your word instead of GRANT

^(?!GRANT).*$

ORA-24005 and ORA-24002 on table DROP

If table drop ends with message

ORA-24005: Inappropriate utilities used to perform DDL on AQ table [queue_table_name]

first try drop through package DBMS_AQADM with force=true

BEGIN
  SYS.DBMS_AQADM.drop_QUEUE_TABLE(QUEUE_TABLE =>'[queue_table_name]', FORCE=> TRUE);
END;
/ 

But what to do if it returns

ORA-24002: QUEUE_TABLE [queue_table_name] does not exist
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 2

Your data dictionary is already little bit corrupted. You can try following

alter session set events '10851 trace name context forever, level 2';
drop table [queue_table_name];

четверг, 29 декабря 2016 г.

Script generator for moving objects to another tablaspace

Limitation and aspects
1. Subpartitioning doesn’t support
2. Currently 3 source-target tablespaces pares
3. IOT and lobs, partitioned IOT and lobs are supported
4. Parallelism support
5. Output script can be divided to separate files per owner

DECLARE 
  -- Minimum size of objects for parallel processing
  C_MIN_PARALLEL_SIZE_MB CONSTANT NUMBER := 100;  
  -- Parallel degree for moving and rebuilding
  C_PARALLEL_DEGREE CONSTANT INT := 8;

  TYPE tpt_varchar2 IS TABLE OF VARCHAR2(30);
  -- currently 3 tablespaces supported
  -- 1st old -> 1st new, 2nd old -> 2nd new etc
  l_old_ts tpt_varchar2 := tpt_varchar2('GRIM_DATA1','GRIM_INDX1', '');
  l_new_ts tpt_varchar2 := tpt_varchar2('GRIM_DATA2','GRIM_INDX2', '');


  FUNCTION get_old_ts_list RETURN VARCHAR2 IS
  BEGIN
    RETURN '''' || l_old_ts(1) ||''','''|| l_old_ts(2)||''','''||l_old_ts(3) || '''';
  END;

  FUNCTION get_new_ts_list RETURN VARCHAR2 IS
  BEGIN
    RETURN '''' || l_new_ts(1) ||''','''|| l_new_ts(2)||''','''||l_new_ts(3) || '''';
  END;

  FUNCTION get_corresponding_ts(l_old_ts_name VARCHAR2) RETURN VARCHAR2 IS
    i PLS_INTEGER;
  BEGIN
    i := l_old_ts.FIRST;
    WHILE UPPER(l_old_ts(i)) <> UPPER(l_old_ts_name) LOOP
      i := l_old_ts.NEXT(i);
    END LOOP;
    RETURN l_new_ts(i);
  EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, 'Can''t find corresponding ts for ' || l_old_ts_name);
  END get_corresponding_ts;

  FUNCTION get_parallel_clause(l_size_mb NUMBER) RETURN VARCHAR2 IS
  BEGIN
    IF l_size_mb > C_MIN_PARALLEL_SIZE_MB THEN
      RETURN ' PARALLEL ' || C_PARALLEL_DEGREE || ' ';
    END IF;
    RETURN NULL;
  END get_parallel_clause;

  PROCEDURE echo_rebuild_indexes(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.new_line;
    dbms_output.put_line('
PROMPT Rebuild unusable indexes
BEGIN
  FOR rec IN (SELECT * FROM all_ind_partitions WHERE index_owner = ''' || l_owner || ''' AND status = ''UNUSABLE'') LOOP
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.index_owner || ''.'' || rec.index_name || '' REBUILD PARTITION '' || rec.partition_name || '' PARALLEL ' || C_PARALLEL_DEGREE || ''';
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.index_owner || ''.'' || rec.index_name || '' NOPARALLEL'';
  END LOOP;

  FOR rec IN (SELECT * FROM dba_indexes WHERE owner = ''' || l_owner || ''' AND status = ''UNUSABLE'') LOOP
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.owner || ''.'' || rec.index_name || '' REBUILD PARALLEL ' || C_PARALLEL_DEGREE || ''';
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.owner || ''.'' || rec.index_name || '' NOPARALLEL'';
  END LOOP;
END;
/');
  END echo_rebuild_indexes;

  PROCEDURE echo_header(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.put_line('SPOOL ' || l_owner || '.log'); 
    dbms_output.put_line('PROMPT Unusable indexes
SELECT owner, index_name, NULL AS part_name FROM all_indexes WHERE owner = ''' || l_owner || ''' AND status = ''UNUSABLE''
UNION ALL
SELECT index_owner, index_name, partition_name AS part_name FROM all_ind_partitions WHERE index_owner = ''' || l_owner || ''' AND status = ''UNUSABLE''
;
PROMPT Check parallel degree greater than 1 
SELECT owner, index_name AS obj_name, DEGREE AS par_degree
FROM all_indexes 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
UNION ALL
SELECT owner, table_name AS obj_name, DEGREE AS par_degree
FROM all_tables 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
;');     
  END echo_header;

  PROCEDURE echo_footer(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.new_line();
    dbms_output.put_line('PROMPT Check parallel degree greater than 1 
SELECT owner, index_name AS obj_name, DEGREE AS par_degree
FROM all_indexes 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
UNION ALL
SELECT owner, table_name AS obj_name, DEGREE AS par_degree
FROM all_tables 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
;

PROMPT Check moving
SELECT owner, table_name AS obj_name, NULL AS part_name
FROM all_tables
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, index_name AS obj_name, NULL AS part_name
FROM all_indexes
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, table_name || ''.'' || column_name AS obj_name, NULL AS part_name 
FROM all_lobs
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name, partition_name 
FROM all_tab_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT index_owner, index_name, partition_name
FROM all_ind_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name || ''.'' || column_name, partition_name
FROM all_lob_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, table_name, ''DEF ATTRIBUTE''
FROM all_part_tables
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, index_name, ''DEF ATTRIBUTE''
FROM all_part_indexes
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name || ''.'' || column_name, ''DEF ATTRIBUTE''
FROM all_part_lobs
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT USER, segment_name, partition_name
FROM user_segments 
WHERE tablespace_name IN (' || get_old_ts_list || ');

SPOOL OFF');
  dbms_output.put_line(LPAD('-', 40, '-'));  
  END echo_footer;
BEGIN
  FOR rec IN (
      -- not by dba_segments because of delayed segment creation
      SELECT owner 
      FROM dba_tables
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_indexes
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_lobs
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_tab_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT index_owner 
      FROM dba_ind_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_lob_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_part_tables
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_part_indexes
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_part_lobs
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
  ) LOOP
    echo_header(rec.owner);
    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned tables');
    FOR obj_rec IN (
        SELECT * 
        FROM dba_part_tables t
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned indexes');
    FOR obj_rec IN (
        SELECT t.* 
        FROM dba_part_indexes t, dba_indexes i
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND i.owner = t.owner
          AND i.index_name = t.index_name
          AND i.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned IOT');
    FOR obj_rec IN (
        SELECT i.table_owner, i.table_name, t.def_tablespace_name 
        FROM dba_part_indexes t, dba_indexes i
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND i.owner = t.owner
          AND i.index_name = t.index_name
          AND i.index_type = 'IOT - TOP'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_owner||'"."'||obj_rec.TABLE_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned LOB');
    FOR obj_rec IN (
        SELECT * 
        FROM dba_part_lobs t
        WHERE t.table_owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_owner||'"."'||obj_rec.TABLE_NAME || '" '
                         ||'MODIFY DEFAULT ATTRIBUTES LOB (' || obj_rec.column_name || ') (TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||');');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move non-partitioned tables'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_tables t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)
                           || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move table partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_tab_partitions t, dba_segments s
        WHERE t.table_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.table_owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE PARTITION "'||obj_rec.partition_name || '"'
        || ' TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name) 
        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move IOT table partitions'); 
    FOR obj_rec IN (
        SELECT i.table_owner, i.table_name, t.partition_name, t.tablespace_name, s.bytes/1024/1024 size_mb
        FROM dba_indexes i, dba_ind_partitions t, dba_segments s
        WHERE t.index_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.index_owner = s.owner(+)
          AND t.index_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
          AND i.owner = t.index_owner
          AND i.index_name = t.index_name
          AND i.index_type = 'IOT - TOP'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE PARTITION "'||obj_rec.partition_name || '"'
        || ' TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)|| ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move indexes'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_indexes t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
          AND t.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME ||'" REBUILD '
      || ' TABLESPACE '||get_corresponding_ts(obj_rec.tablespace_name)||''
      || get_parallel_clause(obj_rec.size_mb) || ';');
      -- return parallel degree back
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME||'"' ||' PARALLEL '||obj_rec.DEGREE||';'); 
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move index partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_indexes i, dba_ind_partitions t, dba_segments s
        WHERE t.index_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.index_owner = s.owner(+)
          AND t.index_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
          AND i.owner = t.index_owner
          AND i.index_name = t.index_name
          AND i.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.INDEX_OWNER||'"."'||obj_rec.INDEX_NAME||'"'
                        ||' REBUILD PARTITION "'||obj_rec.PARTITION_NAME||'" TABLESPACE '||get_corresponding_ts(obj_rec.tablespace_name)||''
                          || get_parallel_clause(obj_rec.size_mb) || ';');
      -- noparallel not needed for index partitions
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move non-partitioned lobs'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_lobs t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME||'"'
                          ||' MOVE LOB ("'||obj_rec.COLUMN_NAME||'") STORE AS (TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)||')'
                        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move lob partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_lob_partitions t, dba_segments s
        WHERE t.table_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.table_owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.lob_partition_name = s.partition_name(+)
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.TABLE_OWNER||'"."'||obj_rec.TABLE_NAME||'" MOVE PARTITION "'||obj_rec.PARTITION_NAME||'"'
                        ||' LOB ("'||obj_rec.COLUMN_NAME||'") STORE AS (TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name) ||')'
                        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    echo_rebuild_indexes(rec.owner);
    echo_footer(rec.owner);
  END LOOP;
END;