SQL & PL/SQL :: How To Rename A Index
Aug 8, 2011How can i rename a index? is there a way except for drop and create.
View 2 Replies
ADVERTISEMENT
SQL & PL/SQL :: RENAME Table In Oracle 8i?
Nov 21, 2011how to rename table in oracle 8i ?
ALTER TABLE olname RENAME TO nwname;
Above command is not working.
SQL & PL/SQL :: Rename User Or Schema
Jul 12, 2012I have one use "A" . I would like to rename user "A" to "B". how its possible ?
View 11 Replies View RelatedAlter Tablespace Rename
Sep 28, 2012I would like to rename tablespace A to B , but when I do it tables after rename tablespace have columns tablespace_name.dba_tabkles in A . Is possibility to change it into without move ?
View 6 Replies View RelatedWindows :: Rename Command In Dos
Mar 13, 2011I want to rename
cf(1)
cf(2)
cf(3)
to
cf1
cf2
cf3
in DOS.
Rename Database With Physical Standby?
Jul 23, 2010I need to rename an Oracle9i database on windows which has a physical standby database running on a different machine.
I have the steps to rename the primary database but am unsure of it's impact on the physical standby database. It seems like I will have to re-create the physical standby database, since renaming the primary database involves opening the db with "resetlogs". It's a production environment hence can't play with it without knowing the results.
SQL & PL/SQL :: Generate Command To Rename Log File From ASM To Non-ASM
Apr 9, 2013I am in the process of recovering a database from an ASM environment to a non-ASM environment using RMAN. I've got a query to generate the command to RENAME the file:
SELECT 'ALTER DATABASE RENAME FILE '''||MEMBER||''' TO '''||'/u01/data/dev'||
SUBSTR(MEMBER,
INSTR(MEMBER, '/', -1, 1),
INSTR(MEMBER, '.', 1, 1) - INSTR(MEMBER, '/', -1, 1)) ||
DECODE(SUBSTR (MEMBER, 1, INSTR(MEMBER, '/', 1, 1) -1), '+DATA', 'a', '+FRA', 'b', 'c') ||'.log'';'
FROM V$LOGFILE;
When I run the query, this is an example of the result I get:
ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/group_3b.log';
ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/group_1a.log';
But I want to replace the "group" with "redo" and get the below result:
ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/redo_3b.log';
ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/redo_1a.log';
PL/SQL :: Rename Only Two Names In Column Report_headings1?
Jul 30, 2012I need to rename only two names in column report_headings1. But in order for me to see all other names I need to use all of them in decode function. Is there an easier way to write this Select Statement.
select days_start, days_to,
decode(report_heading1,'31 Or More days', 'Future', '00 Dispute/Pend', 'Dispute or Pend', '1 to 30 Days', '1 to 30 Days',
'31 to 60 Days', '31 to 60 Days','61 to 90 Days', '61 to 90 Days', '91 Or More Days', '91 Or More Days' ) report_heading1, report_heading2, type
from ar_aging_bucket_lines lines, ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id;
Server Administration :: Rename A Partitioned Table?
Mar 21, 2007i want to rename a table that has partitions.
alter table
testora.oldtablename
rename to
testora.newtablename;
ORA-14048: a partition maintenance operation may not be combined with other operations
SQL & PL/SQL :: Unable To Rename A Column For Original Table?
Feb 14, 2012i am not able to rename a column for original table, once i create a new one with Create table as select i can rename the new table column, but not the original one
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 13:10:10 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> show user
USER is "WEBSYSTEMD6"
SQL> alter table cbe rename column tnum to tnum_old;
alter table cbe rename column tnum to tnum_old
*
ERROR at line 1:
ORA-00900: invalid SQL statement
SQL> create table cbe_test as select * from cbe;
Table created.
SQL> alter table cbe_test rename column tnum to tnum_old;
Table altered.
SQL>
Recovery Manager (RMAN) :: Way To Rename Dbfs
Oct 8, 201211.2.0.3 win 2003. have direct aim to restore database from inc0+inc1 backup + recover up to latest archives made after inc1.
Steps have to be done:
1. SQLPLUS> startup nomount from pfile='......';
2. RMAN> restore controlfile from ...
3. SQLPLUS > alter database mount;
4. RMAN> run
{
[code]...
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2012 13:30:20
[code]....
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
795 108.13M DISK 00:00:16 05-OCT-12
BP Key: 831 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_00000
[code]...
Questions:
1. Why it ignoring "set new name" and db_file_name_convert??
2. Why no backup or copy of datafile 1,2,3,4 found to restore, it can seee it clearly through all its commands?
SQL & PL/SQL :: Copy Into Same Table / Recursive Rename Of Duplicates
May 11, 2011In MS Windows, if I copy a file and paste it into the same folder, I get a copy with the text 'Copy of' in front of the file name. If I paste it again, I get another copy with a different version number.
E.g.
sqlnet.log
Copy of sqlnet.log
Copy (1) of sqlnet.log
Copy (2) of sqlnet.log
I was wondering if I could copy existing rows into a table and do the same thing?
So, for example if I had this table:
create table tst_srch (srch_is varchar(100), user_name varchar(100), srch_name varchar(100));
insert into tst_srch values (1,'USER1','SRCHA');
insert into tst_srch values (2,'USER1','SRCHB');
insert into tst_srch values (3,'USER1','SRCHC');
insert into tst_srch values (4,'USER1','SRCHD');
insert into tst_srch values (5,'USER2','SRCHC');
insert into tst_srch values (6,'USER2','SRCHD');
insert into tst_srch values (7,'USER2','SRCHD_1');
Could I write a procedure like copy_searches('USER1','USER2') that would copy all USER1's searches to USER2 - including renaming any duplicates.
So it would create these new rows:
8,USER2,SRCHA
9,USER2,SRCHB
10,USER2,SRCHC_1
11,USER2,SRCHD_2
I've looked at various insert statements, merge and match statements and exception handling in procedures .
Copy And Rename An Oracle Database - (without Export / Import)
Oct 21, 2011I inherit a backup procedure described in
[URL]......
I ask if this procedure works on Oracle Database 10g R2. I have a Oracle DB 10g r2 on Linux machine and I want to copy it to a windows Oracle DB 10g r2, or if it not works to another Linux machine.
Server Utilities :: Rename Table While Using Datapump Import?
Mar 18, 2013I have a Datapump Export File which was created in Schema mode.
I have to import the tabelles in a new database where a have to use the REMAP SCHEMA Parameter.
Additionally I would like to add a prefix to tablenames.
For example:
original tablename: THE_TABLE
Name after import: IMP_THE_TABLE
Is there a way to add a prefix while using Datapump Import?
Reports & Discoverer :: Rename Report Output File
May 10, 2012I'm calling myreport.rdf from a form using the rwservlet and destype=mail and desformat=pdf. The report file is attached to the email as myreport.pdf. However, I want to have the output file named myfilename.pdf. Is there any way to accomplish this short of using destype=file and then renaming it and sending that file?
View 2 Replies View RelatedText :: Index For Domain Index With Composite Domain Index (CDI) Very Slow
Jun 27, 2012I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
Performance Tuning :: Local Index Versus Global Index On Partitioned Table
Jun 28, 2011I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies View RelatedServer Administration :: Convert Global Index To Local Index
Jun 23, 2011I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?
Nov 29, 2010I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
Performance Tuning :: Index With NVL / Query Is No Longer Using Index
Nov 19, 2010I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
Performance Tuning :: Force Index If Table Not Using Index?
Aug 9, 2013How to force an index if the table not using the index?
View 10 Replies View RelatedWhen Index Lock Contention Occurs / How To Know Which Index Is Causing
Aug 27, 2012We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
Server Administration :: ORA-01111 / Name For Data File 636 Is Unknown - Rename To Correct File
Nov 3, 2012i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors
SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
*
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS
SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?
Jul 11, 2012Can we create non-cluster index on a clustered index?
View 5 Replies View RelatedExport/Import/SQL Loader :: Use Export And Then Rename Table?
Jan 12, 2013oracle 11g.2 ASM with RAC under RHEL 5
we have 2 table same structure one of them is empty and the pther one is contain data the vendor do the insert as select but i found he is wrong due to there duplicated ,now i want to use export and then rename the table and then import but i need with export do a condition
exp user/pass tables=MTR_EPPC_CALLED_DATA file=MTR_EPPC_CALLED_DATA.dmp query="where callstarttime >=to_date('01122012','ddmmyyyy')
and callstarttime <=to_date('31122012','ddmmyyyy')"
but it's seem the query take one condition how can i use this above condition in export ???also my friend say there is way to insert with rowid is this possible ??
SQL & PL/SQL :: Difference Between Index And Clustered Index
Aug 3, 2010Any on give explanation for difference between Index and Clustered Index?
It will be great if i get explanation how memory allocation and Execution takes place.?
Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?
Jan 10, 2011What is the difference between index rebuild and index rebuild online.
View 3 Replies View RelatedPL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full
Oct 5, 2013Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
Last Used Time Of Index
Apr 7, 2011I'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.
After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.
--Monitor an index to see if it's used
alter index SAMPLE_INDEX monitoring usage;
select * from v$object_usage where index_name = 'SAMPLE_INDEX';
alter index SAMPLE_INDEX nomonitoring usage;
Index With Degree More Than 1
Jul 29, 2010There is an index with degree 16 on a rac env. The base table has no degree. The table and index are not partitioned. Does the degree of index only affect index DDL (alter, rebuild etc)? Any effects on query (PQ)?
View 4 Replies View Related