[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT FND_TS_MIG_CMDS_S.nextval
FROM SYS.dual;
SELECT lineno,
new_tablespace,
object_type,
subobject_type,
migration_status
FROM fnd_ts_mig_cmds
WHERE owner = p_owner
AND object_type = p_object_type
AND object_name = p_object_name
AND index_parallel = NVL(p_index_parallel, 'NOPARALLEL')
AND subobject_type = NVL(p_subobject_type, 'X')
order by migration_status;
UPDATE fnd_ts_mig_cmds
SET migration_cmd = p_mig_cmd,
new_tablespace = p_new_tablespace,
old_tablespace = p_old_tablespace,
total_blocks = p_tot_blocks,
partitioned = p_partitioned,
parent_owner = p_parent_owner,
parent_object_name = p_parent_object_name,
generation_date = sysdate,
last_update_date = sysdate
WHERE lineno = cmd_rec.lineno;
UPDATE fnd_ts_mig_cmds
SET migration_cmd = p_mig_cmd,
last_update_date = sysdate
WHERE lineno = cmd_rec.lineno;
INSERT INTO fnd_ts_mig_cmds (lineno,
owner,
object_type,
subobject_type,
index_parallel,
object_name,
parent_lineno,
old_tablespace,
new_tablespace,
migration_cmd,
migration_status,
parent_owner,
parent_object_name,
total_blocks,
execution_mode,
partitioned,
error_text,
generation_date,
last_update_date)
VALUES (l_lineno,
p_owner,
p_object_type,
NVL(p_subobject_type, 'X'),
NVL(p_index_parallel, 'NOPARALLEL'),
p_object_name,
p_parent_lineno,
p_old_tablespace,
p_new_tablespace,
p_mig_cmd,
'GENERATED',
p_parent_owner,
p_parent_object_name,
p_tot_blocks,
p_execution_mode,
p_partitioned,
p_err_text,
sysdate,
sysdate);
SELECT tablespace
FROM fnd_tablespaces
WHERE tablespace_type = fnd_ts_mig_util.l_def_ind_tsp;
SELECT blocks
FROM dba_segments
WHERE owner = p_owner
AND segment_name = p_object_name
AND partition_name = p_partition_name;
SELECT owner,
table_name,
column_name,
data_type
FROM dba_tab_columns
WHERE owner = l_owner
AND table_name = l_table_name
AND data_type IN ('CLOB', 'BLOB', 'NCLOB');
SELECT MIN(dl.chunk)
FROM dba_lobs dl
WHERE dl.owner = l_owner
AND dl.table_name = l_table_name;
SELECT /*+ FIRST_ROWS */ d.column_name ,
d.table_name,
d.segment_name,
d.owner
FROM dba_lobs d
WHERE owner = l_owner
AND table_name = l_table_name
AND NOT EXISTS (select column_name
from dba_tab_columns c
where c.data_type in ('CLOB','BLOB','NCLOB')
and c.owner = l_owner
and c.table_name = l_table_name
and c.column_name = d.column_name)
AND EXISTS (select attr_name
from dba_type_attrs ta,
dba_tab_columns tc
where tc.owner = l_owner
and tc.table_name = l_table_name
and tc.column_name = SUBSTR(d.column_name, 2, INSTR(d.column_name, '.', 1) - 3)
and tc.data_type_owner = ta.owner
and tc.data_type = ta.type_name
and ta.attr_type_name in ('CLOB','BLOB','NCLOB')
and ta.attr_name = RTRIM(SUBSTR(d.column_name,INSTR(d.column_name, '.', -1) + 2), '"'));
SELECT owner,
column_name,
table_name,
segment_name,
index_name
FROM dba_lobs d
WHERE owner = l_owner
AND table_name = l_table_name;
SELECT table_name
FROM dba_tables
WHERE owner = l_owner
AND iot_type = 'IOT_OVERFLOW'
AND iot_name = l_table_name;
SELECT index_name
FROM dba_indexes
WHERE owner = l_owner
AND table_name = l_table_name
AND index_type = 'IOT - TOP';
SELECT partitioning_type,
subpartitioning_type,
def_tablespace_name
FROM dba_part_tables
WHERE owner = l_owner
AND table_name = l_table_name;
SELECT partition_name,
tablespace_name,
logging
FROM dba_tab_partitions
WHERE table_owner = l_owner
AND table_name = l_table_name;
SELECT lob_name,
column_name,
lob_partition_name,
lob_indpart_name
FROM dba_lob_partitions
WHERE table_owner = l_owner
AND table_name = l_table_name
AND partition_name = l_part_name;
UPDATE fnd_ts_mig_cmds
SET total_blocks = l_sum_blocks,
execution_mode = l_execution_mode
WHERE owner = p_owner
AND object_type = 'MV_LOG'
AND object_name = p_parent_obj_name;
SELECT owner,
index_name,
index_type,
tablespace_name,
partitioned,
status,
ityp_owner,
ityp_name,
domidx_opstatus,
domidx_status,
ltrim(rtrim(degree)) degree,
ltrim(rtrim(logging)) logging
FROM dba_indexes
WHERE table_owner = l_owner
AND table_name = l_table_name
AND NVL(temporary, 'N') = 'N'
AND index_type NOT IN ('IOT - TOP', 'LOB', 'CLUSTER')
ORDER by index_type DESC;
SELECT partitioning_type,
subpartitioning_type,
def_tablespace_name
FROM dba_part_indexes
WHERE owner = l_index_owner
AND index_name = l_index_name;
SELECT /*+ ALL_ROWS */ partition_name,
tablespace_name,
status,
ltrim(rtrim(logging)) logging
FROM dba_ind_partitions
WHERE index_owner = l_index_owner
AND index_name = l_index_name;
SELECT dt.owner,
dt.table_name,
dt.tablespace_name,
dt.partitioned,
dt.logging,
dt.iot_type
FROM dba_tables dt
WHERE dt.owner = l_index_owner
AND dt.table_name LIKE l_ctx_tabs
AND dt.tablespace_name <> l_tablespace_name
AND NVL(dt.iot_type, 'N') NOT IN ('IOT', 'IOT_OVERFLOW')
AND NVL(dt.temporary, 'N') = 'N'
UNION
SELECT di.owner,
di.table_name,
di.tablespace_name,
di.partitioned,
di.logging,
'IOT' iot_type
FROM dba_indexes di
WHERE di.owner = l_index_owner
AND di.table_name LIKE l_ctx_tabs
AND di.tablespace_name <> l_tablespace_name
AND di.index_type = 'IOT _ TOP'
AND NVL(di.temporary, 'N') = 'N';
l_query := 'SELECT 1
FROM ctxsys.ctx_indexes
WHERE idx_owner = :1
AND idx_name = :2
AND idx_status = ''INDEXED''';
l_query := 'SELECT sdo_index_type,
sdo_tsname,
sdo_index_table,
nvl(sdo_tablespace, ''X'') sdo_tablespace,
NVL(sdo_index_dims, 2) sdo_index_dims,
NVL(sdo_rtree_pctfree, 10) sdo_rtree_pctfree,
sdo_commit_interval,
sdo_level,
sdo_numtiles
FROM '||l_sdo_metadata_table||'
WHERE sdo_index_owner = :1
AND sdo_index_name = :2';
SELECT tablespace_name
FROM dba_indexes
WHERE table_owner = p_owner
AND table_name = p_iot_name
AND index_type = 'IOT - TOP';
SELECT /*+ RULE */ dqt.owner owner,
dqt.queue_table queue_table,
fnd_ts_mig_util.l_aq_tab_tsp tablespace_type,
ft.tablespace new_tablespace,
dt.tablespace_name tablespace_name,
dt.iot_type iot_type,
dt.partitioned partitioned,
dt.logging logging
FROM dba_queue_tables dqt,
dba_tables dt,
fnd_tablespaces ft
WHERE dqt.owner = p_owner
AND dqt.owner = dt.owner
AND dqt.queue_table = dt.table_name
AND ft.tablespace_type = fnd_ts_mig_util.l_aq_tab_tsp
AND dt.table_name NOT LIKE 'BIN$%'
AND NVL(dt.temporary, 'N') = 'N';
aq_rec_tab.owner.DELETE;
qry := ' SELECT owner,
table_name,
tablespace_name,
iot_type,
partitioned,
logging
FROM dba_tables
WHERE owner = :1
AND NVL(temporary, ''N'') = ''N''
AND table_name like ''AQ$_''||:2||''%''';
SELECT /*+ RULE */ distinct dsl.log_owner log_owner,
dsl.master master,
dsl.log_table,
fnd_ts_mig_util.l_def_mv_tsp tablespace_type,
ft.tablespace new_tablespace,
dt.tablespace_name,
dt.iot_type,
dt.partitioned,
dt.logging
FROM dba_snapshot_logs dsl,
dba_tables dt,
fnd_tablespaces ft
WHERE dsl.log_owner = p_owner
AND dsl.master = p_table_name
AND dsl.log_owner = dt.owner
AND dsl.log_table = dt.table_name
AND ft.tablespace_type = fnd_ts_mig_util.l_def_mv_tsp
AND dt.table_name NOT LIKE 'BIN$%'
AND NVL(dt.temporary, 'N') = 'N';
mvlog_rec_tab.log_owner.DELETE;
SELECT /*+ RULE */ ds.owner,
ds.name,
ds.table_name,
fnd_ts_mig_util.l_def_mv_tsp tablespace_type,
ft.tablespace new_tablespace,
dt.tablespace_name,
dt.iot_type,
dt.partitioned,
dt.logging
FROM dba_snapshots ds,
dba_tables dt,
fnd_tablespaces ft
WHERE ds.owner = p_owner
AND ds.owner = dt.owner
AND ds.table_name = dt.table_name
AND dt.cluster_name IS NULL
AND ft.tablespace_type = fnd_ts_mig_util.l_def_mv_tsp
AND dt.table_name NOT LIKE 'BIN$%'
AND NVL(dt.temporary, 'N') = 'N';
SELECT '1'
FROM dba_tab_partitions
WHERE table_owner = l_owner
AND table_name = l_table_name
AND tablespace_name <> l_tablespace_name;
mv_rec_tab.owner.DELETE;
SELECT column_name,
data_type
FROM dba_tab_columns
WHERE owner = p_owner
AND table_name = p_table_name
AND data_type IN ('LONG', 'LONG RAW');
SELECT oracle_username
FROM fnd_oracle_userid
WHERE oracle_username = p_owner
AND read_only_flag IN ('E', 'A', 'U', 'M', 'K');
SELECT dt.owner,
dt.table_name,
NVL(fot.custom_tablespace_type, fot.tablespace_type) tablespace_type,
ft.tablespace new_tablespace,
dt.tablespace_name,
dt.iot_type,
dt.partitioned,
dt.logging
FROM dba_tables dt,
fnd_object_tablespaces fot,
fnd_tablespaces ft
WHERE dt.owner = p_owner
AND dt.owner = fot.oracle_username
AND dt.table_name = fot.object_name
AND NVL(fot.custom_tablespace_type, fot.tablespace_type) = ft.tablespace_type
AND fot.object_type = 'TABLE'
AND dt.cluster_name IS NULL
AND dt.table_name NOT LIKE 'BIN$%'
AND EXISTS ( select dtc.table_name
from dba_tab_columns dtc
where dtc.owner = p_owner
and dtc.table_name = dt.table_name
and dtc.data_type in ('LONG', 'LONG RAW'))
UNION ALL
-- all unclassified tables go to TRANSACTION_TABLE tablespace
SELECT dt.owner,
dt.table_name,
fnd_ts_mig_util.l_unclass_tsp tablespace_type,
ft.tablespace new_tablespace,
dt.tablespace_name,
dt.iot_type,
dt.partitioned,
dt.logging
FROM dba_tables dt,
fnd_tablespaces ft
WHERE dt.owner = p_owner
AND ft.tablespace_type = fnd_ts_mig_util.l_unclass_tsp
AND dt.cluster_name IS NULL
AND dt.table_name NOT LIKE 'BIN$%'
AND EXISTS ( select dtc.table_name
from dba_tab_columns dtc
where dtc.owner = p_owner
and dtc.table_name = dt.table_name
and dtc.data_type in ('LONG', 'LONG RAW'))
AND NOT EXISTS ( SELECT object_name
FROM fnd_object_tablespaces fot
WHERE fot.oracle_username = p_owner
AND fot.object_type = 'TABLE'
AND fot.object_name = dt.table_name);
SELECT fnd_ts_mig_cmds_s.nextval from dual;
tab_rec_tab.owner.DELETE;
SELECT dt.owner,
dt.table_name,
NVL(fot.custom_tablespace_type, fot.tablespace_type) tablespace_type,
ft.tablespace new_tablespace,
dt.tablespace_name,
dt.iot_type,
dt.partitioned,
dt.logging
FROM dba_tables dt,
fnd_object_tablespaces fot,
fnd_tablespaces ft
WHERE dt.owner = p_owner
AND dt.owner = fot.oracle_username
AND dt.table_name = fot.object_name
AND NVL(fot.custom_tablespace_type, fot.tablespace_type) = ft.tablespace_type
AND fot.object_type = 'TABLE'
AND NVL(dt.temporary, 'N') = 'N'
AND dt.cluster_name IS NULL
AND NOT EXISTS ( select dtc.table_name
from dba_tab_columns dtc
where dtc.owner = p_owner
and dtc.table_name = dt.table_name
and dtc.data_type in ('LONG', 'LONG RAW'))
AND NOT EXISTS ( select ds.table_name
from dba_snapshots ds
where ds.owner = p_owner
and ds.table_name = dt.table_name)
AND NOT EXISTS ( select dsl.log_table
from dba_snapshot_logs dsl
where dsl.log_owner = p_owner
and dsl.log_table = dt.table_name)
AND NOT EXISTS ( select dqt.queue_table
from dba_queue_tables dqt
where dqt.owner = p_owner
and dqt.queue_table = dt.table_name)
AND NOT EXISTS ( select det.table_name
from dba_external_tables det
where det.owner = p_owner
and det.table_name = dt.table_name)
AND dt.table_name NOT LIKE 'AQ$%' -- tables for AQ tables
AND (dt.table_name NOT LIKE 'DR$%' -- tables for INTERMEDIA indexes
OR dt.owner = 'CTXSYS')
AND dt.table_name NOT LIKE 'RUPD$%' -- tables for snapshot logs
AND dt.table_name NOT LIKE 'MDRT%$' -- tables for SPATIAL indexes
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.nested='NO'
UNION ALL
-- all unclassified tables go to TRANSACTION_TABLES tablespace
-- Not IOTs, AQs, Domain Index tables, MVs, MV logs
SELECT dt.owner,
dt.table_name,
fnd_ts_mig_util.l_unclass_tsp tablespace_type,
ft.tablespace new_tablespace,
dt.tablespace_name,
dt.iot_type,
dt.partitioned,
dt.logging
FROM dba_tables dt,
fnd_tablespaces ft
WHERE dt.owner = p_owner
AND ft.tablespace_type = fnd_ts_mig_util.l_unclass_tsp
AND NOT EXISTS ( SELECT object_name
FROM fnd_object_tablespaces fot
WHERE fot.oracle_username = p_owner
AND fot.object_type = 'TABLE'
AND fot.object_name = dt.table_name)
AND NVL(dt.temporary, 'N') = 'N'
AND NVL(dt.iot_type, 'X') NOT IN ('IOT', 'IOT_OVERFLOW')
AND dt.cluster_name IS NULL
AND NOT EXISTS ( select dtc.table_name
from dba_tab_columns dtc
where dtc.owner = p_owner
and dtc.table_name = dt.table_name
and dtc.data_type in ('LONG', 'LONG RAW'))
AND NOT EXISTS ( select ds.table_name
from dba_snapshots ds
where ds.owner = p_owner
and ds.table_name = dt.table_name)
AND NOT EXISTS ( select dsl.log_table
from dba_snapshot_logs dsl
where dsl.log_owner = p_owner
and dsl.log_table = dt.table_name)
AND NOT EXISTS ( select dqt.queue_table
from dba_queue_tables dqt
where dqt.owner = p_owner
and dqt.queue_table = dt.table_name)
AND NOT EXISTS ( select det.table_name
from dba_external_tables det
where det.owner = p_owner
and det.table_name = dt.table_name)
AND dt.table_name NOT LIKE 'AQ$%' -- tables for AQ tables
AND (dt.table_name NOT LIKE 'DR$%' -- tables for INTERMEDIA indexes
OR dt.owner = 'CTXSYS')
AND dt.table_name NOT LIKE 'RUPD$%' -- tables for snapshot logs
AND dt.table_name NOT LIKE 'MDRT%$' -- tables for SPATIAL indexes
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.nested='NO'
UNION ALL
-- all IOTs go to TRANSACTION_TABLES (default rule)
-- Not AQs, Domain Index tables
SELECT dt.owner,
dt.table_name,
fnd_ts_mig_util.l_def_tab_tsp tablespace_type,
ft.tablespace new_tablespace,
di.tablespace_name,
dt.iot_type,
dt.partitioned,
dt.logging
FROM dba_tables dt,
dba_indexes di,
fnd_tablespaces ft
WHERE dt.owner = p_owner
AND ft.tablespace_type = fnd_ts_mig_util.l_def_tab_tsp
AND dt.owner = di.table_owner
AND dt.table_name = di.table_name
AND di.index_type = 'IOT - TOP'
AND NVL(dt.temporary, 'N') = 'N'
AND NVL(dt.iot_type, 'X') = 'IOT'
AND dt.cluster_name IS NULL
AND NOT EXISTS ( select dqt.queue_table
from dba_queue_tables dqt
where dqt.owner = p_owner
and dqt.queue_table = dt.table_name)
AND NOT EXISTS ( select det.table_name
from dba_external_tables det
where det.owner = p_owner
and det.table_name = dt.table_name)
AND dt.table_name NOT LIKE 'AQ$%' -- tables for AQ tables
AND (dt.table_name NOT LIKE 'DR$%' -- tables for INTERMEDIA indexes
OR dt.owner = 'CTXSYS')
AND dt.table_name NOT LIKE 'MDRT%$' -- tables for SPATIAL indexes
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.table_name NOT LIKE 'RUPD$%'; -- tables for snapshot logs
SELECT '1'
FROM dba_tab_partitions
WHERE table_owner = l_owner
AND table_name = l_table_name
AND tablespace_name <> l_tablespace_name;
tab_rec_tab.owner.DELETE;
SELECT oracle_username
FROM fnd_oracle_userid
WHERE oracle_username = p_schema
AND read_only_flag IN ('E', 'A', 'U', 'M', 'K');
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc
WHERE r_owner = l_owner
AND constraint_type = 'R'
AND status = 'ENABLED';
SELECT dt.owner, dt.trigger_name
FROM dba_triggers dt
WHERE table_owner = l_owner
AND status = 'ENABLED';
SELECT dq.owner, dq.name
FROM dba_queues dq
WHERE owner = l_owner
AND queue_type = 'NORMAL_QUEUE'
AND TRIM(enqueue_enabled) = 'YES';
SELECT object_owner,
object_name,
policy_group,
policy_name
FROM dba_policies
WHERE object_owner = l_owner
AND enable = 'YES';
SELECT FND_TS_MIG_CMDS_S.nextval
FROM SYS.dual;
SELECT lineno, subobject_type
FROM fnd_ts_mig_cmds
WHERE object_type = 'POSTMIG'
AND object_name = 'AQ_TM_PROCESSES';
SELECT value
FROM v$parameter
WHERE name='aq_tm_processes';
INSERT INTO fnd_ts_mig_cmds (lineno,
owner,
object_type,
subobject_type,
index_parallel,
object_name,
migration_cmd,
migration_status,
execution_mode,
partitioned,
generation_date,
last_update_date)
VALUES (l_lineno,
p_schema,
'POSTMIG',
l_value2,
'NOPARALLEL',
'AQ_TM_PROCESSES',
l_string,
'GENERATED',
'P',
'NO',
sysdate,
sysdate);
UPDATE fnd_ts_mig_cmds
SET migration_cmd = l_string,
subobject_type = l_value2,
generation_date = sysdate,
last_update_date = sysdate
WHERE lineno = l_lineno;