Server Administration :: How To Find The Base Table Of A View
Aug 15, 2011How 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?
ADVERTISEMENT
SQL & PL/SQL :: Create View Without Base Table?
Jun 25, 2012How to create a view without base table . some example?
View 8 Replies View RelatedSQL & PL/SQL :: Find Out Base Table Of Synonym?
Jun 10, 2011How to find out the base table of a synonym?
View 3 Replies View RelatedPL/SQL :: Update A Base Table Using Updatable Materialized View?
Sep 12, 2013I am using sqlplus. example to update a base table using a updatable materialized view.
View 17 Replies View RelatedServer Administration :: Change Datatype To HEX Notation / Base 64
Jul 10, 2013I am currently in the process of migrating our database from US7ASCII to AL32UTF8 using DMU.
I am stuck at a point where I have encrypted data that, when the conversion happens, will be destroyed. Oracle suggests to move the data which is currently stored in VARCHAR2 datatype, to a "character set safe way" like hex notation or base64 before converting.
Server Administration :: How To Find Updated Table
Mar 9, 2012how to find a table is updated and when the table is updated.
View 1 Replies View RelatedServer Administration :: How To View Table Constraints Information
Mar 13, 2010I 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?
Server Administration :: How To Find Which Table Or Index Needs To Be Reorg
Apr 21, 2009How to find which table or Index needs to be reorg..??
IS there any Query!!!.
Server Administration :: Unable To Drop User - Table Or View Does Not Exist
Jun 6, 2011Im 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
Performance Tuning :: Creating Index On Base Tables Of A View?
Dec 9, 2010I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?
The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.
Server Administration :: How To Change Existing Materialized View To Normal View
Jan 17, 2013can we change the existing materialized view to normal view? if yes how?
View 2 Replies View RelatedSQL & PL/SQL :: How To Find Size Of View Or Table In Database
Jul 10, 2010How can we find the size of a view or synonym or table in a database. What is the code.
View 4 Replies View RelatedPL/SQL :: Join Between Table With User Based Content And Base Table
Jul 30, 2012I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
Server Administration :: Unable To Find Server Process In Oracle Database For Specific Application / Client
Feb 13, 2013I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
Server Administration :: Find Or Set New Oracle Instance Name On Backup Server
Sep 22, 2010We performed image copy of production Oracle server (OS and instances) to a backup server. After a few weeks, we try to restore a latest Oracle database backup from production server to backup server. As we know, Oracle instance must be unique on the network.
Even we log on to backup server and bring up the instance, I think that still point to production instance since all init file, TNSNAMES.ora and listener file are still same. If we restore the database, we will end up bring down the production instance and restore on top of productions. How to change instance name on backup server including TNSNAMES, sqlnet, listener files in order for us to restore Oracle database from production to backup server?
Server Administration :: How To Get Ddl Of View
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]....
SQL & PL/SQL :: XML Parsing Into Base Table
May 20, 2010I want to load XML into base table using PL/SQL procedure.For that I have wrote procedure but that does not work well .
View 4 Replies View RelatedServer Administration :: Subpartition Template View
Oct 12, 2011Are there some oracle views about subpartition template?
View 3 Replies View RelatedServer Administration :: Create View With Parameters?
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;
Server Administration :: System View Cannot Found
Feb 22, 2012There 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
Forms :: Changing A Block Not Base Table
Oct 15, 2012How do I change the status of a block to change?
View 7 Replies View RelatedServer Administration :: How To Find Unused Columns
Nov 9, 2011is there a way to find out which unused columns in oracle?
View 8 Replies View RelatedServer Administration :: Find Whether Database Link Is Being Used?
Jul 26, 2011I am having database A and database B and I am inserting into database b by selecting some records from database a using db link.Is there any way to find whether database link is being used?
View 6 Replies View RelatedServer Administration :: How To Find When The Datafile Was Resized
Jan 17, 2012We need to find when any datafile was resized ( if at all)in a tablespace. Actually, by noting the created date from v$datafile , we used to know the data growth in a tablespace. Now as the number of datafiles have increased, we want to resize them. This diagnostinc have to be done without changing/adding anything in DB.
View 12 Replies View RelatedServer Administration :: Where To Find Alert Log Errors
May 10, 2010where i can find an alert log errors so whenever they occur in my RAC so i can comprehend them very well. I am trying to know that what can be the errors when u have RAC with ASM and SAN .
View 3 Replies View RelatedServer Administration :: Materialized Aggregate View Index?
Jun 27, 2012When 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;
Server Administration :: ORA-00054 - Drop MATERIALIZED View Log?
Jan 17, 2013SQL> 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?
Server Administration :: Cannot Find SQL Plan For Insert Statement
Dec 27, 2011I can not find the sql plan for insert statement,why?
Select sql_text From v$sql a
Where a.SQL_ID = '0yungrk19a277';
-------------------------------
INSERT INTO OS_USERBILL_ACTV_READ_MON_DT
(MONTHNO, MAILCODE, OPERTYPE, PROVCODE, AREACODE, DAY_TOTALCOUNT, TOTALCOUNT,
CREATETIME, MODIFYTIME, SENDER_TYPE, SENDER_DOMAIN)
VALUES
(:B9 , :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , SYSDATE, SYSDATE, :B7 , :B8 )
[code]....
Server Administration :: Find Last Run Duration Of Job Which Was Scheduled Using DBMS_JOB
Jun 20, 2010Is there any way to find the last run duration of a job which was scheduled using DBMS_JOB?
View 3 Replies View RelatedServer Administration :: How To Find The Size Of Individual Folder In ASM 10g
Mar 16, 2012Would like to know, how to find the size of individual folder in ASM 10g !
Also I am not able to find the PATH/name of the raw devices in ASM 10g !