SQL & PL/SQL :: Extract DDL Of Constraints Of User In Database?
Feb 10, 2012How to extract the ddl of constraints of a user in a database?
View 6 Replies
ADVERTISEMENT
Extract Index DDL From Database?
Apr 27, 2011Is there a way, i can extract index DDL from my database?
View 1 Replies View RelatedReplication :: Goldengate Extract For 12C Database
Aug 30, 2013 I am implementing GG 11g r2 for 12C database. But i am getting below error. My question Why Goldengate needs specific package ... Since this is homogeneous & heterogeneous.
/u01/12c_database_software/goldengate/dirtmp.
2013-08-30 05:28:44 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, ext1.prm: Positioning to Sequence 66, RBA 25067536, SCN 0.0.
2013-08-30 05:28:44 ERROR OGG-01028 Oracle GoldenGate Capture for Oracle, ext1.prm: ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_INTERNAL_CLKM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.
2013-08-30 05:28:44 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.
Database details
----------------
SQL> select object_name, object_type from dba_objects where object_name='DBMS_INTERNAL_CLKM' and object_type in ('PACKAGE');
no rows selected
SQL> select object_name, object_type from dba_objects where object_name='DBMS_INTERNAL_CLKM';
no rows selected
PL/SQL :: Create Trigger To Insert Data From One User To Another User In Same Database?
Apr 16, 2013I Created One Trigger as Follows
CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW
[code]......
Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist
Server Administration :: How To Extract License / CSI In Oracle Database
Jul 6, 201211:53:51 SQL>select * from v$version
11:53:55 2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
All, I have an infuriating problem and I'm hoping for some advice. Well actually it is a number of problems but I'll use one example as a microcosm.I'm going to use as the example:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 912K(100)| |
| 1 | HASH JOIN | | 3412K| 940M| 826M| 912K (1)| 03:02:28 |
| 2 | HASH JOIN | | 3412K| 787M| 699M| 748K (1)| 02:29:39 |
| 3 | HASH JOIN | | 3610K| 657M| 557M| 560K (1)| 01:52:07 |
[code]...
Now then, this query runs perfectly and as expected (direct reads/DB scattered reads) until it hit step 13 in the plan (FTS of T6). At step 13 it basically enters into a (rather fatal) process of reading almost nothing but undo exclusively.
Now then, I wonder why this might be. Of course the natural reaction is to say "they data is changing, its read consistency (optional rookie insult)". Now, I found this hard to believe that the volume of data could be so extensively changed, keeping in mind its a good sized table and it's doing a full access.
I checked the DBA_HIST_SEG_STAT(DB_BLOCK_CHANGES_DELTA) view for the period and in the run up, i.e. the explain plan steps 2-12 took approx 20 minutes. In this window, less than 1% of the blocks in the table T6 were changed (approx 3-4k out of a 500k block table).Nearly every access of this table is reading undo, yet since this query started, 99% of the table is unchanged. But the query is behaving as if everything it reads has altered since it began.
To be clear, I understand the read consistency etc and expect it, what I DONT expect is the virtually the ENTIRE table scan operation routing via undo when I'm accessing the full table, yet hardly any blocks are altered after I start running.
Note this query/application seems to fetch the rows in blocks of 1k, its almost as if its re-executing for each fetch, but I wouldn't have expected that.
In summary:
>Query starts
>hits final table
>almost exclusive undo reads
>under 1% of block changes in read table since query began
Is there any other way anyone knows of that might cause a session to read into undo beyond a session changing data after the query executed?
JVM :: How To Extract Java Class Objects From Oracle Database
Jun 26, 2013How to Pick / Extract the java class files from the database.? We have not maintained the latest codes in the oracle application server where java class code is residing.
All the Java Classes are available only in database. So we need to pick the latest java class code from production environment. In TOAD we tried but all class objects are listing at the left side but we are unable to take the code. So how can we take the latest codes(java classes) from the Production Database as a backup.
SQL & PL/SQL :: Extract Data From Local Database Table To Text File?
Jun 27, 2013i want to extract data from my local database table to text file using plsql
View 4 Replies View RelatedSecurity :: To See Any Database For The Operations Of Sys User Or Any Other User
Aug 23, 2011I have enabled Auditing in my oracle Database but I am not able to see any database for the operations of sys user or any other user in my "SYS.AUD$" and "SYS.FGA_LOG$" tables.
Value for the parameter "AUDIT_TRAIL" is set to "db,extended".
I am working as "SYS" user and I have shutdown and again startup the database but neither there was any information in both the tables nor I can see any files at the destination specified by "AUDIT_FILE_DEST".
SQL & PL/SQL :: Copying Data From Table (with No Constraints) To Table With Constraints?
Jun 13, 2012I want to create a store procedure to copy data from a source tables(which may not have any constraints defined) to a table which has primary key, foreign key and unique key constraints.
Any records which are rejected due to these constraints not being satisfied need to go another table.
Once the initial data load is done, these procedures need to be automated(through cron) to do the future incremental uploads in the same manner.
SQL & PL/SQL :: How To Know Particular User Existed Or Not In Database
Apr 22, 2013May i want to know SQL command to find particular table name in a particular user account existed or not in oracle 11g database
View 8 Replies View RelatedFind Youngest User In Database?
Feb 6, 2007How do you find the youngest user in a certain table
select a.illness_desc
from uma.pc_illness a, uma.pc_patient b, uma.pc_diagnosis c
where b.patient_no = c.patient_no and c.illness_code = a.illness_code
and b.age = '21';
i currently have this (it also needs to display their illness which i have done) but i can do only if i cheat and see what the youngest user is, so instead of b.age = 21 i need something that will search the database and find that youngest age
we are using SQL+
SQL & PL/SQL :: User Restriction On Database Level?
Dec 13, 2011For the list of userid's, how to find the list of OS/Restricted Shell ID's at the database level ?
View 7 Replies View RelatedPL/SQL :: Unlock Oracle Database User?
Jun 14, 2013Could the Oracle database user be configured so that it will not get locked with infinite wrong tries.
View 3 Replies View RelatedReplicate User In Oracle Database
Jan 31, 2013I Have one oracle user ABC which is having 10 Tables.
Now i want to create replica of this user XyZ which can access only 5 tables from . This user having All access.
Example :
Any DML changes made in XYZ these changes should be reflected in ABC user. and vice a versa.
How can i achive this in ORACLE DB.
Security :: How To Find Current User In Database
Sep 22, 2011query to find the current user accessing the database
View 5 Replies View RelatedServer Administration :: Connect 9i To 10g With Same Database And User?
Oct 19, 2012last two years i am using oracle 9i in windows 2003 server but now i installed oracle 10g on another server (PC) , i want to connect 9i to 10g with same database and user .
View 3 Replies View RelatedServer Utilities :: Clone Database User
Aug 1, 2011I want to create such a script to clone the Database user with the new name. Just like we do normal import and export I want that i should enter just the username of the existing user and username of the new user I want to get created, the password for the same.
It should create the new user with all roles and the default roles and privileges of old user.
Access Oracle Database From Root User?
Dec 18, 2012i have an linux instance running oracle 11g as oracle OS user. i am able to connect the DB from clients.
i need to access this DB from OS root user. what should i do.
Trace Particular One User SQL Activity In Oracle 10G Database?
Jan 27, 2013I want to trace particular one user SQL activity in Oracle 10G database.
Note - Not only one session / Not for all Database activity
Database User Account Lock And Expire
Jun 28, 2012I have altered an user account and set the account status as expired.When I tried to connect with that user, oracle prompted for changing password.
But, after I changed the password, I got an error message as ora-01017 invalid username/password logon denied password unchangedWhat is the cause and solution for it? I am using 11g database and 10g client.
Security :: Find Out The User Access The Database?
Nov 8, 2011Is there a way to find out the user access the database?
View 1 Replies View RelatedMaximum Number Of Processes Per Database User
Jun 1, 2013I am using Oracle 11g R2. (11.2.0.3)
I know that in the profiles of the user I can set limit for the number of sessions per database user, using the below command:
alter profile <profile_name> limit sessions_per_user=<required_number>;
I need to set maximum number of processes for the application user so in case they opened too many number of processes on the database they do not block the automated jobs to be run.My questions:
1. set a limit on sessions_per_user is it the same as a limit on processes? 1 proccess= 1 session ? Is there any way to limit the number of processes (not sessions) for a database user?
2. I am using RAC so the number of sessions is per the whole database, while the max number of processes is by instance (not database), so even I set a limit on the number of sessions (above the limit of processes for one of the instances) one instance may face ORA-20 errors, while the max number of sessions did not hit the limit. is there any workaround for this case?
Constraints On A Table
Feb 25, 2008How do know all the constraints that are present on the data in a table in oracle ??
View 2 Replies View RelatedSQL & PL/SQL :: Constraints In Describe
Nov 1, 2013I want to display columns along with their constraints using describe command
Is it possible?
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- -------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL & PL/SQL :: Trigger Sum Constraints
Mar 7, 2010Basically, I need to fulfil the constraint:
"student cannot have more than 100 points"
I need to use a trigger to do this, but I don't understand how this would work.
Written in plain english I imagine it work work something like:
AFTER UPDATE
WHERE student.lessonid = lesson.lessonid
IF SUM(lesson.points) > 100 then ROLLBACK
lessonid is the only field relating the two tables.
--
How to fulfill that criteria, whether it be using triggers or not.
SQL & PL/SQL :: Constraints On RECORDS?
Mar 21, 2011How to create any constraints other than NOT NULL in PLSQL RECORD.
View 5 Replies View RelatedSQL & PL/SQL :: Constraints On Objects?
Mar 5, 2012Ive created a scenario to what im trying to achieve here so ignore how its set-up attribute wise.
CREATE TYPE object_obj AS OBJECT (
obj_id NUMBER,
NAME VARCHAR2(20),
age NUMBER) NOT FINAL;
/
CREATE TYPE object_ext UNDER object_obj (
course_name VARCHAR2(20));
/
CREATE TYPE object_ext2 UNDER object_obj (
location VARCHAR2(20);
/
CREATE TABLE object_tab OF object_obj(obj_id PRIMARY KEY);
/
Now for the course_name i need to make sure it can only be on of these three values ('Science','Math','English'). I can't find a way to apply this constraint without making a table. But then that creates another issue of how to insert the data from the main supertype (object_obj) and i only want the object_tab table and view the subtypes via a select query.
PL/SQL :: What Are Constraints Copied
Aug 23, 2013When a table structure is copied from another table.What are the constraints copied and why?Unique keyPrimary keyForeign KeyCheckNot null.
View 10 Replies View RelatedHow To Find Length Of User Account Password In A Database
Jun 13, 2013how to find length of the user account password in a database?
View 1 Replies View RelatedSecurity :: How To Find User And IP Address From Oracle Database
Jun 19, 2013Last week we have realized that a user who connects through SQL Developer(as nothing wrong found in application server logs) has made a serious change in the database which created a real mess. The user has done some mischief in some calculations by making some inserts and updates in some important tables in our production database.
How can I find which user or from which IP the change is made.
