SQL & PL/SQL :: Data Dictionary View Not Available
Nov 1, 2011
Question 1) I have read the following statement in a PL/SQL book.
Quote:To check whether an existing procedure is compiled for native execution or not, you can query the following data dictionary views:
[USER | ALL | DBA]_STORED_SETTINGS
[USER | ALL | DBA ]_PLSQL_OBJECTS
However, I when i query the view USER_PLSQL_OBJECTS I get the following error message:
Quote:ORA-00942: table or view does not exist
Question 2) I have read the PLSQL_WARNING can be set to DEFERRED at the system level. However, I am unable to defer it. tell me how to apply defer caluse to following statement:
Quote:ALTER SYSTEM SET PLSQL_WARNINGS ='DISABLE:ALL'
View 10 Replies
ADVERTISEMENT
Sep 2, 2012
I created a materilaized view with partitions and i need to add partitions dynamicaaly using stored procedure for that i need to check whther the partiions with the same name existing.where can i see the partition names for a materilaized view is there any table like "USER_TAB_PARTITIONS"?.if the same query exists in the forum
View 3 Replies
View Related
Feb 6, 2013
Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.
View 2 Replies
View Related
Feb 6, 2011
can i modify oracle data dictionary
View 6 Replies
View Related
May 21, 2012
Changing Data dictionary by DML Sentences (e.g Insert, Delete, Update,...), Will be possible? Or just by DDL Sentences?
View -1 Replies
View Related
Sep 14, 2011
I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).
I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.
How can I find these references?
View 4 Replies
View Related
Feb 17, 2013
which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies
View Related
Sep 6, 2012
Oracle version : 11.2.0.3
Some DBA_views are huge with lots of columns ; a DESC command's output becomes less readable ; example DESC command's output of V$SESSION view.
Lets say I want to see only the date columns in a DBA_view or V$view ; How can I do that in a DESC like output or similair ?
View 2 Replies
View Related
Jul 4, 2011
In order to improve the performance of our live server, I am trying to do an exhaustive comparison with our test environment which is quite quick in spite of the fact that we port the data from Live every month.
There are no obviously slow queries appearing in the the top SQLs of AWR, we have optimised such things already. Right now it is about general uplift rather than SQL based tuning.
I picked up random SQLs and I noticed a marked differences in the execution time. Typically they are 3 to 4 times and there are cases much more than that.
1. I observed that, while the explain plan of the queries are same, trace of the queries give a different picture. I have observed that the recursive calls, consistent gets and sorts(memory) are quite high on Live.
2. I have no solid reasons to say this but my instincts tell me that the recursive calls is the major contributing factor. It is sometimes 2000+ for an SQL.
3. On googling more on that, it finally made me compare the data dictionary on the AWR report of test and Live.
The dc_objects caught my eyes. In that 4 hour AWR, there were about 10 million get requests and the pct miss was ~10. For similar load, the test server had 5 million gets with 0.08 PCT miss for 4 hours.
View 27 Replies
View Related
Sep 30, 2011
Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?
View 6 Replies
View Related
May 20, 2012
In my database I found some anomalies like
SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....
The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.
SQL> SELECT * FROM COUNTRIES;
CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...
why the country table is missing in user_segments data dictionary table.
View 8 Replies
View Related
Jul 7, 2010
I've got one database which was Initially upgraded from Oracle 8i to 10.2.04 running on windows. Most of the tabespaces are Dictionary managed. Do you think moving them to locally managed tablespace would give me better performance?
if Yes, what approach I should apply to move them to locally managed? I would like to do this with minimum/no downtime.
View 6 Replies
View Related
Dec 12, 2010
I have a name field like below; i need a query to display the name field in alphabetical order like in dictionary.
Emp Name
--------
Sam
John
Noel
Alen
Saaem
output would be
---------------
Alen
John
Noel
Saaem
Sam
View 5 Replies
View Related
Aug 20, 2010
I'm having a problem with grouping sets over dictionary views.
10g output:
SQL> select
2 -- 10g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments
[Code]...
ERROR at line 10: ORA-03001: unimplemented feature
Elapsed: 00:00:00.12
The query is fine over a non-dictionary table however (My actual code isnt against dual, but this makes it generic, the error is consistent)
11g output
SQL> select
2 -- 11g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments
[Code]....
ERROR at line 8: ORA-00904: : invalid identifier
Different error and that syntax, as far as I can tell, is sound - error message be damned.
An example of a query working on both versions is
select
employee_id,
sum(salary)
from
employees
group by grouping sets ((employee_id),null)
;
Am I missing something? Does grouping sets not work over the dictionary views?
Edit: Added version tags over the code to make it easier to read
View 4 Replies
View Related
Oct 22, 2011
I have a procedure in my schema. I have created a sys context name xyz for passing the date in that i am passing the sysdate to that context which is used in a view.i have used/called that procedure in form.
My problem is that when i am giving Grant select any dictionary to the user then form is compiled otherwise form is giving error procedure name must be declared. But for security reasons i don't to give select any dictionary to that user.
View 3 Replies
View Related
Aug 5, 2010
I get the Error like this frequently
ERROR at line 1:
ORA-20001: ORA-04022: nowait requested, but had to wait to lock dictionary
object
ORA-06512: at "MDWDBA.MDW_PKG", line 917
ORA-04022: nowait requested, but had to wait to lock dictionary object
ORA-06512: at line 1
why this occurs? and how to Resolve it?
View 2 Replies
View Related
Nov 5, 2012
Is it possible to create Dictionary Tablespace creation in 10g database?
View 5 Replies
View Related
Aug 19, 2011
How we can create dictionary managed temporary tablespace in Oracle 10g.
SQL> create temporary tablespace temp
2 tempfile '+GWDAAS04_TEMP_DG01/pimsb_gw/tempfile/temp01.dbf' size 500m
3 extent management dictionary;
create temporary tablespace temp
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
View 7 Replies
View Related
Apr 22, 2013
I would like to add Dictionary in Rich Text Editor of APEX.
I am using Oracle APEX 4.2 on Oracle 11g.
View 1 Replies
View Related
Dec 21, 2010
I want to create system table space's extent management dictionary with the syntax:
CODEcreate database
logfile
group 1 ('/u01/app/oradata/anand/redo1a.log') size 100M,
group 2 ('/u01/app/oradata/anand/redo2a.log') size 100M,
group 3 ('/u01/app/oradata/anand/redo3a.log') size 100M
datafile '/u01/app/oradata/anand/system.dbf' size 400M extent management dictionary
sysaux datafile '/u01/app/oradata/anand/sysaux.dbf' size 300M
default temporary tablespace temp tempfile '/u01/app/oradata/anand/temp.dbf' size 50M
but it is giving error
ERROR at line 6:
ORA-25141: invalid EXTENT MANAGEMENT clause
how can I make system tablespace's extent management dictionary?
View 3 Replies
View Related
Jan 6, 2010
I am trying to generate ddl for a baseline created in Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0. I used to be able to do this in a prior version of oem 10.2.0.4.0. Since the upgrade I am no longer able to view a baseline that has been generated successfully. I can also no longer generate ddl from a baseline capture. There are no errors ... it just seems to time out.
View 1 Replies
View Related
Feb 12, 2011
There is a offline_dictionary.ir file created by Logminer which is like 13G in our "DIAG" directory. What is the significance of this file and can it be removed?
I tried to search about this file, but couldn't get much on this. This is what I got from metalink doc id 755838.1
Quote:2. HM_CREATE_OFFLINE_DICTIONARY
The job is created by the 11g script catmwin.sql which mentions that this is a job for creation of offline dictionary for Database Repair Advisor.
From unpublished Note 602459.1 - 'What Is OFFLINE_DICTIONARY': 'The system job SYS.HM_CREATE_OFFLINE_DICTIONARY executes the dbms_hm.create_offline_dictionary package which creates a LogMiner offline dictionary in the ADR for DRA name translation service. The job for generating the logminer dictionary is scheduled during the maintenance window. This job can be disabled. '
View 3 Replies
View Related
Mar 27, 2010
I have created a view in that the primary key column is not included,so that we cannot insert data into that view,but i want to insert data into that view without using Instead of Triggers.
CM: swapped version and title so they're the right way round.
View 12 Replies
View Related
Jul 16, 2013
I want know how the values in view DBA_FEATURE_USAGE_STATISTICS gets updated i have already checked the metadata of this view but unable to find how / when the values are being updated.
View 3 Replies
View Related
Apr 14, 2011
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.
So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
View 4 Replies
View Related
Jan 11, 2012
I have a column which holds the data in the below format.
Source Data :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192>SNO_54-90-16489-008G
>SNO_SDPMDB-008G-11>SNO_54-90-18008-008G>SNO_54-62-08791-008G>SNO_20-81-00327
>SNO_SDPMDB-008G-12>SNO_54-90-17830-008G>SNO_54-62-08598-008G>SNO_20-81-00327
[code]..
Problem Statement :
Split the above data into individual components and create columns / aliases dynamically.If the column is present then place the data under the created column.
Example 1) :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
Result
Column Name : SKUCol_54-99
Data :SNO_SDSDQ-8192-BNSNO_54-99-24120-8192
Example 2)
>SNO_SDSDAA-002G-101-J>SNO_54-90-16002-002G>SNO_54-62-05781-002G>SNO_20-81-00135-5
Column Name : SKU54-90Col_54_62Col_20_81
Data :SNO_SDSDAA-002G-101-JSNO_54-90-16002-002GSNO_54-62-05781-002GSNO_20-81-00135-5
Column Name can be derieved from the components like SNO_54-62-05781-002G
i.e. SNO_54-62-05781-002G ==> SNO_-05781-002G
"54-62" will give the Col_Name
The First Column will always have data starting with
SNO_SD%
This column is constant and will be named as SKU.
I need this data to be placed in the same record / row but under different columns as per the data set.Basically, Can the data be split into multiple parts based on delimeters and the columns are created based on the unique data in the parts that form the data in the column.
View 1 Replies
View Related
Feb 27, 2012
How can i load the data into a new table from view,when ever scheduler runs in the night, the data gets loaded in to a view and data coming from different tables and i should load that data every day and i dont want previous data again.The data should be loaded along with view .
View 6 Replies
View Related
Jul 25, 2012
My boss make a requirement in exist database as some user can view salary column at employment table by SQL and some user can view salary column at employment table by SQL.
The boss do not like to make changes front SQL. Ooracle 11g vault or Oracle Label Security is best for this requirement?
my oS is 2008 32 bit window and DB is 11.2.0.1
View 4 Replies
View Related
Jan 26, 2010
Is it possible to use Data grid view in Forms6i just as in Visual Basic?
View 7 Replies
View Related
Nov 15, 2012
I have got a simple materialized view question.
I have a view which is taking long time to get back results and this view is being used in various user queries. I noticed that the SQL of the view access some tables, data of them changes rarely. So it makes sense to retrieve that data in a materialized view and somehow re-use it.
My question is how can I use the materialized view and it's data from a query.
View 2 Replies
View Related
