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

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 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

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 ...


6

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 ...


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

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

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 ...


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

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 ...


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

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 ...


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 ...


3

If you look inside mongodb/bin folder (ls -l) You can find a couple of binaries. The mongoDB has import and export tools. If you're running mongod locally on the default port, you can just do: $ ./mongodump Example: Dumping Everything $ ./mongodump --host prod.example.com Example: Dumping a Single Collection $ ./mongodump --db blog --collection ...


3

As far as I know, Import/Export and Data Pump are command-line-only types of tools. But if you really wanted to use that functionality from within SQL Developer (or SQL Plus), Data Pump uses the built-in DBMS_DATAPUMP and DBMS_METADATA packages. You should be able to call those packages directly, so I would start there. There's documentation available in ...


3

Maybe an error appears in the target database's alert log? Run this: SQL> show parameter background_dump_dest ...and go to the directory given to check the alert_<SID>.log. Or do a trace of the imp session in the target database instance. (Usually the errors appear in the exp / imp output themselves (saved to a log with LOG=log_file.log ) but ...


3

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 ...


3

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text. It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL. The script you need ...


3

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem. e.g. DECLARE data CLOB; objType varchar2(30) := 'TABLE'; objSchema varchar2(30) := 'SCOTT'; objName varchar2(30) := 'EMP'; fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql'; ...


3

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.


3

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 ...


3

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 ...


2

Send it over the network While extensive searching found no way to limit disk io natively (and serverFault will probably have more details on that) a "natural" way of limiting IO is to send the output directly across the network without ever touching the local disk. This then allows you to take advantage of all the network IO throttling resources out there. ...


2

If you can see them in the .dmp file you should be able to extract them? something like: grep -a 'CREATE PROCEDURE' expdat.dmp alternatively perhaps the parameter SHOW can help you see the DDL


2

Not using a built in command, but... If an UPDATE really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases. Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to ...



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