SQL & PL/SQL :: Returns Comments Of All Objects Of Schema Using Metadata Api

Mar 2, 2010

A function returns the comments of all objects of the schema using metadata api. I used DATABASE_EXPORT as object_type for open function. which name (eg: base_object_name, base_object_schema...) should i use to get comments of single object. My code is..

1 CREATE OR REPLACE function f_depen_obj
2 return clob
3 as
4 a number;
5 b number;
[code].....

I got comments of all objects when i commented 11th,12th lines. Now i want the comments of EMP table of SCOTT schema. I got the following error ..

SQL> select f_depen_obj from dual;
ERROR:
ORA-31603: object "EMP" of type TABLE not found in schema "EMP"
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 3806
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "SCOTT.F_DEPEN_OBJ", line 17

no rows selected...i got output (comments of single object) when i use COMMENT instead of DATABASE_EXPORT in open function(line no: 9). But i need to use DATABASE_EXPORT as a object_type. Which name is suitable for set_filter procedure instead of 'NAME'?

View 3 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: How To Query To Show Schema About Metadata In OracleDB

Apr 23, 2010

i got problem that i want to know Metadata in my Oracle DB 2 version there're OracleDB 9i and OracleDB 10g ,So i want to know about sql command to show all metadata of these Database, because i want to list My metadata and improve to Metadata's Standard(ISO/IEC 11179). How can i show all metadata of these database. is it the same sql command line with this 2 version Database to show all metadata?

View 1 Replies View Related

Statement Separate In SQLPlus / It Successfully Returns Schema

Mar 28, 2008

I am running this statement in a Java DAO and it results in an SQLException. I'm trying to get the DB schema. If I run this statement separate in SQLPlus, it successfully returns the schema. Why this is failing in my Java program?

ResultSet r = s.executeQuery("SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM S_DUAL;");

View 5 Replies View Related

SQL & PL/SQL :: What May Be Disadvantages Of Schema Objects

Jul 13, 2010

I was wondering whether we all think of disadvantages or is it we always love the concept and use them..so recently i started out to think what may be the disadvantages of schema objects.

For example Packages has lot of advantages like Information hiding, Scope of varaibles declared in spec, dependency crisis etc, then i read somewhere that if a package is referenced then the entire package will be downloaded in the memory.

So my question is what if the package is very large would it accomdate in the memory without any leaks , i am assuming that its like LEAST RECENTLY USED mechanism. i have a question for you about disadvantages

1. Packages
2. Cursors
3. Collections, includes Associative arrays, VARRAY, nested table
4. materialized views
5. views
6. indexes
7. having more than 100 columns in a table need to spilt it or to keep in the same table.

any object can have its own disadvantages.

View 3 Replies View Related

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

How To Move Database Objects From One Schema To Another

Sep 6, 2011

I wanted to move all db objects from one schema to another schema.

View 4 Replies View Related

SQL & PL/SQL :: Invalid Objects In SYSMAN Schema

Jul 19, 2012

Recently I migrated our Oracle to new machine using exp/imp on schema basis. After import finished I had tons of invalid objects in database. I ran utlrp.sql script and lots of them got validated. Than I recompiled manually in EM those are left but two invalid objects (MGMT_JOB_UI description and body) in SYSMAN schema gave error while recompiling.

Now when I click on any scheduled jobs to edit it or view its schedule, EM throw following error:

X Error
jobType - jobType page property expected

I think its related to that invalid package. The errors while compiling the specification are as follow:

Line # = 50 Column # = 1 Error Text = PL/SQL: Declaration ignored
Line # = 65 Column # = 9 Error Text = PLS-00201: identifier 'JOBRUNTABLETYPE' must be declared
Line # = 88 Column # = 1 Error Text = PL/SQL: Declaration ignored
Line # = 107 Column # = 9 Error Text = PLS-00201: identifier 'JOBEXECTABLETYPE' must be declared

The Package specification is as below,

AS---------------------------------------------------------------------------------- type definitionTYPE CURSOR_TYPE IS REF CURSOR;-- Get the targets for this step, if any-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN SMP_EMD_STRING_ARRAY;-- Get the targets for this step, if any, as a comma separated string-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets_str ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN VARCHAR2;-- Get the parameters for this job, filter as specified for this jobtypePROCEDURE get_visible_params ( p_job_id RAW, p_exec_id RAW, p_params_out OUT CURSOR_TYPE);-- Get the URI for this uri_use-- see emSDK/job/dtd/UriSource.java for uri_use constantsFUNCTION get_display_uri ( p_job_type IN VARCHAR2, p_uri_use IN

[code]....

View 16 Replies View Related

What Privileges Other Schemas Have On Objects Of Schema A

May 19, 2013

i have schema A and i want to check who(which schemas) can access schema A's objects and what privileges other schemas have on objects of schema A.

View 7 Replies View Related

Loose Coupling Among Objects Within Oracle Schema?

Sep 30, 2010

I am building an information service that manages Suppliers. The suppliers are used by our billing system, tender system and sales system. Though 60% of the attributes of supplier are unique to each system, there are still 40% attributes of Supplier that are shared across the systems.

My objective is to build a flexible system, so that change to one individual system's data, should not impact other systems. For example, if i need to make certain tables offline for upgrading them, it should not impact rest of the systems that need supplier information. What is the best way of achieving this? Should all the different context specific attributes live in one schema, but deployed on different table spaces? Also, the read and update may happen more for one set of attributes than the other. How should i logically represent them via one model, but deploy them in such a fashion that they can evolve independently?

View 3 Replies View Related

Replication :: Replicate (A) Schema Objects To (B) At Server?

Mar 30, 2011

I told about Oracle multi master replication below.

Can I replicate some objects of "X" schema at A server to "Y" schema at B server using MM replication?

View 13 Replies View Related

Server Utilities :: Getting Count Of Objects After Schema Refresh

Sep 13, 2013

Iam having the following query, After executing schema refresh using export & import , getting count of database objects comparison to be done,

-- SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;
SQL> SELECT 'select count(*) from '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;

The output expected was to display each table name in a schema following below with corresponding number of records to be displayed, but it wasn't showing correctly.

View 7 Replies View Related

Server Administration :: Deleting Unwanted SYS Schema Objects?

May 25, 2011

I was importing one schema from Oracle 10g to 11g using traditional import. I imported as a SYS user, so all the objects created in SYS schema. how can I remove these objects and retain only default SYS objects

View 11 Replies View Related

Server Administration :: USERS Tablespace Should Not Contain Schema Objects?

Jan 31, 2012

I heard that USERS tablespace should not contain any other application schema objects.

If the above statements is true , why it should not contain other schema objects ?

View 7 Replies View Related

Backup & Recovery :: Restore Schema And Its Associated Objects Once Dropped?

Sep 11, 2011

Is there a way to restore the schema and its associated objects once it is dropped?

1. The flash back database feature is not ON in the database where schema was dropped.

2. The data files/ table space has not been dropped

3. There was no export taken for the schema.

View 4 Replies View Related

Query To Identify Invalid Objects In Database Per Schema

Sep 29, 2012

I need a job to get executed for every 1hour.Like i need a query to identify invalid objects in database per schema ,invalid type and this is a job to run every hour.How to schedule it.I only know that dba_jobs have the info.

View 3 Replies View Related

Server Utilities :: IMP-00058 - Error While Trying To Import Objects Into Schema

Feb 23, 2013

I get import error while trying to import objects into schema.

Export file created by EXPORT:V11.02.00 via direct path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing DEMO's objects into TEST
. . importing table "TAB1"
IMP-00058: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'

i understand we need to grant the user space resource on the tablespace as below.

ALTER USER <user> QUOTA UNLIMITED on <tablespace_name>

My another question is can we grant QUOTA UNLIMITED on <tablespace_name> to user ?

View 2 Replies View Related

Server Utilities :: Find All Objects Of Schema HR In Imported Database

Jul 25, 2010

I imported a schema HR from export DUMP ....i can find all the objects of schema HR in the imported database... but i got an error for a plan_table which is assigned to USERS tablespace in the source database.. ...

HERE COMES THE ERROR I GOT DURING IMPORT:

[oracle@localhost mom]$ imp file=exp_schema_ref.dmp log=imp.ref.log fromuser=hr touser=hr commit=y
Import: Release 10.2.0.1.0 - Production on Mon Jul 26 00:03:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys/sys as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing HR's objects into HR
. . importing table "COUNTRIES" 25 rows imported
. . importing table "DEPARTMENTS" 27 rows imported
. . importing table "EMPLOYEES" 107 rows imported
[code]....

View 1 Replies View Related

Server Administration :: Impact Of Up-to-date Statistics On Main Schema Objects

Feb 29, 2012

I am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.

For this I wanted to:

first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.

However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.

View 1 Replies View Related

Server Administration :: Validate OLAPSYS Schema Objects After Transferring Database

Apr 21, 2011

We transferred our Oracle database 11.1.0.7 from windows 2003 enterprise edition 32 bit to windows 2008 enterprise edition server 64 bit.Database is working fine but we have 53 uncompiled objects which are related to OLAPSYS and public as follows

OLAPSYSALL$OLAP2_AW_CUBE_AGG_LVLVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_MEASVIEW
OLAPSYSMRAC_OLAP2_AW_DIMENSIONS_VVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_OPVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_SPECSVIEW
[code]....

Our business objec is working fine and all other schema does not have any uncompiled objects.How can we validate the OLAPSYS and PUBLIC schema.

View 11 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

Metadata For Some Of Partitions

Nov 10, 2010

i have many partitions in one table. i need metadata for some of the partitions. We have any option to get the metadata for partitions only.because while selecthing the table meatdata i am getting long script.

View 2 Replies View Related

SQL & PL/SQL :: Get Metadata Of Different Schemas

Jun 6, 2012

I'm trying to get the Create-Statements of all tables of several schemas:

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'<schmema>')
FROM (SELECT TABLE_NAME, OWNER FROM SYS.ALL_TABLES WHERE owner='<schema>') u

Is there a way to create a loop to handle several schemas listed in a array, for example?

View 10 Replies View Related

SQL & PL/SQL :: Derive Metadata From Cursor?

Jul 11, 2011

I have to build something like an export-interface-mappingtool. So user defines more or less a query and I have to display the attributes of this query to enable a mapping.

If I build up a ref cursor fom this query, I did not find the possibility to read its metadata (mostly Type/length is enough).

What I do not want to do is creating a temp. table or parse the query string to derive it from the dictionary.

Is it possible at all to get the metadata of a dynamic SQL ?

View 6 Replies View Related

SQL & PL/SQL :: Hierarchical Query To Get Metadata Information?

Mar 5, 2010

i'm trying to display the heirarichal relationship between the tables (parents-child-subchild).

[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID

[code]....

Expected output

table_name path
DEPT DEPT
EMP DEPT/EMP
EMP_AUTHORIZATION DEPT/EMP/EMP_AUTHORIZATION
EMP_AUTHRIZATION_CARD DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD
EMP_AUTHRIZATION_DUP DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD/EMP_AUTHRIZATION_DUP

but by using below query i am getting complete heirarichy.

SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Using DBMS-METADATA To Transform Tablespace DDL?

May 18, 2010

I'm currently busy with database consolidation, so I'm searching for a solution to generate some useful DDL to prepare the new target database before importing the application's data. This should include TABLESPACE DDL and all additional users with their grants.

So first I thought of developing a simple script, which will create the CREATE TABLESPACE DDL but with transformed datafile paths.But my throws some errors and I don't understand why:

ORA-31604: invalid NAME parameter "STORAGE" for object type TABLESPACE in function SET_FILTER
declare
l_hObject NUMBER;
l_Objddl CLOB;

[code]...

View 7 Replies View Related

Importing Metadata For Transportable Tablespace

Nov 20, 2012

We are migrating one of our databases from linux to AIX (testing phase). We're using transportable tablespace for the same. All steps are done and we're stuck during importing the metadata. While importing metadata we require to mention fully qualified datafile name which has to be plugged. The number of datafiles is 309 and the name of the all the datafiles has to be in one line. Now if we try to write it in one line using vi editor, the editor gives error 'ex: 0602-140'.

Is there a way by which we can overcome this situation?

View 5 Replies View Related

JDeveloper, Java & XML :: How To Get Metadata Of All Fields In SQL Query

Feb 22, 2012

I am using the dbms_xmlgen.getXMLfunction to get the result of any query in xml format.

With this XML I also want the metadata information about all the fields used in the query (passed to getXML function). Is it possible and how can I achieve this.

I tried to Google it but couldn't find any solution , it's easy to do it in java where I can get the resultset meta data from the resultset but I have to do it in Oracle function since I want the result in xml format and want to use the oracle XML API.

You may think why I need metadata , the reason is the application will later use this information to sort the data contained in these fields according to their data type provided to.

View 7 Replies View Related

Server Utilities :: Exporting Metadata Backup

Jul 19, 2011

i am using a schema which i need to take a backup of meta data only i am using exp utility

exp shan/shan@shan file=/backup_dump/shan.dmp log=/backup_dump/shan.log owner=shan rows=n

but it will return me below error, i have only access to user shan our client cant allow me to use system or sysdba schema or any other required grants or privileges so is there any way to take metadata backup of user shan from user shan.

EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

View 3 Replies View Related

PL/SQL :: How To Find If Column Default Value Is Stored As Metadata

Aug 17, 2012

I'm using Oracle 11g enhanced ADD COLUMN Functionality. Adding new columns with DEFAULT values and NOT NULL constraint no longer requires the default value to be stored in all existing records.

Sometimes we change DB columns from NOT NULL with DEFAULT to NULL with DEFAULT. This operation "materialize" column default. Is there an easy way (Dictionary view) how to find, that COLUMN default value is stored as metadata or is "materialized" ?

Oracle RDBMS version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

View 4 Replies View Related







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