Server Administration :: Privileges Required For Export
Dec 1, 2011
I have one question regarding privileges required for export.I read in a document that
Quote:
If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.
what are the system privileges required in EXP_FULL_DATABASE role to perform export objects contained in another users schema?
For procedures, we need to grant EXECUTE privilege.
For example:
grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.
Which Privileges required to create a trigger like after logon on database. I have grant Create any trigger privilege to user but still not able to create and give me an error insufficient Privilege.
We have 2 Unix SunOS server, named as SUNOS01 and SUNOS02, there is some space need to add in SUNOS02 and for that I have to bring Database down on SUNOS02. When I checked I found that the DB is pointing to SUNOS01, i.e. there is no DB on SUNOS02, So, Do I required to shutdown the DB on SUNOS01 or it is not required to do anything from Database side. I mean to say should I ask Unix team to bring down the unix server and add the space.Also, if DB shutdown is not required then what I have to do afetr they have added space and bring the server up from DB side.
I found some of the tablesapces whose used% exceeded 80%.Based on this output i thought of increasing the datafile.But there are many tablespaces of DATA1 whose autoextensible column some are set to NO, and some are set to YES. I believe we dont need to bother about the tablespace whose Autoextensible column set to YES as Oracle will automatically manage until the Maxsize of it. But some tablesapce of DATA1 are set to NO. Should i recommend to add size for this DATA1 tablespace whose autoextensible are set to YES/NO or leave it as it is.
Please see the column below.
Quote: FILE_IDTABLESPACE_NAMESIZE(M)USED(M)FREE(M)MAX(M)AUTOEXTENSIBLE% USED 59DATA13276732741.1325.7532767YES100 60DATA13276732741.6925.1932767YES100 61DATA1327673274026.8832767YES100 76DATA13270032193.31506.560NO98 77DATA13270030858.51841.380NO94 78DATA13270029476.563223.310NO90
I have 10 important schemas in my database,and i want to give only select privileges from SCHEMA PRODUSER to other 10 schemas. And also want that the new objects that are created in PRODUSER after granting the privileges are also have select privileges. Is it possible that i should directly grant select privileges from one schema to other without granting via individual objects(script to grant individual privileges from individual objects)
Details are as below: database version: 9.2.0.8.0 OS version: Microsoft Windows Server 2003 Standard Edition Service Pack 2
The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.
WHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE
ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.
I have one user CD_APP. I have one partition table CD.T_FCDR_DT. User has got ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table..
Now when I try to drop a partition, I get error as below: ------------------------------------- SQL> show user USER is "CD_APP" SQL> ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES; ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES * ERROR at line 1: ORA-01031: insufficient privileges -------------------------------------- Do I have to grant some other privileges for this user.
I have been granting "ALTER ANY MATERIALIZED VIEW" to a role but not able to see the same granted in DBA_SYS_PRIVS.
I have used the image for the first time, so pasting the sql prompt output as well:
SQL> select * from v$version where banner like 'Oracle%';
BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production SQL> grant ALTER ANY MATERIALIZED VIEW to OPS_1ST_LINE;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee = 'OPS_1ST_LINE';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- OPS_1ST_LINE CREATE SESSION NO OPS_1ST_LINE ALTER ANY SNAPSHOT NO
SQL> commit;
Commit complete.
SQL> select * from dba_sys_privs where grantee = 'OPS_1ST_LINE';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- OPS_1ST_LINE CREATE SESSION NO OPS_1ST_LINE ALTER ANY SNAPSHOT NO
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE = 'ALTER ANY MATERIALIZED VIEW';
no rows selected..why is this grant not been shown. Is "ALTER ANY MATERIALIZED VIEW" not present for Oracle 9i database.
We are migratingfrom [IBM P5 - AIX 5.3 - Oracle DB 10.2.0.1 64bit]to [IBM P7 - AIX 6.1 - Oracle DB 10.2.0.5 64bit]
The new database is already up and running, our next step now is the following: 1. Create Tablespaces (done) 2. Export the Database from P5 3. Import the dump to P7.
We have this following options:
I. Use a Windows XP workstation 32bit with Oracle 10.2.0.1 Database software to export from P5, then import the dump to P7. (Export/Import Utility 10.2.0.1 Windows 32bit) II. Use the Export Utility of the P7 (10.2.0.5 AIX 64bit) to do the export and import. III. Use the Export Utility of P5 (10.2.0.1 AIX 64bit) then use the Import Utility of P7 (10.2.0.5 AIX 64bit)
BTW, our colleague tried to do the following: A. Use Windows - Export Utility 10.1.0.2.0 - 32bit to make a dump of our database (Oracle 10.2.0.1.0 AIX 64bit) B. Use Windows - Import Utility 10.1.0.2.0 - 32bit to import the dump file from step A to our new database (Oracle 10.2.0.1.0 AIX 64bit)
But after issuing imp system/password@NEWDB file=(a.dmp, b.dmp... c.dmp) full=y ignore=y statistics=none
The import seems to hang here...importing SYSTEM's objects into SYSTEM
I have installed Oracle server and SQL Server on separate machines which cause me a time delay of 21 seconds for each execution. Why executions delay? I have set RPC out (true).
Note: My main concern is either if the query is correct/incorrect it executes for 21 seconds._
Another case when I have both servers on the same machine it executes in milliseconds. I have tried Following methods in SQL SERVER.
*1, Using OPENQUERY:* SELECT * From OPENQUERY(Linked Server Name,’Select * from OracleTableName ‘)
*2, Using Exec:* DECLARE @sql NVARCHAR(MAX); SET @sql =(’Select * from OracleTableName ‘); EXEC (@sql) AT Linked Server Name ;
How to reduce the time delay caused for the execution?
I will have to proceed with Oracle 9 database refresh from production server to integration server. 5 biggest schemas must be exported and imported. They constitute 97% space used in a database. This is very big database so I would like to be sure that everything will go smoothly. That is why i want to ask you some questions.
Have you got any clues for me before I start with exp/imp? From my side i will tell you that I will have to exp/imp schema by schema because there is small space both on production and integration disk for a dump. First thing I thought are dependencies between schemas that are exported and that which are not, and also between schemas that are exported/imported one by one.
This is procedure that I plan:
For every schema that is to be refreshed { 1. Export schema with ROWS=N CONSTRAINTS=Y 2. EXPORT schema with ROWS=y CONSTRAINTS=N 3. Import schema from step one 4. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT. 5. Import schema with rows } ALTER TABLE ENABLE CONSTRAINT
With above procedure i think that I will avoid problems with dependencies between schemas exported/imported one by one. But my concern is if there are any dependencies between those schemas and schemas that are not exported. Is there an way to check it before refresh ?
any other utilities that we can use to load data from our PROD server (10g) to DEV server (9i)? I've read some related topics here that it's not possible to import from a HIGHER to LOWER versions of Oracle. We've tried (many times) EXPorting selected tables from the 10g dB, then IMPort it to the 9i dB and we've haven't succeeded anyhow. PROD & DEV have a different schema/owner but the same table structures.
I am trying to export schemas from 10g to 11g. The NLS_CHARACTERSET for 10g is WE8ISO8859P1 and the NLS_CHARACTERSET for 11g is WE8MSWIN1252. Is it fine or do I need to change the character set, so that I will be able to successfully do the export/import?
i am having a problem when trying to export my DB,i could run an import fine,i have ran the catalog.sql,catproc.sql,catexp.sql and utlrp.sql again.Is it because the client and DB are different?How can i solve this problem
exp usr/pass file=exp_full.dmp log=exp_full.log full=y consistent=y Export: Release 8.1.7.0.0 - Production on Wed Nov 28 13:40:04 2007 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
I was asked to do export/import of some schemas from 10g(linux) to 11g(AIX) using original expor/import method. I did not consider the character set and started doing export and import. while exporting, I get questionable statistics error in export log file. In the import log, I see the error like CREATE DATABASE LINK "xxxxxxxxxxxxx" CONNECT TO "xxxx" IDENTIFIED BY...
i need to export master data in excel sheets to our database and we use toad too. How i can export the data with the use of macros in excel. how i can export data from excel to oracle.
how can i monitor the export and import job and how increase the export and import job performance.
can i monitor the export and import job by checking the log and dump file created by export and import and can its performance increase by configure parallism. m i right or not?
