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 Replieshow to take all schema metadata export except one schema (scott)
can i use like EXCLUDE=schema:"IN('SCOTT')
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'?
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]....
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.
SQL> select * from t1;
NAME
----------
JACK
JOHN
JENN
SQL> select * from t2;
NAME
----------
JACK
PAUL
SQL> select a.name from t1 a, t2 b where a.name <> b.name
2 order by name;
NAME
----------
JACK
JENN
JENN
JOHN
JOHN
I would have expected to see the following:
name
-----
JOHN
JENN
PAUL
how to fix this query? In additon, is there a way to print the table name or some arrows (>>, <<) to show which table the values came from
I.e
name
=====
JOHN t1 or <<
JENN t1 or <<
PAUL t2 or >>
I use oracle 11g r2
i have created a table for objects
create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);
and inserted many values (examples below )
INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:
SELECT
'Enquiry' as Origin,
job.job_number,
enquiry.enquiry_number as Defect_or_Enquiry,
job.site_code,
central_site.site_name,
job.priority_code,
priority.priority_name,
job.target_comp_date,
'Target Completion Date required' as Due_Date
[code]....
Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.
I need to create a report that will show all activity for the day that the report is run. The report will be run or auto-refreshed throughout the day. So, at 10am, 11am, 2pm, 4pm etc. I just need to display quantities received, shipped etc. for that same day. Since the report will not be run at a fixed time, I can't use sysdate - .5 for example.
View 6 Replies View RelatedI have a scenario for which I need to show same record twice.
i.e
SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul
Is this possible with a single query.
i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.
I have such a database "ZOO" in oracle PL/SQL. URL.....The query is "Show all the keepers, who cares of all animals in the zoo.
View 14 Replies View Relatedi am trying to use the query builder in my 11g xe database but vainly ,there are no results shown ?
View 6 Replies View RelatedI'm trying to create a query which will show sales of products by month, something like this: -
JanFebMarApr
Prod 11020511
Prod 200510
Prod 31921015
The data is held on 3 tables: -
TB_ARTICLES
ID
CODE
DESCRIPTION
TB_TRANSACTIONS
ID
BOOKKEEPING_DATE
TRANS_DATE
[code]....
Running a query for one month is no problem at all, I just don't know how to create a cross tab style one.
the point is that I have a table with two columns and I am trying to find and easy way to show grouped data.
CREATE TABLE TEST_DATA(C1 VARCHAR2(1), C2 VARCHAR2(2));
INSERT INTO TEST_DATA VALUES('1','A1');
INSERT INTO TEST_DATA VALUES('1','A2');
INSERT INTO TEST_DATA VALUES('1','A3');
[Code] .....
With the sample data there are 4 groups based in c1 column:
1 : A1,A2,A3
2 : A1,A2,A4
3 : A1,A2,A3
4 : A1,A2,A4
5 : A6,A7
I'm trying to get and output like :
A1,A2,A3 : 1,3
A1,A2,A4 : 2,4
I was trying something like :
select t1.c1 as gr1, t2.c1 as gr2, t1.c2
from test_data t1,test_data t2
where t1.c1<>t2.c1 and t1.c2=t2.c2
and
(select count(*) from test_data t3 where t3.c1=t1.c1)=
(select count(*) from test_data t4 where t4.c1=t2.c1)
order by 1 asc, 2 asc
but I don't find the way to refilter to group the data as expected. The idea is find subsets and show the set of data and values in column c1.
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
View 16 Replies View RelatedI need a query to retrieve a particular string from all the columns and all tables in a specified schema.
For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.
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]....
Is there a query that will return all the table names that are not populated with any data within a Userid/Schema?
View 2 Replies View RelatedI want to list the tables in a schema which contains a particular value or data.
View 2 Replies View RelatedI 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 Relatedi 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 RelatedI'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?
I have a Query(report) which is running in <5 mins in one Scheme, where as the same is running for a long time in second schema. I have identified that an Index is scanning for more than 2000 Millions of records in second Schema, but this is scanning only 440 Millions in First Schema and hence it is fast. I am expecting the same to be done in Second schema.
I have verified the following
All records in tables in 2 schemas are same.
All indexes are same
Analyzed the tables
Gathered Histogram on all the columns as per the first schema.
But now i still have the same problem, don't know what could be the problem.
Table_nameNum_RowsBlocks
PRPSL_LST_T5866107159
PRPSL_WKFLW_ACTVTY_T5829904030
ITEM_CHR_VAL_T5134340104049020
ITEM_RGN_ASSN_T8571220137215
Also attached 2 screen shots of OEM Plans..
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 ?
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]...
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?
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
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