Server Administration :: Implementing Case-insensitive Search
May 8, 2012
we are implementing case insensitive seaarch in our small 10g database...We would be modifying the following parameters.
nls_comp=LINGUISTIC ;
nls_sort=binary_ci;
We want to use the existing indexes and as I understand we will need indexes created in following manner
create index <index_name> on <table_name>(nlssort( <column_name>, 'NLS_SORT=BINARY_CI'));
1) Is there any mechanism with which we can convert existing index to above type? (without drop and recreate)One of the issue I can see is related to unique values as 'ABC', 'Abc' and 'abc' would be treated same
2) Apart from that is there any issue, added maintenance for such index?
replace a occurence of a string by case insensitive search For eg.
String | Replaced String ----------------------------------|---------------------------- I am NEW.USER to regex | I am NOTSONEW.USER to regex I am NEWBIE.USER to regex | I am NOTSONEW.USER to regex I am part of newer.users of regex | I am part of NOTSONEW.users of regex
Essentially am trying to look for case-sensitive occurence of the string NEW%. and replacing it with NOTSONEW.
How can I drop a lower case tablespace name from my DB?
SQL> select * from v$version; BANNER ------------------------------------------------------------ Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production [code]....
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE 2- edit my report query to something similar to > Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null 3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this. I am using theme 23 page template without sidebars Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
Questions about implementing ARCHIVELOG mode in RAC environment.
Oracle 11gR1 RAC + ASM database. I'd like to implement ARCHIVELOG mode. Each node will have its own archive log.
Does it make sense to place archivelogs on ASM (shared storage)? Or the best practice would be to have some aside "BACKUP" server to place and keep archivelogs from all the RAC nodes there?
Additional question is about archive logs space required estimation. If it's correct to know estimate archive logs space required to get the daily avg number of switch logs
CODEselect round(avg(n_of_switch_logs), 3) avg_num_of_switch_logs from (select trunc(first_time), count(*) n_of_switch_logs from gv$log_history group by trunc(first_time)) and multiply it on size of single redolog?
I have question regarding data masking. Currently we are using Oracle 11g database , we are thinking of implementing data-masking feature in 11g. Is this can be done by adding any package to it.
We are planning to use an Application Express application for intranet and internet users. We want to implement a DMZ configuration for this application.
Is there any good guide to implement it?, we want to use Oracle Http Server to access Oracle Application Express.
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME) WHEN 'ALERT' THEN SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A WHERE A.ALERT_ID = IN_PARENT_NODE_ID; INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
I was trying to delete the database in the test server. When i was deleting listener was already stopped, i continued deleting using dbca, it shown me some alert that datafiles cant be deleted because system could't find database, since listner was stopped so only service was deleted(the one showing in the windows administrator toolsservicesOracleServiceTEST).
All the datafile parameter files are still there. How can i delete the datafiles and parameter files belongs to that database or how to create the deleted service, so that i will start the listener and do the complete deleting of the database.
I have to migrate production database version 10gR2 on windows 2003 server to oracle 11gR2 on windows 2008 server . So far i have just installed the oracle software on 64bit windows 2008 server. How to move the data or migrate from old server to new server .
after a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
I 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%'
I have database in single server. I already have ORACLE_SID=stagedb. So i want to create new sid and named as proddb.Is it possible have multiple db in single server? This server not running as RAC. It run as single only.
database administration , we are planning to use amazon cloud database , this database does not allow us to login to server machine , unfortunately amazon don't provide ssh to this machine , in general for doing any of adminstration task on the database will there be need to log into the machine ? we can always log in through toad or any other sql client but we cannot do ssh to server..general can this limitation effect administration ?
I want to install Oracle 11g R2 in windows 2008 64 bit server. How can I know whether my server is ready to install Oracle ie is all components are available in server or any patch is to be applied etc.
I'm trying to connect a oracle client application on the client machine to a remote oracle server on the server machine but i get a connection fail.
On the server machine I configured oracle server in the following way:
Installed oracle server. Created a database "DB_Test" with the database configuration assistant Created a LISTNER with the Oracle NET Manager with the following parameter:
Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521 Created "dbtest" service with the Oracle NET Manager with with the following parameter: Service Name: "dbtest" Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521
All services on the server machine are running and I opened port number (1521) in the router. On the client machine I installed SQL PLUS and SQL Developer.
With SQL Plus as by the official documentation I have entered the following command:
CONNECT username/password@[//]host[:port][/service_name]. In my case is: CONNECT SYSTEM/oracledb@//ENZOVAIO:1521/testdb.
With SQL Developer I have entered the same parameter.
But with both SQLPlus and SQL Developer the connection fails.
We 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?
I recently installed Oracle 10g on my windows Xp laptop. It has become considerably slow since then. I want to start the database server only when I need it, and not every time I start my laptop. I looked around in OEM and did found a way.
I have installed oracle 11g Release 11.1.0.1 on windows server 2008.I access this server from a client machine running on winXP through SQL PLUS. I wanted to shutdown and start DB server from this client machine. Accordingly i issued shutdown immediate command from SQLPLUS.
Now when i tried to start the DB with "STARTUP NOMOUNT" command i received error 12514. I understand this error means that the listener received a request to establish connections to a database but since DB services are not up and running, hence the error. So my first question is how do I resolve this error.
Continuing further, i tried to re-connect to the database as sys, and i received another error ORA-01041. So, my second question is why did i receive this error and again how can i resolve it
may be you can point me to relevant reference in Oracle documentation) how can i start and stop oracle from a client machine using SQL interface in CLI mode and windows environment and avoid this error.
I am connected as System. It was the only user I set-up a password when installed the database on personal computer.
SQL> alter user sys identified by mypass007 2 / User altered. SQL> connect sys/mypass007 ERROR: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
We will be having a meeting with our client regarding their Database Server Migration (They are planning to buy a new server). Their current database is Oracle 10gR2, they will not upgrade to 11g, they just plan to migrate to a new more powerful machine.
I was planning to ask the following questions.
1. Specifications of the current server and the new one. 2. Operation system (I think they will use same OS, just an updated one) 3. Can the business afford full downtime on current servers? 4. Size of the DB, because it can take hours to move large files.
And is there documentation regarding Server Migration (Change of machine only, not database upgrade or anything,
