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;

[code].....

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Recreate Objects Of One Schema Into Another

Apr 8, 2010

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.

code is...

1 CREATE OR REPLACE PROCEDURE SCHEMA_IMPORT6
2 AUTHID CURRENT_USER
3 AS
4 V_Objid NUMBER;
5 V_Xml XMLTYPE;
6 V_Xml_Ind XMLTYPE;
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Unable To Recreate An Object?

Mar 15, 2010

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.

View 9 Replies View Related

Backup & Recovery :: Logical Block Corruption - Recreate SYSMAN Schema Index

Mar 3, 2010

I am facing problem of logical block curruption.

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
3 61454 1 1417157166 LOGICAL
3 31256 1 1417011117 LOGICAL

OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
SYSMAN MGMT_METRICS_RAW_PK P MGMT_METRICS_RAW

Can i recreate the SYSMAN schema index by dropping this one?

View 17 Replies View Related

Networking And Gateways :: Unable To Test Dblink Between Oracle XE And MSSQL?

Jul 30, 2013

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.

View 2 Replies View Related

Server Administration :: Unable To Access Remote Database Using DBLink

May 21, 2010

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.

View 2 Replies View Related

Server Administration :: Unable To Gather Statistics Of Neither Schema Nor Table?

Jul 13, 2011

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

[code]....

View 17 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

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

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Materialized View Recreate

Oct 2, 2012

we have a materialized view (in Oracle 11g), usually this MV we easily drop and recreate it.

But last few days, i am trying to drop a MV, but running a long time but no result and it keeps running. not dropped also.

Bcos, db performance is good since we tried to a view using script, it is fine. Problem seems only with Materialized view.

where and how to check what is the issue?

View 1 Replies View Related

SQL & PL/SQL :: How To Retrieve A View Definition To Recreate

May 20, 2013

How to retrieve a view definition to re-create it somewhere when its size is more than 32767 bytes?Here are some constraints :

- We are in Oracle 9.2.0.8, meaning that we cannot use the DBMS_METADATA package.
- We cannot concatenate char and long : things like
set long 100000

select 'create or replace view my_view as ' || text from dba_views where view_name = 'MY_VIEW';
are not possible.

View 31 Replies View Related

Server Administration :: How To Recreate Lobindex

May 24, 2011

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)

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

*ERROR at line 2: ORA-00936: missing expression

note: this tablespace is in ORACLE EBS R11 name "APPS_TS_TX_IDX" how to recreate LOBINDEX?

View 1 Replies View Related

Recreate Parameter File When Lost Or Corrupted?

Jul 1, 2010

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.

View 2 Replies View Related

Server Administration :: How To Recreate Control File

Jun 12, 2012

I can not run the control file treace file,why?

[oracle@hxl oradata_bak]$ more run_sql.sql
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100

[code]...

View 12 Replies View Related

Enterprise Manager :: How To Recreate File For EMCTL

Jul 19, 2010

I cannot start our Enterprise Manager services for our newly created db. I'm encountering this error:

"$ emctl start dbconsole
Unable to locate file /appl1/home/oracle/10.2/xxx-dbs_abcd/sysman/config/emd.properties : No such file or directory"

I've been executing this command for a no. of times at the ORACLE_HOME directory:

$ ./emca -config dbcontrol db -repos create

.. but still I cannot start EMCTL dbconsole.

View 16 Replies View Related

PL/SQL :: Materialized View - Update Query Without Drop And Recreate

Aug 24, 2012

i have a little doubt in Materialized view. i created a materialized view and log with the following query,

create table test_mv (a number ,b number )
alter table test_mv add constraint t_pk primary key ( a );
insert into test_mv values (1,2);

[Code]...

Now i want to update the query in the MV as 'Select a from test_mv' . for that i tried with

*'ALTER MATERIALIZED VIEW product_sales_mv AS SELECT a from test_mv;'*

But it throwing error,

Error starting at line 5 in command:

alter  MATERIALIZED VIEW product_sales_mv   AS SELECT  b  from test_mv

Error report:

SQL Error: ORA-00933: SQL command not properly ended +00933. 00000 - "SQL command not properly ended"+

*Cause:+   
*Action:+

i guess i am doing wrong. i want to update it without drop and recreate

View 2 Replies View Related

Recreate Global OraInventory Directory For 9iR2 In Solaris SPARC 64 Bit?

Jan 22, 2011

recreate global OraInventory directory for Oracle 9iR2 in Solaris SPARC 64 bit.

View 2 Replies View Related

Export/Import/SQL Loader :: Clone Oracle 8i Recreate Database

Aug 2, 2013

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 ?

View 2 Replies View Related

Data Guard :: Recreate Standby Using Full Datafile Backup

Dec 6, 2012

I have to recreate my standby database due to huge archive gap. I have full datafile backup of primary.

View 24 Replies View Related

PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?

Apr 24, 2013

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:

ALTER TABLE admin_docindex MOVE;

View 3 Replies View Related

Server Administration :: Accessing Single Schema From Multiple Schema Logging?

May 16, 2011

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

View 2 Replies View Related

Security :: Schema Consolidation And User Schema Mapping Based On Service

Jul 24, 2011

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.

View 5 Replies View Related

Client Tools :: How To Create A Table In Another Schema As In Existing Schema

Apr 26, 2010

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.

View 10 Replies View Related

Server Utilities :: How To Take All Schema Metadata Export Except One Schema (scott)

Jul 5, 2012

how to take all schema metadata export except one schema (scott)

can i use like EXCLUDE=schema:"IN('SCOTT')

View 4 Replies View Related

Export/Import/SQL Loader :: Imp Records Of One Schema Into Another Schema Of Same Table

Nov 3, 2012

I had done following steps,

schemas(toy,toys)

1) i open the session of toy schema

First i taken backup of table

create table bck20121103_himan as select * from himan;

Backup table is created.

After taking the Backup table

delete himan;(deleting the records)

2) i log in to another session(toys)

exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/

3) i log in to toy schema

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y

i tried the above statement it taken so much of time..

Later i tried

I log in to toy session

i rename the table with other name.

later i imported

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y

it's successfully imported.

View 3 Replies View Related

Server Utilities :: How To Move Schema To Another Schema In Same Database

Jan 5, 2009

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.

View 14 Replies View Related

Sequence Name In Oracle Database Schema As Well As Timesten Schema?

Jan 13, 2011

How to use same oracle sequence name in Oracle Database schema as well as timesten schema?

View 1 Replies View Related

Server Administration :: One Schema Versus Multiple Schema

Feb 1, 2010

single schema setup or multiple schema setups for an application development. Which option is recommended and pros and cons of these methods?

View 4 Replies View Related

SQL & PL/SQL :: Execute Immediate With DBLink

Feb 23, 2011

I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.

execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....

What's the correct way to format this kind of dynamic query?

View 4 Replies View Related

PL/SQL :: ORA 02070 And DBLink?

Jul 20, 2012

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 ?

View 3 Replies View Related

Getting Data Over SQL Server DBLink

Apr 17, 2012

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?

CODE#
# HS init parameters
#
HS_FDS_CONNECT_INFO = M3PREP
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = M3PREP.LOG

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved