Tag Info

Hot answers tagged

9

Instead of export/import, you should use alter table t shrink space; which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s): set verify off column file_name format a50 ...


9

Tom Kyte has a number of different solutions to generating flat files from Oracle on his site. There is a PL/SQL implementation using UTL_FILE as well as a Pro*C SQL unloader application.


9

No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql. The options you found "--disable-keys" cause the the mysqldump to write something like this before the table's load via INSERTs: DROP TABLE IF EXISTS `tblAccountLinks`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE ...


8

You really just have to get creative. As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare. For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach ...


8

You should look at the built in UTL_FILE package. There are several ways you could use it. You could write any number of procedures in packages that use the UTL_FILE package to write to any number of files. These procedures can then be called from almost any application including SQL*Plus. You could write a PL/SQL script to do the same work and call the ...


8

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option. Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps). Call expdp as usual, adding: REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION Short example (schema: mat): create or replace package remap as function ...


8

Since your source server is SQL Server 2000, you can't restore directly to SQL Server 2012. You will need to restore to another instance first (2005, 2008, or 2008 R2). On 2000: BACKUP DATABASE dbname TO DISK = 'D:\backups\db.bak' WITH INIT; On 2005 or 2008 or 2008 R2: RESTORE DATABASE dbname FROM DISK = 'D:\restores\db.bak' WITH REPLACE, RECOVERY, ...


7

If you've got access to the original database, I'd go with DBMS_METADATA.GET_DDL. You can script it with UTL_FILE so that it goes through each object (from USER_OBJECTS), uses the name and type to extract the object then write it to a file that has the appropriate naming convention. It will be a lot cleaner than trying to split a single file.


7

There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs ...


7

There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...). Look at this ...


7

Looping through all those articles is an option if you want to get old watching it execute. Some different options that you should try are: the bcp utility (mentioned above, too, it's a command line tool to import and export data to/from an SQL Server); BULK INSERT statement - the T-SQL language sugar to work with data imports from scripts; SqlBulkCopy ...


6

Ionice can limit disk usage Take a look at the ionice utility for linux, as it seems to suit your needs quite well.


6

Yes, by using # at the start of a line. Actually everything after the # sign will be ignored, so this can be used after a parameter as well. This is a bit hidden in the manual: Table names specified on the command line cannot include a pound sign (#), unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table ...


6

To be honest, this is easiest: backup/restore a copy locally remove unwanted data from the copy (with DELETE or TRUNCATE TABLE, not DROP...) ship the copy I wouldn't bother with filegroups because of the added complexity you noted...


6

Rather than doing this, I'd advise on getting the character set info from the nls_database_parameters view & then using DBCA to create a new database, along with dbms_metadata.get_ddl to handle tablespace creation. - Much easier and less prone to error. The character set and national character set are really the only things that are a pain to change when ...


5

Yes, no problem. Use BCP (which is a command line tool) to export the data. Once you have the parameters the way you like, schedule this as a SQL Agent job.


5

Your method will work fine, I would pre-allocate the data file on the new database so it won't need to autogrow. A backup would work just as well as detaching. And the destination server will need the same or higher version of SQL.


5

Your database is unhappy. Login to your Linux box as the DB owner user (usually oracle), restart it & then do the following to recreate the broken data dictionary objects: sqlplus / as sysdba @?/rdbms/admin/catrepr.sql @?/rdbms/admin/catrep.sql @?/rdbms/admin/utlrp.sql Restart the database once done. This is an unusual situation to be in - Has ...


5

To do this, you'll be wanting to use the 11.1 Data Pump (expdp/impdp) rather than exp/imp. With Data Pump you export using the higher version export utility with the VERSION= parameter. For example: expdp scott/tiger version=10.1 directory=DUMPDIR dumpfile=DUMPFILE.dmp logfile=DUMP.log Don't forget to create the directory (using CREATE DIRECTORY DUMPDIR ...


5

impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want. Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE ...


5

Please run this query: SELECT Data_BB / POWER(1024,1) Data_KB, Data_BB / POWER(1024,2) Data_MB, Data_BB / POWER(1024,3) Data_GB FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A; This will give you a ballpark figure. The column index_length is ...


4

I'd of thought BCP, with a format file rather than defaults, would be your best bet. Creating a format file


4

There are a few different ways to do this: Install Oracle SQL Developer which I thought was installed as a default. Then use this to export your data on a per table basis or as a .dmp file The old fashioned way, from the dos command line using the [export command]. This gives you a .dmp file which you can import into another database2 if you want to send ...


4

You'll want to look at using SQL Server Integration Services. If you want to export a small amount of data, you can also SELECT the data in SQL Server Management Studio, and highlight / copy / paste it into Excel, or rt click save as.


4

If performance is a concern, you may want to consider tools from vendors. I have evaluated tools from BMC, Wisdomforce, CoSort, DBCrane. They are all significantly faster than spool, utl_file or external table. We are using DBCrane because my boss didn't want to spend too much on license.


4

The way you speak of only needing the primary file group, it sounds like to me that you only want the database structure and a very small amount of data. I would suggest if you just want an update of the database structure (objects, tables, etc) to simply script the database out with all the objects. This can be done quickly and easily with PowerShell. Then ...


4

I'll answer this at a high level for you. The two backup methods work at different levels. An RMAN backup is a physical backup and a Data Pump backup is a logical backup. A database dump using expdp is a 1-time export of one or more database schemas. It backs up DDL (table structures, views, synonyms, stored procedures, packages, etc), plus data. An RMAN ...


4

Both the approaches already suggested appear to be unnecessarily complicated. Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths. Because it's a psql backslash command you omit the trailing semicolon, eg: \copy products TO '/tmp/products.csv' CSV ...


4

I wrote an open source utility that will script out common server configurations and save them to various files. It includes everything in sys.configurations as well as security settings, server properties, credentials, databases and a whole lot more. The utility is SQL Server Configurations on CodePlex. If you script out the two servers and use a diff ...


4

Many experts share many ways on how to overcome this problem. These are my suggestions to play a safe game. Try to set the below command in a seperate session. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; use db; SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME; COMMIT; exit; Doing by this way the SELECT statements are ...



Only top voted, non community-wiki answers of a minimum length are eligible