http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/ USE AdventureWorks GO SELECT t.name AS table_name ,SCHEMA_NAME(schema_id) AS schema_name ,c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name L...
I want to import an oracle dump into a different tablespace. I have a tablespace A used by User A. I've revoked DBA on this user and given him the grants connect and resource. Then I've dumped everything with the command exp a/*** owner=a file=oracledump.dmp log=log.log compress=y Now I want to import the dump into the tablespace ...
I have a script that takes a table name and generates a control file by querying all the columns/rows the table. This works fine for numeric and character data but fails on timestamp data so I need to adjust the script to output the timestamp data into the control in such a way that it can be read in properly. So essentially, my questi...
default is 49 how to edit to higher? ...
The SQL-LDR documentation states that you need to do a convetional Path Load: When you want to apply SQL functions to data fields. SQL functions are not available during a direct path load I have TimeStamp data stored in a CSV file that I'm loading with SQL-LDR by describing the fields as such: STARTTIME "To_TimeStamp(:S...
I have a number of tables that use the trigger/sequence column to simulate auto_increment on their primary keys which has worked great for some time. In order to speed the time necessary to perform regression testing against software that uses the db, I create control files using some sample data, and added running of these to the build...
I have a stored procedure that consists of a single select query used to insert into another table based on some minor math that is done to the arguments in the procedure. Can I generate the plan used for this query by referencing the procedure somehow, or do I have to copy and paste the query and create bind variables for the input par...
I'm taking on the re-architecting of a pair of applications which use Hibernate in one case, and a combination of Hibernate and a Java Content Repository (specifially JackRabbit) in the second. A key issue in the rearchitecting is to improve performance, so I'm wondering whether there's any value in bringing in a DBA for the design and ...
I need to grant select permission for all tables owned by a specific user to another user. Can I do this with a single command along the lines of: Grant Select on OwningUser.* to ReceivingUser Or do I have to generate the sql for each table with something along the lines of: Select 'GRANT SELECT ON OwningUser.'||Table_Name||'TO Rec...
Is it possible to show other processes in progress on an Oracle database? Something like Sybases sp_who ...
My initial installation for the MySQL had no password for root. I assigned a password for root and everything worked fine. Due to some reason (don't ask why) I had to revert back to the original settings where root didn't have any password. I changed the root password to '' (empty string). The problem now is that MySQL doesn't run with ...
A while ago I had a query that I ran quite a lot for one of my users. It was still being evolved and tweaked but eventually it stablised and ran quite quickly, so we created a stored procedure from it. So far, so normal. The stored procedure, though, was dog slow. No material difference between the query and the proc, but the speed c...
I'm new to database development so hopefully this is trivial. I have a large collection of raw data (around 300million rows) with about 10% replicated data. I need to get the data into a database. For the sake of performance I'm trying to use SQL copy. The problem being when I commit the data, primary key exceptions prevent any of the d...
We use Grid Control 10.2.0.4, with a catalog repository database also at 10.2.0.4. It seems that after a week or two of being up, the response time of the web interface gets very poor (20+ seconds to navigate to a new page, when normally 2-3 seconds is seen). The only thing we've found to overcome it is a restart of the catalog database ...
Related to the question about what project managers do all day... I have asked this amongst my colleagues at work who are all web developers. Part of our job is to basically be part DBA for our own projects. But we spend most of our time developing in .NET not SQL. So what does one do when one is working full time as a DBA? ...
We're doing a lot of large, but straightforward forms for a fairly big project (about 600 users using it throughout the day - that's big for me at least ;-) ). The forms have a lot of question/answer type sections, so it's natural for some people to type a sentence, while others type a novel. How beneficial would it be to put a charact...
How do I setup SQL full search in SQL Server Express 2005 and 2008? Are there any helpful queries that a dba should know? ...
I am trying to help a small business that has an application that could benefit from occasional (or temporary) database expertise. The problem is, this company has all their IP in this database, so they are rightfully hesitant to give someone access it. They would typically meet and gain trust in person, but the talent pool within ph...
I'm a developer so I'm a little lost in the DBA world. Our systems guys have given me a backup of an Oracle 9i database. I have installed oracle 9i on my pc and am now trying to 'import' the backup files so I have a normal database to work with. The backup folder has on SNCF[SID].ora file and around 150 [SID]-[Date]-[counter]-[soemnum...
I am creating a copy of our testing Oracle DB (10g both) and run into a problem in that our testing database has UTL_SMTP and UTL_FILE system packages installed and my local OracleXE version does not. Our system administrator has gone home for the day and given his state earlier I really would rather not bother him. How do I get these ...