This content has been marked as final. Show 4 replies
Check the timezone file setting in your XE target, it should contain timezone files up through v14, an unpatched 18.104.22.168 install has timezone files through v11. A directory listing of OH/oracore/zoneinfo should show several timezone_<n>.dat and timezlrg_<n>.dat files, its that simple to see which timezone versions are included.1 person found this helpful
The timezone files only affect how the engine handles TIMESTAMP WITH [LOCAL] TIMEZONE data types, and if the schema(s) at the export source don't include those data types, it should not be relevant at all.
$ sqlplus /nolog host dir <drive>:\...\oracore\zoneinfo ... readme.txt ... timezlrg_11.dat ... timezone_11.dat ... connect system ... password ... Connected. select * from v$timezone_file; FILENAME VERSION -------------------- ---------- timezlrg_14.dat 14
Unless there is something not quite right with the registry settings, does your XE host have another ORACLE_HOME installed? In Windows its possible that programs could be seeing a different OH.
And for an ora-1031 ... insufficient privileges running impdp ... the user does not have the privileges needed. Not sure how else the utility could gripe to be more clear.
Does the user have permission to read and write in the data pump directory? ==> grant read, write on directory <directory> to <user>;
Does the user have sufficient privileges to allow creating tables/procedures/other schema objects? ==> grant resource to <user>;
Does the user have sufficient space remaining in their tablespace quotas? ==> the resource role includes the 'unlimited tablespace' privilege.
in the oracore/zoneinfo folder I find only the following 4 files:
This is apparently what comes with Oracle XE11g, so timezone files of earlier versions are not included (but should they?).
I read some more blog postings concerning the various DST upgrades, but I fail to understand how I am supposed to patch XE11 if there is no support for this.
SELECT * from v$timezone_file; FILENAME VERSION -------------------- ---------------------- timezlrg_14.dat 14
At any rate, looking at the error message it seems that the required DST version is 11, which must be the version installed in the customer's DB, what I don't know is if I can ask him to send me the related dat files and simply put them in the oracore/zoneinfo directory or if I need to run some utility to let Oracle acknowledge their presence.
Concerning the "insufficient privileges" error, I've a tons of ideas on how to make it clearer, what about "ORA:01031: insufficient privileges, CREATE TABLE privilege not granted"? or "ORA:01031: insufficient privileges, failed to CREATE PROCEDURE xyz."?
It is that simple.
I mean, "insufficient privileges" as it is, it's more or less like saying "hey, there is a problem with something somewhere", go figure.
At any rate, this morning at least I could import the metadata without content from the dump using the CONTENT=METADATA_ONLY parameter, attempting to load the data separately is still failing.
1 person found this helpful
Yes they should- on my winXP box, I only have the v14 files. Hadn't noticed that before, and the all files versions 1 thru 14 should be in there for 22.214.171.124.
(but should they?)
May be able to just grab an install for the same platform under a trial license, and copy over the missing ones to the XE home. Or just the v11 ones that might get you past the TZ error.
Well, no argument the ora-1031 could be a bit more helpful. But read/write on the data pump directory and the ability to create objects by a user ... got to have those privs if its a non-dba role user doing the import.
Yeah, that's the fun part! ;)
there is a problem with something somewhere ... go figure.
Sorry for the delay in replying but I've been hindered by an annoying problem with the forum login.
After installing all the missing .dat files in the oracore/zoneinfo folder, I could import the dump without any problems, even if I am still missing a couple of versions, namely 12 and 13 that I could not find anywhere.
I still don't understand why Oracle didn't provide the files in the distribution of XE.