New Tables And Indexes Created Not Show In Dba-segments View?
Aug 8, 2012
I have created 3 tables and some indexes, but these objects do not show up in dba_segments view. Is this a normal behaviour? Previously, with dictionary managed tablespace, I can specify the minimum extent to create, when the table/index is created. But I'm not sure how the locally managed tablespace work.
I'm using Oracle 11g R2 (11.2.0.1.0) for Microsoft Windows (x64), running on Windows 7.For the purpose of reproducing this issue, I have created the tablespaces as follow:
CREATE TABLESPACE CUST_DATA
DATAFILE 'd:appasusoradataorcl11gr2CUST_DATA01.DBF' SIZE 512K
AUTOEXTEND ON NEXT 256K MAXSIZE 2000K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
Create Materialized View table_name Tablespace FG_Report Build Immediate Refresh Force Start with SYSDATE next SYSDATE+5/1440 As Select * from table_name@dblink;
after creating, i don't see indexes on the the materialized view created. where as in actual table we have indexes..
Can we replicate indexes along with Materialized views?
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor: SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID) FROM GAR_DW.ARTICULOS o10475528, GAR_DW.EMPRESAS o10475602, GAR_DW.L_DIA o10475639,
I want to create a meta data table which has all the views users can access with a description of what the view was supposed to do.
Is there a trigger that can be created that fires when a view is created? To make it easy only one user in this system (oracle 9.2.0.8) has the create view privilege.
I know I could write a procedure to create views with dynamic sql that would do this but that seems a bit of overkill.
the sequence of events should go like this: view created trigger fires record with view name and creation date inserted in a table later, manually go to the table and add explanatory comment
or can I add a comment to a view like you can to tables?
I know that truncate is a ddl operation that removes all the data from the table and set the HWM to very beginning. I am curious to know, is anyhow indexes will be affected on the issuance of truncate command or we need to rebuild the indexes after issuing truncate.
Also is there any way to know that how many rows/blocks a select statement is scanning because of the effect of HWM even the table has 0 rows.
i am using oracle 10g. is there a way to find out the over fragmented tables/indexes ,so that reorganizing or rebuilding them will improve sql performajnce?
So our situation is pretty simple. We have 3 tables.
A, B and C
the model is A->>B->>C
Currently A, B and C are range partitioned on a key created_date however it's typical that only C is every qualfied with created date. There is a foreign key from B -> A and C -> Bhave many queries where the data is identified by state that is indexed currently non partitioned on columns in A ... there are also indexes on the foreign keys that get from C -> B -> A. Again these are non partitioned indexes at this time.
It is typical that we qualifier A on either account or user or both. There are indexes (non partitioned on these) We have a problem with now because many of the queries use leading wildcards ie. account like '%ACCOUNT' etc. This often results in large full table scans. Our solution has been to remove the leading wildcard.
We are wondering how we can benefit from partitioning and or sub partitioning table A. since it's partitioned on created_date but rarely qualified by that. We are also wondering where and how we can benefit from either global partitioned index or local partitioned indexes on tables A. We suspect that the index on the foreign key from C to B could be a local partitioned index.
I have one control table as below.I want to rebuild all indexes for the tables in control table.
The control table is having the following data.
SEQ_IDTABLENAME SCHEMA_NAME 1GEDIS_ORDER_FORM_STATES ALL 2GEDIS_NOTES ALL 3GEDIS_CARD_TYPE_AUDIT APRT 4FAX_HEADER OMS
In the control_table schema_name "ALL" means this is for 30 schemas(The table is existed in 30 schemas).Except for schema_name "ALL" ,the table is existed in the particular schema(The table is existed in the only one schema).
I tried the following code it is executing for all 30 schemas(ALL).But it is not executing for specific schemas.
CREATE OR REPLACE PROCEDURE Rebuilding_index IS l_sql VARCHAR2(4000); CURSOR cur_tab_schema IS SELECT tablename,schema_name FROM control_table3;
I have tried a lot by alternate solutions like rearranging the order of tables in join and moving where conditions before but no success...Its a bottleneck and I could not have indexes on these tables in production...I want to change the approach in subquery
SELECT g.COLUMN1, g.COLUMN2, e.COLUMN3, g.COLUMN4, MIN(e.dat1) KEEP ( DENSE_RANK FIRST ORDER BY date2 Desc) * -1, min(to_char(date3,'dd-mm-yyyy')) [code]....
I have noticed that Oracle text related objects, particularily the $I tables are some of the largest objects in our database. I have been actively pursuing utilizing Oracle advanced compression in our databases for OLTP table compression and LOB object compression. I have been unable to find any documentation or notes on if it is advisable to implement either table OLTP or LOB compression for Oracle text objects.
1. After creating a view, how do I associate that view with certain level of security. As in, only a few users must be allowed to access that view. 2. How can I create a new user-login and password for my database application?
I have set up a view that pulls news & events records.
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.category, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType FROM(so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
[Code].....
For events I want only the events that have a p.startDate equal to today. I have tried setting that line up a few different ways.
p.startDate = sysDate didn't provide any records for events p.startDate = TO_CHAR(sysDate, 'MM/DD/YYYY') freaked the system out and nothing appeared for either parts of the union
I am trying to create view that selects a number of rows from a table and has a calculated field which is a counter form 1 to n next to each row.
In the below example, s_name is repeated many times each with a differnet serial, I want to cteate a view that shows a counter for the occurences of s_name next to the serial.
CREATE TABLE XYZ ( SERIAL NUMBER PRIMARY KEY, S_NAME VARCHAR2(30) ); INSERT ALL INTO XYZ VALUES (1, 'ABC') INTO XYZ VALUES (11, 'ABC')
SELECT department_id FROM (SELECT department_id FROM employees UNION SELECT department_id FROM employees_old ) WHERE department_id=100; [code]....
The index has been created on both depart_id for the two tables. The only difference between the two I observed was the 1 recursive call for the 1st sql.and also, one additional view in the plan.There is a little difference in bytes sent over the network.
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind (decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind (decode(S,'I','I',null), decode(S,'I',style,null) )
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3) 2 style ,count(*) 3 from vh_tst_decode_ind 4 where 5 decode(S,'I','I',null)='I' 6 group by style 7 ;
I have 780(12*65) csv files generated from 65 databases.Now I have to load this 780 csv files into 12 tables created in my database for some monitoring and reporting purpose.to call the sql loader I am plannig to create 780 lines like below.
we know creating 780 control files is the difficult task.So I have created only 12 control files. is there any mechanism to pass a varible (planning to declare it in the sqlldr line) to the infile clause like below in sql loader?
aix 6.111.2.0.3 I have an expdp dump from prod to be imported to our test database.I have imported it using impdp, but to my surprise the tables were imported but lots of indexes were not created? even If I have used TRANSFORM=SEGMENT_ATTRIBUTES:N just to use the default USERS tablespace. How do I import the indexes separately, skipping the tables and other objects?
