I want to export Oracle database, but not full and not determined schemas; for example schemas that their names like 'IAS%' Or schema name like 'YS%'
I have written this command:
Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir
But i face error:
invalid positional parameter value 'like'
You will not be able to run your export using that command. The Data Pump export utility (EXPDP) does not use regular SQL. You cannot use shorthand SQL commands in clauses - the clauses have to be defined in accordance to Oracle's documentation. According to the documentation, an example of the EXPDP schemas clause looks like the following:
expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe
They have the schemas listed as comma separated values, and you will have to do something like this to perform your export. Instead of trying to use a query in the command itself, you could create a query that will return all of those schemas in comma separated format and simply copy and paste that list into the command.
To get that list, you could try running the following query:
select listagg(username,', ') within group(order by username) csv
from all_users
where username like 'IAS%';
This query uses the listagg function. If you are using a version of Oracle that does not support listagg, you could use the following query to accomplish the same thing. I found this query on a separate StackOverflow question, but it should work just fine:
SELECT SUBSTR (SYS_CONNECT_BY_PATH (username , ','), 2) csv
FROM (SELECT username , ROW_NUMBER () OVER (ORDER BY username ) rn,
COUNT (*) OVER () cnt
FROM all_users where username like 'IAS%')
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
Regardless of which query you use, you will now have a list of comma separated schemas that start with the string 'IAS'. For this example, let's say that list looks like IAS_1, IAS_2, IAS_3, etc. Your command would then change from:
Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir
To something more like:
Expdp admin/admin@orcl schemas=IAS_1,IAS_2,IAS_3 file=my_data.dmp directory=exp_dir
If schemas are created and dropped often within this database, you could run the query to update the list of schemas that are impacted and manually copy and paste the new list. If this is something that will happen to frequently for that, you could also create a Dynamic SQL script to generate and execute your EXPDP command.
Hope this helps!
Documentation on Data Pump: https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200
Documentation on listagg function: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
