SQL & PL/SQL :: DML Sentences Role In Data Dictionary?
May 21, 2012Changing Data dictionary by DML Sentences (e.g Insert, Delete, Update,...), Will be possible? Or just by DDL Sentences?
View -1 Replies
ADVERTISEMENT
SQL & PL/SQL :: Data Dictionary View Not Available
Nov 1, 2011Question 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'
SQL & PL/SQL :: Count Blank Space In Sentences
Jul 3, 2012Is There any way to count how many space in the sentences..Like this the sentences : " I AM BOY".
There is 2 space in the sentences.How can i find out using oracle query.
SQL & PL/SQL :: Can Modify Oracle Data Dictionary
Feb 6, 2011can i modify oracle data dictionary
View 6 Replies View RelatedData Dictionary - Find All References Of A Table?
Sep 14, 2011I 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?
PL/SQL :: Data Dictionary To See Foreign And Primary Key Of Table
Feb 17, 2013which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies View RelatedSecurity :: Any Role Where Exp_full_database Role Is Contained In It?
Aug 13, 2013Is there any Role where 'exp_full_database' role is contained in it? or it is compulsory to grant to the user for exporting objects.,
View 2 Replies View RelatedList Only Date Columns In Data Dictionary / Views
Sep 6, 2012Oracle 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 ?
Performance Tuning :: Data Dictionary Cache - Dc_objects
Jul 4, 2011In 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.
Server Administration :: Possible To Write Triggers On Data Dictionary Tables And Views
Sep 30, 2011Is 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 RelatedSQL & PL/SQL :: Connect Role In Dba-role-privs?
Oct 10, 2012But what I noticed is that user HIEL has a connect role but YONC does not. (Result set is below) Have searched for information but did not find what I was looking for. I did read something about backward compatibility.I'm leaning towards that thought since the other user YONC does not have this role.
Database: 11g R1
SQL> select * from dba_role_privs where grantee in ('YONC', 'HIEL') order by grantee;
GRANTEE GRANTED_ROLE ADM DEF
--------------------------- --------------------------- --- ---
YONC WCAIMS_INQUIRY NO YES
YONC ENDUSER NO YES
HIEL WCAIMS_CSR_ADDR NO YES
[code].........
Export/Import/SQL Loader :: Assigned To ROLE X Be Transferred To Role Y Via Datapump Import
Oct 18, 2013i have user with the name 'Rob' and this user has been assigned a role 'MY_SRC_ROLE' . I developed a table under rob schema and granted access to this table via role GRANT DELETE, INSERT, SELECT, UPDATE ON rob.emp TO MY_ SRC_ ROLE; I have 100 more users & they have been granted this role 'MY_SRC_ROLE'. These 100 users can now access emp table via Role 'MY_SRC_ROLE' without any issues. Now i took a datapump export & performed datapump import on target server which is also HP Unix with 11.20.3 .
On target server i have user 'JACK' and a role called 'MY_WORK_ROLE'. 5000 users have been granted 'MY_ WORK_ ROLE' on this server. I have used remap tablespace clause & remap schema clause in datapump import script. Once i performed an import , due to schema remap , i can see JACK now owns table 'emp', however grants are still not there, I tried searching on Google & oracle documentation, if somehow we can remap ROLE GRANTS also while doing datapump imp, but i couldn't find supporting syntax. can i assume datapump import is not capable to handle this particular scenario ? I was able to do it by manipulating sqlfile and replacing role name in that but i am looking for a sol. within datapump itself. how can grants assigned to ROLE 'X' be transferred to 'Role Y' via datapump import.
Create A Role Like DBA Role?
Sep 20, 2012is there a way to create a role just like DBA role?
View 6 Replies View RelatedSQL & PL/SQL :: Table Is Missing In User_segments Data Dictionary Table
May 20, 2012In 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.
Dictionary Managed Tablespaces
Jul 7, 2010I'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.
SQL & PL/SQL :: Sort Name Field In Dictionary Order?
Dec 12, 2010I 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
SQL & PL/SQL :: Grouping Sets / Dictionary Views
Aug 20, 2010I'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
PL/SQL :: Dictionary For Materialized View Partitions
Sep 2, 2012I 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 RelatedForms :: Grant Select Any Dictionary To User
Oct 22, 2011I 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.
SQL & PL/SQL :: Nowait Requested But Had To Wait To Lock Dictionary
Aug 5, 2010I 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?
Create Dictionary Tablespace Creation In 10g Database?
Nov 5, 2012Is it possible to create Dictionary Tablespace creation in 10g database?
View 5 Replies View RelatedSQL & PL/SQL :: Create Dictionary Managed Temporary Tablespace In Oracle 10g
Aug 19, 2011How 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
Application Express :: Add Dictionary In Rich Text Editor?
Apr 22, 2013I would like to add Dictionary in Rich Text Editor of APEX.
I am using Oracle APEX 4.2 on Oracle 11g.
How To Make System Table Space Extent Management Dictionary
Dec 21, 2010I 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?
Enterprise Manager :: Dictionary Baseline - Generate Ddl No Longer Works
Jan 6, 2010I 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 RelatedServer Administration :: Significance / Removal Of Logminer Dictionary File
Feb 12, 2011There 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. '
PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time
Feb 6, 2013Is 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 RelatedRole Of SGA And PGA In Oracle
Aug 19, 2005what is the role of SGA and PGA in oracle . And want kind of functionality they provide the oracle. i not understand what is system global Area and Program global Area (SGA and PGA).
View 7 Replies View RelatedSQL & PL/SQL :: Role Not Creating
Jun 3, 2010If iam create a new role in scott/tiger user why not creating. can any one explan me.
SQL> create role test_role;
create role test_role
*
ERROR at line 1: ORA-01031: insufficient privileges
SQL & PL/SQL :: Find Out Privileges On A Role
Oct 20, 2010How can i find out or list all the privileges that were given to a role with sqlplus cmd?
View 2 Replies View Related