[how to] How to connect a SQL Server 2012 to internet

---

• How to connect a SQL Server 2012 to internet
• Challenge: Build MySQL table that's not in first normal form
• is it good to use combined primary key for MySQL InnoDB clustered index?
• Find Underlying Data Type (int - integer) of Oracle Table instead of number
• User with dba privs can't login to APX, but can from SQL*Plus
• MySQL: Problems Making Configuration File Changes
• Issue after moving the ib_logfile1 and ib_logfile0 files
• Question about MySQL Semisynchronous Replication
• Duplication Oracle Database to remote host
• DBA_SCHEDULER_Job is not giving any result
• Why disabling a clustered index makes the table inaccessible?
• Transfer data from multiple different servers to a single database server
• ibdata deleted and innodb error [duplicate]
• "Replicate" sql structure between testing and staging on same database instance
• force PostgreSQL into recovery mode
• Service accounts for SQL Server 2012 only allow pre-Windows 2000 name?
• Add partition works but not the drop partition
• Shrinking the SYSTEM tablespace in Oracle
• Using MySQL InnoDB as an Archive
• ORA-16000 when trying to perform select on read only access ORACLE database
• MySQL Table not repairing
• Group By days interval (aging type)
• How to run a cold backup with Linux/tar without shutting down MySQL slave?
• Run Unix command using PL/SQL
• How do I perform an Oracle 10g export with expdp?
• How does name resolution work in Oracle 10g?
• how does name resolution in oracle 10g work? [duplicate]

How to connect a SQL Server 2012 to internet Posted: 05 May 2013 03:43 PM PDT As you know you can create a database with SQL Server using the SQL Server configuration manager and SQL Server Management Studio. You know this - just typing that to tell that I checked the whole internet but didn't find answer to my problem. Let's go back to question. I installed servers - one with default instance (MSSQLSERVER) and one with my own instance name. I want to configure that server so I can connect to it over the internet - from everywhere I am. I did some changes as I see on the internet - TCP Ports are enable and Named Pipes too. I set same ports on my server everywhere - to execute chance to be changed after restart (Many people say it - so I did it). OK good. When I open SQL Server Management Studio, I need to type in the Server name - here are three ways to do it - that is what I know: 1)Typing: Computer name\SQL server name 2)Typing: Local IP(given by the router)\SQL server name 3)This is what I need - network one: Typing: IP Adress(check in internet on one of many websites that says your real IP that comes to router\SQL SERVER NAME And my question is - why can I do first and 2nd connections, but in the 3rd one I get an error... The one that says there is no such a server. I did changes to router that allow ports on SQL Server and SQL Browser and nothing ... but everyone says i need to find port forwarding on router settings. I'm using Tenda W541R and I don't have such as "Port forwarding" but I have some kind of that - "Virtual Server" - I think that must be the same. Thanks in advance !

Challenge: Build MySQL table that's not in first normal form Posted: 05 May 2013 04:40 PM PDT I have read numerous books and articles about database design and SQL in which it is said that a database should be in first normal form (1NF). (Some then go on to describe situations in which it may be smart to denormalize a bit, but that's a different subject.) For example, a typical example of a table not in 1NF would be: +---------+-------+--------------+ | last | first | phone | +=========+=======+==============+ | Smith | John | 303-123-4567 | ---------------------------------- | Doe | Mary | 303-456-9933 | | | | 303-456-9944 | ---------------------------------- | Johnson | Bill | 303-987-6543 | +---------+-------+--------------+ The primary key is (last, first). Having studied this issue a bit, it's my belief that it is impossible to create a MySQL table that is not in 1NF. If that's true, then I wonder why such a big deal is made about it. Why tell users not to do something that's impossible? It reminds me of a sign on an elevator door: "Elevator out of order. Do not use." (In the comments, I'd appreciate it if anyone can give me instructions about how to create a MySQL table that is not in 1NF.) My example non-1NF row can't be inserted by the MySQL INSERT statement, because that statement allows only one value per row per column. If the type of the phone column is, say, varchar(255), one could certainly insert the value '303-456-9933,303-456-9944', but this would not violate 1NF, because clearly that string is a single value. (If you don't believe me, try getting at the two phone numbers without searching for the comma or breaking the string in half at position 13. SQL and every programming language I know of would treat the value as a single string.) Another attempt at building a MySQL table that's not in 1NF would be the following, and in this case the table certainly can be created in MySQL and the data shown can easily be inserted: +---------+-------+--------------+--------------+ | last | first | phone1 | phone2 | +=========+=======+==============+==============+ | Smith | John | 303-123-4567 | NULL | ------------------------------------------------- | Doe | Mary | 303-456-9933 | 303-456-9944 | ------------------------------------------------- | Johnson | Bill | 303-987-6543 | NULL | +---------+-------+--------------+--------------+ However, this table is also in 1NF, since phone1 and phone2 are two different columns (attributes). I used MySQL as my example because I don't know what's going on with the very latest versions of Oracle, DB2, SQL Server, etc. I do know that recent SQL standards allow array and row types for columns, but, as Chris Date has written extensively, whatever value one might have for a array- or row-type column, it is a single value, and therefore doesn't violate 1NF. Anyway, I'm not concerned with array and row types, because none of the database design books (the ones I've seen, anyway) that warn against non-1NF tables mention those two types, as the books are a few years old. It's all those books that have been written over the past 40 years that concern me. (Because I'm writing yet another book that touches on this subject.) My proposal is that the typical formulation of 1NF ("no repeating groups", "single value in each column of a row", or whatever) be replaced by a formulation that addresses the actual problem. Something like this: "A table should never have anything that looks like a repeating value, nor should data that looks like a repeating value be entered, unless one has first determined that the design is the desired one. (The issue is not whether a value is repeating, but whether it looks like it is.)" Maybe not the best wording, but at least it captures my thinking on 1NF. That is, the database or the application program should prohibit data like '303-456-9933,303-456-9944' from being entered into the phone column unless the designer has decided this is what he or she wants. Likewise, one should never have two columns named phone1 and phone2 unless one has thought about it first. After all, it's pretty common in database design to have a few phone columns (office phone, home phone, mobile phone, etc.), as opposed to defining a separate phone table and using a one-to-many relationship. Both approaches are reasonable and widely used, and anyone could come up with a list of pros and cons for each. That particular discussion is not related to the point I'm making. My point is this: The way 1NF is almost always defined, both are in 1NF, and therefore the design principle that suggests one approach versus another can't be based on 1NF. Ironically, with so many places where SQL violates the relational model, it seems that we have a case where it forces a relational principle! Incidentally, I don't have the same concerns about 2NF and 3NF. Those rules are definitely easily, and even frequently, violated by MySQL databases. Comments appreciated! UPDATE: Here's a quote from Chris Date's "Relational Database Dictionary" (p. 69): "It follows that a "table," in a language like SQL, can be considered to be in 1NF if and only if it's a direct and faithful representation of some relvar, where direct and faithful means among other things that every row-and-column intersection (i.e., every cell, q.v.) in that table contains exactly one value of the applicable type, nothing more and nothing less. (The value in question can be arbitrarily complex—it can even be a table—but, to repeat, there must be exactly one such, and it must be of the applicable type.)" 1NF says nothing whatsoever about two columns, even if they are named phone1 and phone2. It is solely concerned with the value in a single cell. People try to apply it to two columns with similar names, and there's no justification in the 1NF rule for doing so. In my proposed reformulation, I am trying to include the multi-column case, something that the rule as currently formulated doesn't do. Update #2: For those who think that phone1, phone2, etc. is a 1NF violation, forget phone numbers and just look at a table with three columns: first name, middle name, and last name. Most databases have such a table (with more columns). Surely you wouldn't say that having the three names made the table not 1NF? If you don't, surely you'll let me call the columns name1, name2, and name3? After all, "first", "middle", and "last" have no meaning in some cultures.

is it good to use combined primary key for MySQL InnoDB clustered index? Posted: 05 May 2013 02:57 PM PDT i'm trying to build a mirroring site of multiple forums. Because most queries are likely to be within same time periods for written_time column, I'm thinking about taking advantage of clustered index ordered by written_time. But because it is not unique, i'm thinking about making primary key with unique id like: (written_time, site_id, board_id, article_id) I guess it would require much bigger space for index and performance problem, but am I right? Is it a good approach to make cluster index like this if I would like to take advantage of query results which have proximity about written-time?

Find Underlying Data Type (int - integer) of Oracle Table instead of number Posted: 05 May 2013 11:54 AM PDT I can create an example table using following syntax. CREATE TABLE "TABLE2" ( "COLUMN_INT" INT NOT NULL, "COLUMN_INTEGER" INTEGER NOT NULL, "COLUMN_SMALLINT" SMALLINT NOT NULL, "COLUMN_NUMBER" NUMBER NOT NULL, "COLUMN_DECIMAL" DECIMAL NOT NULL); After that I query USER_TAB_COLUMNS table using following query. SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE2'; I get following result. COLUMN_NAME DATA_TYPE ------------------------------ ------------- COLUMN_INT NUMBER COLUMN_INTEGER NUMBER COLUMN_SMALLINT NUMBER COLUMN_NUMBER NUMBER COLUMN_DECIMAL NUMBER If I use VS.NET add-in for oracle to get DDL for this table. I get following. -- ****** Object: Table DENEME.TABLE2 Script Date: 05.05.2013 21:43:11 ****** CREATE TABLE "TABLE2" ( "COLUMN_INT" NUMBER NOT NULL, "COLUMN_INTEGER" NUMBER NOT NULL, "COLUMN_SMALLINT" NUMBER NOT NULL, "COLUMN_NUMBER" NUMBER NOT NULL, "COLUMN_DECIMAL" NUMBER NOT NULL, CHECK ("COLUMN_INT" IS NOT NULL), CHECK ("COLUMN_INTEGER" IS NOT NULL), CHECK ("COLUMN_SMALLINT" IS NOT NULL), CHECK ("COLUMN_NUMBER" IS NOT NULL), CHECK ("COLUMN_DECIMAL" IS NOT NULL)) / If I use SQL Developer -------------------------------------------------------- -- DDL for Table TABLE2 -------------------------------------------------------- CREATE TABLE "DENEME"."TABLE2" ( "COLUMN_INT" NUMBER(*,0), "COLUMN_INTEGER" NUMBER(*,0), "COLUMN_SMALLINT" NUMBER(*,0), "COLUMN_NUMBER" NUMBER, "COLUMN_DECIMAL" NUMBER(*,0) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ; -------------------------------------------------------- -- Constraints for Table TABLE2 -------------------------------------------------------- ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_DECIMAL" NOT NULL ENABLE); ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_NUMBER" NOT NULL ENABLE); ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_SMALLINT" NOT NULL ENABLE); ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INTEGER" NOT NULL ENABLE); ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INT" NOT NULL ENABLE); It seems that I can not get INT, INTEGER values, I used in my create table syntax? Int, Integer, small int seems to be a syntactic sugar only. Is there any way to find that if a column is INT/INTEGER/SMALLINT using SQL?

User with dba privs can't login to APX, but can from SQL*Plus Posted: 05 May 2013 10:56 AM PDT I'm using Oracle 11gR2 XE. A user with dba privileges can't log in to the APEX interface, but can from the command line with sqlplus. I just created user: CREATE ROLE administrators; GRANT dba TO administrators; CREATE USER superuser IDENTIFIED BY "root"; GRANT administrators TO superuser; If I run cmd, type sqlplus and log in with "superuser", I get access. But the web interface (APEX) denies access with an "invalid credentials" error message. What do I need to do to be able to log in on the APEX interface?

MySQL: Problems Making Configuration File Changes Posted: 05 May 2013 09:06 AM PDT I use MySQL 5.5 on both Mac OSX (10.6.8) and Windows 7; in both contexts I use Workbench for my administrative needs. I've recently started tinkering with the configuration files (my.cnf/my.ini), and it's been generally problematic. Here is what I am hoping to get from the StackExchange community: a. General feedback on whether I am doing anything obviously incorrect, below I have listed some of the things I have tried. b. A set of basic steps for making changes to my.cnf/my.ini via Workbench. c. The ABSOLUTE SIMPLEST config file that will get my server at least running. (Is it possible to run MySQL without a config file?) This file could then be a starting point for beginning the learning/tuning process. One thing that can be a bit overwhelming about the template files (e.g. my-huge.cnf), as well as many of the example config files on-line, is that they come with a large number of settings/options - it can be hard for a beginner to know what is going on. ACTIONS/ISSUES: Below are some things I've tried, most of which usually result in the server simply not starting. Making manual modifications to the configuration file. A few weeks ago, I used this method to turn on innodb_file_per_table, and it worked fine. However, recently when I tried modifying other settings like innodb_buffer_pool_size, the server simply would not start. Making changes via Workbench. In the Server Administration section of Workbench, there is an area called "Options File". Basically, it is a GUI consisting of check-boxes and text-boxes which makes modifications to the text my.cnf/my.ini file. Now, there have been a few instances in which I've made changes by this method and it has worked (i.e. the server actually started), but most of the time, using this interface has resulted in the server not restarting. Generally it seems that this interface works better in Windows than on Mac OSX. Using the provided templates (e.g. my-huge.cnf). To my suprise, I don't think I've ever been able to get one of these to work, which leads me to think that there is some obvious mistake I'm making (I've really only just copy/pasted them with the appropriate name change). Thanks in advance! Please let me know if there is additional information I could/should provide.

Issue after moving the ib_logfile1 and ib_logfile0 files Posted: 05 May 2013 01:25 PM PDT I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes. So I moved these files to different location and restarted the MySQL server. It worked. Before shutting down the MySQL, server I also made innodb_fast_shutdown = 0. But I have a doubt, after MySQL shutdown can we expect mysql is an consistent state and all the data that were in the ib_logfile0 and ib_logfile1 have been written to data files. So that I can delete these files or should I do something else also. And if the answer is MySQL will be in consistent state than why these files contain some data even after graceful shutdown and start up with innodb_fast_shutdown = 0. [EDIT details] giving the steps i have done Steps i followed: SET GLOBAL innodb_fast_shutdown = 0 /etc/init.d/mysqld stop rm -f /var/lib/mysql/ib_logfile0 rm -f /var/lib/mysql/ib_logfile1 /etc/init.d/mysqld start Is the above step is fine? if yes then how come after the clean shutdown and start up the log files contains some data. Ideally it should be empty.

Question about MySQL Semisynchronous Replication Posted: 05 May 2013 08:18 AM PDT I was researching about MySQL Semisynchronous Replication, my understanding about the workflow is the follow: Master Receive a successful transaction from a client session. Master write it to his binaty log Master wait for rpl_semi_sync_master_timeout milliseconds to receive an acknowledgment from at least one slave Slave pick this successful transaction, write it to his relay log, flush it to disk Slave return an acknowledgment to master Master return successful to his client session. My question is, at any point, this type of replication ensures that the transaction is executed on slave(SQL_TREAD applies it). Having in mind that it will add a delay on each transaction and in a case of master failure, is not 100% guaranteed that the slave will be consistent with master, what is the benefit(s) of using this type of replication?

Duplication Oracle Database to remote host Posted: 05 May 2013 03:39 AM PDT I have two scenarios. 1 ) Let's say , I have the same file system and same SID on both nodes. I've created any directories necessary for start the duplicate database. First I used command below. CREATE PFILE = '/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora' FROM SPFILE; I've moved initDB11G.ora file from source server to auxiliary server. On the auxiliary server : $ mkdir -p /u01/app/oracle/oradata/DB11G $ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G $ mkdir -p /u01/app/oracle/admin/DB11G/adump $ mkdir -p /u01/app/oracle/archive_logs (Archived redo log files) My initDB11G.ora from transcript: DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment *.audit_file_dest='/u01/app/oracle/admin/DB11G/adump' *.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata /DB11G/control02.ctl'#Restore Controlfile *.db_recovery_file_dest='/u01/app/oracle/fra' *.diagnostic_dest='/u01/app/oracle' *.log_archive_dest_1='location=/u01/app/oracle/archive_logs' Lastly : DUPLICATE TARGET DATABASE TO DB11G SPFILE NOFILENAMECHECK; A ) if I create a PFILE from SPFILE then do I have to use SPFILE parameter within DUPLICATE command? B) Another question : If I store backup files and autobackup are in a different location on the source server ( no default location flashback database ) then how to use DUPLICATE command? 2 ) Let's say , I have the same file system and different SID on both nodes. I've created any directories necessary for start the duplicate database. First I used command below. CREATE PFILE = '/u01/app/oracle/product/11.2.0/db_1/dbs/initCLONE.ora' FROM SPFILE; I've moved initCLONE.ora file from source server to auxiliary server. On the auxiliary server : $ mkdir -p /u01/app/oracle/oradata/CLONE $ mkdir -p /u01/app/oracle/fast_recovery_area/CLONE $ mkdir -p /u01/app/oracle/admin/CLONE/adump $ mkdir -p /u01/app/oracle/archive_logs (Archived redo log files) My initCLONE.ora from transcript: DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment *.audit_file_dest='/u01/app/oracle/admin/CLONE/adump' *.control_files='/u01/app/oracle/oradata/CLONE/control01.ctl','/u01/app/oracle/oradata /DB11G/control02.ctl'#Restore Controlfile *.db_recovery_file_dest='/u01/app/oracle/fra' *.diagnostic_dest='/u01/app/oracle' *.log_archive_dest_1='location=/u01/app/oracle/archive_logs' Lastly : DUPLICATE TARGET DATABASE TO CLONE SPFILE NOFILENAMECHECK; is there any missing/incorrect within two different scenarios?If so , please clarify Kind Regards,

DBA_SCHEDULER_Job is not giving any result Posted: 05 May 2013 10:43 AM PDT I have created one procedure that is supposed to trigger on 5th date of every month.for that i have used dba scheduler job.but its not triggering. select job_name,status from dba_scheduler_jobs; JOB_NAME STATE BUY_UNITS SCHEDULED CREATE TABLE "SIPHOLDER" ( "ACCNO" NUMBER, "PORTFOLIONO" NUMBER, "PORTFOLIONAME" VARCHAR2(4000), "NAV" NUMBER, "PERMONTHINVESTMENT" NUMBER, "TOTALUNITSBOUGHT" NUMBER, "STARTDATE" DATE, "SELLDATE" DATE, "FINALAMOUNT" NUMBER, "RETURN" NUMBER, "ECS" DATE, CONSTRAINT "SIPHOLDER_CON" FOREIGN KEY ("ACCNO") REFERENCES "SAVINGACCPERM" ("ACCNO") ENABLE ) CREATE TABLE "SIPPARAM" ( "EXITLOAD1" NUMBER NOT NULL ENABLE, "SERVICETAX" NUMBER(3,0), "TRANSACTIONTAX" NUMBER(3,0), "EXITLOAD2" NUMBER(3,0), "CAPITALGAIN" NUMBER(3,0) ) CREATE TABLE "SIP_PRICE" ( "PORTFOLIONO" NUMBER, "PORTFOLIONAME" VARCHAR2(4000), "DATEOF" DATE, "NAV" NUMBER, CONSTRAINT "SIP_PRICE_PK" PRIMARY KEY ("PORTFOLIONO", "DATEOF") ENABLE ) CREATE TABLE "SAVINGACCPERM" ( "ACCNO" NUMBER, "PASSWORD" VARCHAR2(4000), "APPNO" NUMBER, "BALANCE" NUMBER(38,0) NOT NULL ENABLE, CONSTRAINT "SAVINGACCPERM_PK" PRIMARY KEY ("ACCNO") ENABLE ) CREATE TABLE "UPDATEDNAV" ( "PORTFOLIONO" NUMBER, "DATEOF" DATE, "NAV" NUMBER(3,0), "PORTFOLIONAME" VARCHAR2(4000) ) procedure: create or replace procedure buyunits is mnav number; mpno varchar2(100); mtransactiontax number; mservicetax number; maccno number; mtotalamountleft number; mpermonthinvestment number; cursor ecursor is select accno from sipholder where EXTRACT(day from TO_DATE(ECS,'DD-MON-RR'))=5; begin FOR e IN ecursor LOOP select portfoliono, permonthinvestment into mpno, mpermonthinvestment from sipholder where accno= e.accno; select nav into mnav from sip_price where portfoliono = mpno; select servicetax, transactiontax into mservicetax, mtransactiontax from sipparam where rownum = 1; update savingaccperm set balance = balance-balance-mpermonthinvestment where accno = e.accno; mtotalamountleft := (mpermonthinvestment - (mpermonthinvestment*mtransactiontax)/100 - (mpermonthinvestment*mservicetax)/100); update sipholder set totalunitsbought= totalunitsbought +(mtotalamountleft)/mnav where accno= e.accno; end loop; end; Scheduler job: BEGIN -- Job defined entirely by the CREATE JOB procedure. DBMS_SCHEDULER.create_job ( job_name => 'BUY_UNITS', job_type => 'STORED_PROCEDURE', job_action => 'BUYUNITS', start_date => SYSTIMESTAMP, repeat_interval => 'freq=monthly;bymonthday=5', end_date => NULL, enabled => TRUE, comments => 'Job defined entirely by the CREATE JOB procedure.'); END; /

Why disabling a clustered index makes the table inaccessible? Posted: 05 May 2013 04:55 PM PDT When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index as data in the table changes, and the index cannot be used to satisfy queries. If a clustered index is disabled, the entire table becomes inaccessible, but why, isn't it possible to access the data directly from the table discarding the B-tree most likely by scanning the table row by row, wouldn't that be more appropriate than inaccessible data at all?

Transfer data from multiple different servers to a single database server Posted: 05 May 2013 05:56 AM PDT I am trying to transfer a particular table from 30 different servers into one server. I do not want to create 30 different ole db source and merge them. Is there a simple way to resolve this issue?

ibdata deleted and innodb error [duplicate] Posted: 04 May 2013 11:25 PM PDT This question is an exact duplicate of: InnoDB errors after having deleted “ibdata1” By accident I have deleted ibdata1 file in MySQL directory because the MySQL wasn't functioning correctly so after reading so many forums I found a post asking to delete the 3 files in MySQL directory (ib_logfile0, ib_logfile1, ibdata1) to let InnoDB load again and MySQL comes up and this what I have did but now when InnoDB loads as engine the tables disappear. After I do a graceful restart the InnoDB disappear totally from the server "have_innodb | No" but I can see all tables again for databases using InnoDB. What I'm looking for is a way to load all the tables correctly than ask ibdata to state them as InnoDB then let the InnoDB engine function correctly.

"Replicate" sql structure between testing and staging on same database instance Posted: 05 May 2013 07:50 PM PDT On my local development server I have a database set up which gets hooked into Entity-Framework and is then used for unit testing. Let's call it COMPANY\Database On the same server (and same database instance) I then have another database - COMPANY\DatabaseStaging which has the exact same structure but persistent test data used for constructing a front end site around. So, to hopefully clarify, the structure/flow is something like: Create or modify a table: COMPANY\Database.Product Update entity framework, write some business logic, unit tests, etc against the table. Manually mirror/replicate the changes: COMPANY\DatabaseStaging.Product Put in some slightly more persistent test data and do front end/ASP.NET stuff. Now, this doesn't seem very elegant to be manually updating the COMPANY\DatabaseStaging database to make it reflect changes in COMPANY\Database. Is there a better way of doing it?

force PostgreSQL into recovery mode Posted: 05 May 2013 11:04 AM PDT I have a somewhat strange question. Is there a way to manually force PostgreSQL into recovery mode? I've not been able to find aything in the documentation. I've been working with a script monitoring a postgresql database and need to do some testing. /Peter

Service accounts for SQL Server 2012 only allow pre-Windows 2000 name? Posted: 05 May 2013 06:13 PM PDT I've just set up SQL Server 2012 with domain service accounts. I found that the account name would not validate unless I used the shorter user name. Is there any way of using the full names?

Add partition works but not the drop partition Posted: 05 May 2013 04:05 AM PDT We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not working well any reason for this? IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = CONCAT(' p' , DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN SET @stmt := CONCAT( 'ALTER TABLE testPart1 DROP PARTITION ' , ' p' , DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ) ); PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = CONCAT(' p' , DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN SET @stmt := CONCAT( 'ALTER TABLE testPart1 ADD PARTITION (' , 'PARTITION p' , DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ) , ' VALUES LESS THAN (' , TO_DAYS( CURDATE() ) + 2 , '))' ); PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF;

Shrinking the SYSTEM tablespace in Oracle Posted: 05 May 2013 06:00 AM PDT Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table. We have truncated SYS.AUD$ but the datafile is still very big (~30G). Resize doesn't work because the file contains used data beyond requested RESIZE value What should I do here? Here's our version information: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production

Using MySQL InnoDB as an Archive Posted: 05 May 2013 02:05 PM PDT My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily basis. These archived rows are processed once a week for stuff like finding trends. This archive table consequently grows at 1 million new rows every week, and querying it can get really slow. Is MySQL suited for archiving data, or is my strategy very flawed? Please advise, thank you!

ORA-16000 when trying to perform select on read only access ORACLE database Posted: 05 May 2013 01:05 PM PDT My application's SQL encounters ORA-16000 when trying to access read only Oracle Database ORA-16000: database open for read-only access ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1 ### The error may exist in com/xyz/report/dao/Transaction001Mapper.xml This is the query that involves the XMLTYPE, the INTERFACE_CONTENT is a CLOB COLUMN : SELECT CONTENTS FROM ERRCODES WHERE CODE=(SELECT xmltype(INTERFACE_CONTENT).extract('/Message/PaymentStatus/ps:FIToFIPmtStsRpt/ps:TxInfAndSts/ps:StsRsnInf/ps:Rsn/ps:Prtry/text()','xmlns="urn:abcde" xmlns:head="urn:iso:std:iso:20022:tech:xsd:head.001.001.01" xmlns:ps="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"').getstringval() APP_CODE FROM MESSAGE_EXTERNAL_INTERACTION MEI WHERE MEI.MSG_TYPE='Pacs_002' AND MEI.MID='MY_ID') I also did A lot OF EXTRACTVALUE( ) method on an XML FIELD TYPE. The SQL is working perfectly if the Database is not read only ( read write ). My Question here is what is the issue here - Is this related to some missing priviledges/grant ?

MySQL Table not repairing Posted: 05 May 2013 08:05 PM PDT Table info: Database name: user_motiva Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD when I do a mysqlcheck -r --all-databases it gets hung on that table even if you let it sit all day. Is there anther way to fix/repair/recover that table? Should I use myisamchk? I saw something like: shell> myisamchk --recover City My config on a 16GB ram box cat /etc/my.cnf [mysqld] default-storage-engine=MyISAM local-infile=0 symbolic-links=0 skip-networking max_connections = 500 max_user_connections = 20 key_buffer = 512M myisam_sort_buffer_size = 64M join_buffer_size = 64M read_buffer_size = 12M sort_buffer_size = 12M read_rnd_buffer_size = 12M table_cache = 2048 thread_cache_size = 16K wait_timeout = 30 connect_timeout = 15 tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 64M query_cache_type = 1 low_priority_updates=1 concurrent_insert=ALWAYS log-error=/var/log/mysql/error.log tmpdir=/home/mysqltmp myisam_repair_threads=4 [mysqld_safe] open_files_limit = 8192 log-error=/var/log/mysql/error.log [mysqldump] quick max_allowed_packet = 512M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M and could this have happened because of a crashed table from doing killall -9 mysqld because it would not shutdown and restart? EDIT: root@server [/var/lib/mysql/user_motiva]# myisamchk -e *.MYI Checking MyISAM file: wp_options.MYI Data records: 1827 Deleted blocks: 3 myisamchk: warning: 3 clients are using or haven't closed the table properly - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check records and index references MyISAM-table 'wp_options.MYI' is usable but should be fixed root@server [/var/lib/mysql/user_motiva]# myisamchk --safe-recover wp_options.MYI - recovering (with keycache) MyISAM-table 'wp_options.MYI' Data records: 1827 myisamchk: error: Can't create new tempfile: 'wp_options.TMD' MyISAM-table 'wp_options.MYI' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag root@ns2 [/var/lib/mysql/user_motiva]# myisamchk -o -f wp_options.MYI - recovering (with keycache) MyISAM-table 'wp_options.MYI' Data records: 1827 Does this mean that it is now fixed? If so how do I move it back? (this was done on a different server) Is there a way to maybe bring MySQL down on the main server and run a command to fix all the files?

Group By days interval (aging type) Posted: 05 May 2013 11:05 AM PDT I would lIke to have Mysql group by days interval for example group by every 20 days from current day, like 1 - 20, 21 - 40, 41 - 60 and son on up to lets 120 days. The user can choose the days interval and up to how many days

How to run a cold backup with Linux/tar without shutting down MySQL slave? Posted: 05 May 2013 03:05 PM PDT I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are updated during the process. What am I missing? The slave machine is in a cold standby machine so there are no client processes running while tar is running. CentOS release 5.6 64bits, MySQL 5.1.49-log source distribution.

Run Unix command using PL/SQL Posted: 05 May 2013 08:05 AM PDT Is it possible to run a Unix command using a query in Oracle? I want to run simple commands (like df -h) using a query. Is this at all possible or am I wasting my time? I don't want to use a different language like Java or C to call a procedure, it needs to purely PL/SQL.

How do I perform an Oracle 10g export with expdp? Posted: 04 May 2013 10:03 PM PDT I followed the instructions at http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php to create a database export. I used: expdp scott/tiger@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log to create the export, however I get this error message: UDE-00008: operation generated ORACLE error 12154 ORA-01033: ORACLE initialization or shutdown in progress How do I resolve this issue?

How does name resolution work in Oracle 10g? Posted: 04 May 2013 10:10 PM PDT In Oracle 10g (10.2.0), there are 4 types of names resolution: Easy Connect(10g added this new method) Local naming Directory External naming It is said that if Easy connect is used by the clients, then it may overload the listener on the server (as client queue increases). How can this happen as it is the server's duty to distribute load from one listener to another? (Please correct me if my concepts are wrong.) And how exactly does Directory and External naming work.

how does name resolution in oracle 10g work? [duplicate] Posted: 04 May 2013 10:16 PM PDT Possible Duplicate: how does name resolution in oracle 10g work? In oracle 10g (10.2.0) names Resolutions there are 4 types supported by 10g Easy Connect(10g added this new method) Local naming Directory External naming my question is: it is said that if Easy connect is used by the clients then it may overload the listener on server(as client queue increases), how this can happen as its servers duty to distribute load from one listener to another(please correct me if my concepts are wrong)? and how exactly does Directory and External naming work. thank you.

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610