I am working on a migration of a database from on-premise to the cloud. I am using a scripting tool to aid in the migration, and one of the required parameters is as follows:
"Supply a list of tablespaces you wish to migrate, delimited by commas. All the names must be on a single line. Additional lines will be ignored."
Well, jiminy cricket, how in the world am I gonna do that?
And there are quite a number of tablespaces. Will they even fit?
Luckily, this utility runs in linux, and lines can be of extended length.
I did not know how to do this on the fly, but I looked around, and came up with this query, which also contains some where clauses to qualify what was actually needed (we will not need to migrate SYSTEM, TEMP, or UNDO tablespaces).
The listagg function is an ansi-standard function that was actually first seen in Microsoft SQLServer, so I am going to guess they pushed it forward to the ANSI SQL standards committee, and I am glad they did so.
Here is my implementation, in Oracle, in case it can come in handy:
select listagg(tablespace_name,',') within group (order by tablespace_name) item_name_list
from dba_tablespaces
where contents = 'PERMANENT'
and tablespace_name not like 'SYS%';
