回答編集履歴
4
訂正
test
CHANGED
@@ -42,11 +42,11 @@
|
|
42
42
|
|
43
43
|
EXECUTE IMMEDIATE REC_TA.SQL_STRING INTO L_COUNTS ;
|
44
44
|
|
45
|
-
DBMS_OUTPUT.PUT_LINE(TO_CHAR(L_COUNTS, '999,999,999,999,90')) ;
|
45
|
+
DBMS_OUTPUT.PUT_LINE(TO_CHAR(L_COUNTS, '999,999,999,999,990')) ;
|
46
46
|
|
47
47
|
ELSE
|
48
48
|
|
49
|
-
DBMS_OUTPUT.PUT_LINE('index composition table') ;
|
49
|
+
DBMS_OUTPUT.PUT_LINE('IOT_TYPE:' || REC_TA.IOT_TYPE || ' index composition table') ;
|
50
50
|
|
51
51
|
END IF ;
|
52
52
|
|
3
修正
test
CHANGED
@@ -18,7 +18,7 @@
|
|
18
18
|
|
19
19
|
TA.TABLE_NAME
|
20
20
|
|
21
|
-
, 'SELECT /*+ INDEX_FFS(TA) PARALLEL(DEFAULT) */ COUNT(*) FROM ' || TA.TABLE_NAME || ' TA' AS SQL_STRING
|
21
|
+
, 'SELECT /*+ INDEX_FFS(TA) PARALLEL(DEFAULT) */ COUNT(*) FROM "' || TA.TABLE_NAME || '" TA' AS SQL_STRING
|
22
22
|
|
23
23
|
, TA.IOT_TYPE
|
24
24
|
|
2
修正
test
CHANGED
@@ -16,29 +16,39 @@
|
|
16
16
|
|
17
17
|
SELECT
|
18
18
|
|
19
|
-
TA.TABLE_NAME
|
19
|
+
TA.TABLE_NAME
|
20
20
|
|
21
|
-
, 'SELECT /*+ INDEX_FFS(TA) PARALLEL(DEFAULT) */ COUNT(*) FROM ' || TA.TABLE_NAME || ' TA' AS SQL_STRING
|
21
|
+
, 'SELECT /*+ INDEX_FFS(TA) PARALLEL(DEFAULT) */ COUNT(*) FROM ' || TA.TABLE_NAME || ' TA' AS SQL_STRING
|
22
22
|
|
23
|
-
|
23
|
+
, TA.IOT_TYPE
|
24
24
|
|
25
|
-
|
25
|
+
FROM USER_TABLES TA
|
26
26
|
|
27
|
-
|
27
|
+
WHERE -- 条件を記述できます。
|
28
28
|
|
29
|
-
|
29
|
+
TA.DROPPED = 'NO' -- except trash box, since Oracle10.1.0
|
30
30
|
|
31
|
-
|
31
|
+
ORDER BY
|
32
32
|
|
33
|
-
|
33
|
+
TA.TABLE_NAME
|
34
34
|
|
35
|
-
|
35
|
+
) LOOP
|
36
36
|
|
37
|
-
|
37
|
+
DBMS_OUTPUT.PUT(RPAD(REC_TA.TABLE_NAME, 31)) ;
|
38
38
|
|
39
|
-
|
39
|
+
-- ↓に条件を記述できます。
|
40
40
|
|
41
|
+
IF REC_TA.IOT_TYPE IS NULL THEN
|
42
|
+
|
43
|
+
EXECUTE IMMEDIATE REC_TA.SQL_STRING INTO L_COUNTS ;
|
44
|
+
|
41
|
-
DBMS_OUTPUT.PUT_LINE(
|
45
|
+
DBMS_OUTPUT.PUT_LINE(TO_CHAR(L_COUNTS, '999,999,999,999,90')) ;
|
46
|
+
|
47
|
+
ELSE
|
48
|
+
|
49
|
+
DBMS_OUTPUT.PUT_LINE('index composition table') ;
|
50
|
+
|
51
|
+
END IF ;
|
42
52
|
|
43
53
|
END LOOP ;
|
44
54
|
|
@@ -46,8 +56,6 @@
|
|
46
56
|
|
47
57
|
/
|
48
58
|
|
49
|
-
|
50
|
-
|
51
59
|
```
|
52
60
|
|
53
61
|
何をしたいのかもっと具体的に書かれた方が適切なコメントが付くかと。
|
1
変更
test
CHANGED
@@ -1,14 +1,52 @@
|
|
1
1
|
for loop って空白をひとつ入れていただきたいです。
|
2
2
|
|
3
|
-
[for loop
|
3
|
+
[for loop 参考](https://qiita.com/Papageno/items/6d06f55c2c60cdfb82f3) シンプルに一部を削って
|
4
4
|
|
5
5
|
```PL/SQL
|
6
6
|
|
7
|
-
|
7
|
+
DECLARE
|
8
8
|
|
9
|
-
|
9
|
+
L_COUNTS NUMBER ;
|
10
10
|
|
11
|
+
BEGIN
|
12
|
+
|
13
|
+
FOR REC_TA IN
|
14
|
+
|
15
|
+
(
|
16
|
+
|
17
|
+
SELECT
|
18
|
+
|
19
|
+
TA.TABLE_NAME
|
20
|
+
|
21
|
+
, 'SELECT /*+ INDEX_FFS(TA) PARALLEL(DEFAULT) */ COUNT(*) FROM ' || TA.TABLE_NAME || ' TA' AS SQL_STRING
|
22
|
+
|
23
|
+
FROM USER_TABLES TA
|
24
|
+
|
25
|
+
WHERE -- 条件を記述できます。
|
26
|
+
|
27
|
+
TA.DROPPED = 'NO' -- except trash box, since Oracle10.1.0
|
28
|
+
|
29
|
+
AND TA.IOT_TYPE IS NULL -- except index composition table
|
30
|
+
|
31
|
+
ORDER BY
|
32
|
+
|
33
|
+
TA.TABLE_NAME
|
34
|
+
|
35
|
+
) LOOP
|
36
|
+
|
37
|
+
-- ↓に条件を記述できます。
|
38
|
+
|
39
|
+
EXECUTE IMMEDIATE REC_TA.SQL_STRING INTO L_COUNTS ;
|
40
|
+
|
41
|
+
DBMS_OUTPUT.PUT_LINE(RPAD(REC_TA.TABLE_NAME, 31) || TO_CHAR(L_COUNTS, '999,999,999,999,90')) ;
|
42
|
+
|
11
|
-
|
43
|
+
END LOOP ;
|
44
|
+
|
45
|
+
END ;
|
46
|
+
|
47
|
+
/
|
48
|
+
|
49
|
+
|
12
50
|
|
13
51
|
```
|
14
52
|
|