SQL & PL/SQL :: Unable To Recreate DBLink In Another DB / Schema?
Mar 25, 2010
I'm unable to recreate the db link (private) in another schema/database. Even its a schema object, its come without schema name while extracting from database using metadata api.
code is below (just for extracting)
CREATE OR REPLACE FUNCTION DDI.DBLINK_DDL
RETURN CLOB
AUTHID CURRENT_USER
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
I got the below error while executing the procedure. What the procedure doing is recreate the objects of one schema into another. After recreating some objects it throws the error.
I have the metadata of an object in one of my database table as xml. I failed to recreate the object in the other schema using metadata api. I developed a stored function to do the above job. My function doesn't throw any error as well as it doesn't create the object.
My code snippet is
CREATE OR REPLACE PROCEDURE DDI.move_table( table_name in VARCHAR2, from_schema in VARCHAR2, to_schema in VARCHAR2 ) AUTHID CURRENT_USER
[code]....
The schema where i create and execute my function have dba privilege also.
i have created a dblink between oracle and mssql and tried testing the link its giving me below error.
SQL Error: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified {IM002} ORA-02063: preceding 2 lines from MSSQL 28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:" *Cause: The cause is explained in the forwarded message. *Action: See the non-Oracle system's documentation of the forwarded message.
I have checked the dsn data source i have created, i am able to test the DSN successfully.
We have created a new db link. But we are not able access the remote database. When we are trying to access any table using db link system is getting hanged.
We would like to know what are the parameters/permissions which affect the db link access either at data base level or at server level.
One observation we made is that that particular db link is not getting dropped when we tried to drop it.
I am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.
SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END; * ERROR at line 1: ORA-44004: invalid qualified SQL name ORA-06512: at "SYS.DBMS_STATS", line 13210 ORA-06512: at "SYS.DBMS_STATS", line 13556 ORA-06512: at "SYS.DBMS_STATS", line 13634 ORA-06512: at "SYS.DBMS_STATS", line 13593 ORA-06512: at line 1
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/******** schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
i missed a tablespace that includes more than 20,000 indexes for reason. there is nothing to do with it without recreate these indexes.i could find these index's ddl createment from "dbms_metadata.get_ddl" packages.
but the DDL createment like this:
CREATE UNIQUE INDEX "AMV"."SYS_IL0000241510C00002$$" ON "AMV"."DR$AMV_C_CHANNELS_DESC_CTX$R" ( PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "APPS_TS_TX_IDX" PARALLEL (DEGREE 0 INSTANCES 0)
recreate paramater file if you lost init.ora or spfile.ora
STEPS
SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'D:ORACLEORA92DATABASEINITGPT2.ORA' How to resolve ORA-01078 with LRM-00109 error?
For resolving these error, you should need to replace init.ora or spfile if you have backup of same files. If you don't have backup then go to alert.log and copy all parameters in one file and save as "init.ora". Modify same init.ora with some parameters which need single quotation mark " ' " like background_dump_dest,controlfiles,db_name and "dispatchers ......)". Connect as sysdba in SQL*Plus and execute command "create spfile from pfile=" or startup pfile or startup.
I have a RedHat Server with Oracle Ent. 8iI did a full export of all my current data bases ( DCTEST, DCPROG, DCUSAC, DCCAND)I now have the DC*.dmp files. How can I know import these files into Oracle Database 11R2?
Do you have a Step-by-Step how-to ?IF I don't have the scripts to re-create the databases how to go forward ?
I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:
CREATE TABLE table_iot( ...) ORGANIZATION INDEX OVERFLOW ...;
Recreate Steps:
1) Populate global temporary staging table (gtt) with data -- where gtt is staging for target index-organized table (iot) 2) Lock the target index-organized table (iot) 3) Copy old iot data to gtt -- gtt now contains old and new data 4) Create new index-organized table (iot2) from gtt -- iot2 now contains old and new data
[code]...
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:
A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.
i am using 11g and pl sqlk developper, here is the script snippet
MERGE INTO GDIEXP.OEM_SCHEMAS@OX10CC30_GDI.LOTO_QUEBEC.COM O USING ( SELECT T.GRANTEE, I.INSTANCE_NAME FROM DBA_ROLE_PRIVS T, V$INSTANCE I WHERE T.GRANTED_ROLE = 'PRIVPROPRIETAIRES') DRP
[code]...
An oracle 02070 is popping up, i don't what's the real reason behind it, i only know it works when i don't use the db link. What can i do ?
I just created a dblink from our Oracle 9i database to a SQL Server 2008 R2 Server using oracle heterogeneous services. The dblink seems to be OK and I can query the remote SQL Server database but I only get results for numeric columns.
Say the remote SQL Server table was created like:
CREATE TABLE [MITLOC] ( [MLCONO] [numeric](3, 0) NOT NULL, [MLWHLO] [nchar](3) NOT NULL, [MLITNO] [nchar](15) NOT NULL, [MLWHSL] [nchar](10) NOT NULL, [MLBANO] [nvarchar](20) NOT NULL, [MLCAMU] [nvarchar](20) NOT NULL, [MLFACI] [nchar](3) NOT NULL, [MLWHLT] [nchar](2) NOT NULL, [MLSTQT] [numeric](15, 6) NOT NULL)
The remote dblink is named after M3PREP.
Then if I run:
CODEselect MLCONO, MLSTQT from MITLOC@M3PREP => this works OK and I get the two columns.
If I run:
CODEselect * from MITLOC@M3PREP => I get the same results as in the previous query and all nchar, nvarchar type columns are missing, I only get the same two columns.
If I run:
CODEselect MLCONO, MLWHLO from MITLOC@M3PREP=> I get ORA-00904 - the column name entered is either missing or invalid.
I set up my .ora init file like this, I suspect there is something missing here, is it related to the NLS_LANG or so?