Server Administration :: How To View Table Constraints Information
Mar 13, 2010
I know that you can view all constraints in a table using $USER_CONSTRAINTS or $USER_INDEXES view. What i find difficult is that the information in those views is not really well presented.
I find it very hard to follow. For example, just to find out which columns are foreign keys and which tables they refer to is really tedious. Is there a simpler way to view all the constraints especially foreign keys of a table and including which table columns they refer to in the child table.
I like to see a diagramatic representaion of all the tables involved so i like to see both the parent and the child tables, which columns are related and using which FKs etc. is there an easier way to view this information?
View 4 Replies
ADVERTISEMENT
Aug 15, 2011
How to find the base table of a view,such as a view:
create or replace view vw_test as select * from tb_test;
Is there dic view to get the base table of view?
View 3 Replies
View Related
Sep 2, 2010
I want to find out the columns on which primary constraints are enabled.Which view must I use to check this.
View 2 Replies
View Related
Oct 29, 2010
What should I do with those disabled CONSTRAINTS (most were those file starts with LOGMNR) & TRIGGERS found on our production dB? Are there any impact once I enable those?
View 2 Replies
View Related
Jun 6, 2011
Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
View 4 Replies
View Related
Feb 7, 2013
I am getting unexpected issue while fetching records from dba_ts_quotas & DBA_USERS Views.
FYI..
select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
where username='DWHODS'
USERNAMEDEFAULT_TABLESPACETEMPORARY_TABLESPACE
DWHODS DWHODS_TBS_DATADWHODS_TEMP_DATA
select tablespace_name, username, bytes / 1024 / 1024 "Used MB",
[code].....
View dba_ts_quotas is giving tablespace name "USERS" where DBA_USERS showing default tablespace name "DWHODS_TBS_DATA".
View 1 Replies
View Related
Nov 22, 2010
How Can We Get Hardware Information In Oracle Database 8i for example harddisk size,volume,processor etc. i want database run only specified harddisk
View 1 Replies
View Related
Feb 23, 2010
How do i find out the information regarding the latest patch installed for ORACLE_HOME..
OR
If given a patch number, how do i find out whether it's applied to ORACLE_HOME
View 1 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Jun 13, 2012
I 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.
View 6 Replies
View Related
Jan 27, 2011
I wanted to know the checkpoint information and the interval of checkpoint for my instance.I have checked alert.log file for the same.
Is there is any other way or any view exists to get that information?
View 5 Replies
View Related
Apr 4, 2012
I can desc the view,but i can not get the ddl of the view,how can i do?
SQL> desc oss03.VW_OSS_PM_MONTH_3_201112;
Name Null? Type
----------------------------------------- -------- ---------------------------
RECDATE NOT NULL NUMBER(8)
USERNUMBER NOT NULL VARCHAR2(32)
MODULEID NOT NULL NUMBER(8)
CHANNELID NOT NULL NUMBER(8)
OPERID NOT NULL NUMBER(10)
CONTENTID NOT NULL NUMBER(10)
SERVICEID NOT NULL NUMBER(10)
OPERTYPE NOT NULL NUMBER(10)
PROVCODE NOT NULL NUMBER(5)
AREACODE NOT NULL NUMBER(5)
SERVICEITEM NOT NULL VARCHAR2(20)
ORDERTYPE NOT NULL NUMBER(8)
[Code]....
View 19 Replies
View Related
Oct 12, 2011
Are there some oracle views about subpartition template?
View 3 Replies
View Related
Oct 26, 2011
I created a view with parameters l_id and l_name, how can i find them in oracle view?
Create Table tb_test
(
Id Number,
Name Varchar2(64)
);
Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;
View 3 Replies
View Related
Feb 22, 2012
There are some sys views can not found. why?
SQL> connect / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> desc dba_users;
ERROR:
ORA-04043: object dba_users does not exist
View 2 Replies
View Related
Jun 27, 2012
When you create a MAV, you automatically get a hidden column and an index. Here's an example,drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
create table emp as select * from scott.emp;
create materialized view mv1 enable query rewrite as
select deptno,sum(sal) from emp group by deptno;
select object_name,object_type from user_objects;
select index_name,column_name from user_ind_columns where table_name='MV1';
select column_name,hidden_column from user_tab_cols where table_name='MV1';
select deptno,"SUM(SAL)",sys_nc00003$ from mv1;
View 2 Replies
View Related
Jan 17, 2013
SQL> drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED view log on afccv.tbl_voicechat
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
i dont want to kill the sessions or anything is there any way to set prority to do this task?
View 2 Replies
View Related
Jul 14, 2012
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies
View Related
Mar 18, 2010
the query, to get the foreign key constraints and related table fro give table.
View 2 Replies
View Related
Feb 25, 2008
How do know all the constraints that are present on the data in a table in oracle ??
View 2 Replies
View Related
Jul 1, 2010
I would like to print all the constraints on all columns given a table name.
Example:
SQL>@constrnts
Enter Table Name: emp
empno ---- Primary key
deptno ---- foreign key references Dept(deptno)
View 16 Replies
View Related
Jun 28, 2012
I am having two tables
Table 1 having 16 cror rows .
Table 2 having 1000 rows
I joined both the tables and fetch all inforamtion from big table for those key present in small table.Join query taking more time to fetch the rows .
View 2 Replies
View Related
Oct 15, 2010
When we create a duplicate table, we use the below query:
create table table2 as select * from table1;
But table2 is created without any constraints inherited from table1.Can I know how can i create the table with all the constraints existing in parent table.
View 5 Replies
View Related
Jul 11, 2011
I want to truncate table partition but I'm getting error:
CODEORA-02266: unique/primary keys in table referenced by enabled foreign keys
because of table has a nested table. Currently this column is not in use so I could drop it, but I want to avoid it (table is huge). Is there another posibility to do truncate partitions in this table? ALTER TABLE ... SET UNUSED doesn't resolve the problem.
View 2 Replies
View Related
Feb 16, 2011
can a table level check constraints have conditional checking (if else clause or case conditional structures) and checks which are limited through something like a where clause which inside the table level check constraints.And can a table level check constraints refer to a column in another table column which should have a the same value.
View 1 Replies
View Related
Apr 8, 2010
I knew, how to enable or disable a constraint on a table.
>alter table <table_name> disable constraint <constraint_name>;
But if we know all the data is correct, it's better to disable all constraints to improve the performance.
We are allowed to Disable or Enable only a single constraint.
Enable or Disable All Constraints.
View 11 Replies
View Related
Sep 14, 2011
i am a beginner in SQL and want to create a table with the following constraints :
'orderno' should be primary key and start with o
'clientno' should be foreign key
'ordr date' is not null
'salesmanno' foreign key
'delytype' default value 'f' and possible value ( f or p)
'delydate' cannot be less than order date
'orderstatus' values ('in process.......)
here's my
create table sales_order(orderno varchar2(6) like 'o%' primary key,
clientno varchar2(6) foreign key references client_master(clientno),deldate date not null,dellyaddr varchar2(25),salesmanno varchar2(6) foreign key references salesman_master(salesmanno), delytype char(1) default 'f',billyn char(1),delydate date check > orderdate,orderstatus varchar2(10)check ('in process','fullfilled','cancelled');
View 5 Replies
View Related
Dec 20, 2011
how to copy data and constraints from one table to another table
View 8 Replies
View Related
Jul 22, 2004
i imported a .dmp file but i got this error:
Connected to: Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
Then i run the catexp.sql using SYS user. But i still got that error.
View 20 Replies
View Related
Dec 24, 2010
through Difference between table Level, column Level ,row level constraints.
View 8 Replies
View Related
